SQL filter and search in tables with relationship.

Instant JChem 5.3.8, Windows, MySQL server.

In our database I have introduced row level sequrity, because I want to exclude information related to projects the users are not working on. I have made a user_project table and a projects table, where I define which project or projects a specific compound ID (EPTnumber) is related to.

The SQL filter looks like this: EPTnumber IN (SELECT EPTnumber FROM PROJECTS WHERE Project_name IN (SELECT Project FROM USER_PROJECT WHERE USER_PROJECT.user_name = '__IJC_USERNAME__'))

I has been added in all tables in "Extra Attributes". It works fine, but for one problem: I have made a relationship between my Structures table and various data tables. I have also made a form view, where one can see the relational data. It works in Browse mode, but it is only possible to make a query in the fields related to the Structures table. Queries in any of the fields referring to the related (data) tables returns an error message: “A org.springframework.dao.DataIntegrityViolationException exception has occurred. However, the system should continue working without further problems. Click Show Detals for the strack trace”

What is wrong? The individual tables are searchable in Grid view.

 

We'll look into this. We'll need to set up a similar test system so it will take a little while.

In the meantime, could you look in the log file and see if there is more information about the error reported there? If so then please send/attach it to this topic (or send privately if you prefer)

Thanks

Tim

Hi There,

We set up a test scenario and found that the problem was down to unqualified SQL, in particular on the tables that the filter is being applied to.

i.e. you should need to apply your SQL filter like below - fully qualified. In this test case we used the demo data provided in IJC as parent and child tables but of course you should edit accordingly for your own table names.

Cheers.

WOMBAT.EPTnumber IN 


(SELECT p.EPTnumber 


 FROM PROJECTS p WHERE p.Project_name 


 IN (SELECT up.Project 


     FROM USER_PROJECT up


     WHERE up.user_name = '__IJC_USERNAME__'))





WOMBAT_ACT_LIST.EPTnumber IN 


(SELECT p.EPTnumber 


 FROM PROJECTS p WHERE p.Project_name 


 IN (SELECT up.Project 


     FROM USER_PROJECT up 


     WHERE up.user_name = '__IJC_USERNAME__'))