When you try to sort on an xref field and then try to search any field the records of the table do not appear any more.
The sorting on xref is producing this problem since I get an exception :
org.molgenis.framework.db.DatabaseException: Unknown column 'Biobank_Biodata' in 'order clause'
More specifically I tried to run the mysql query that produces this problem and found out that an extra join table is missing and the order field is not formed correctly :
e.g.
//SELECT Biobank.id, Biobank.canRead, ... xref_canRead.name AS canRead_name,... LEFT JOIN MolgenisRole AS xref_can... ORDER BY Biobank_Biodata ASC LIMIT 10 OFFSET 0
//SELECT Biobank.id, Biobank.canRead, ... xref_canRead.name AS canRead_name,... LEFT JOIN MolgenisRole AS xref_can... LEFT JOIN Biobank_Biodata ON Biobank.id = Biobank_Biodata.Biobank ORDER BY Biobank_Biodata.Biobank ASC LIMIT 10 OFFSET 0;
The second query works (added the red part). This is formed in AbstractJDBCMapper.java I am guessing by rewriteMrefRule? which here is abstract.
(Full queries :
FAILING QUERY : SELECT Biobank.id, Biobank.canRead, Biobank.canWrite, Biobank.owns, Biobank.Cohort, Biobank.Acronym, Biobank.Category, Biobank.SubCategory, Biobank.PanelSize, Biobank.GwaDataNum, Biobank.GwaPlatform, Biobank.GwaComments, Biobank.GeneralComments, Biobank.Publications, Biobank.Approved, xref_canRead.name AS canRead_name, xref_canWrite.name AS canWrite_name, xref_owns.name AS owns_name, xref_Category.name AS Category_name, xref_SubCategory.name AS SubCategory_name FROM Biobank LEFT JOIN MolgenisRole AS xref_canRead ON xref_canRead.id = Biobank.canRead LEFT JOIN MolgenisRole AS xref_canWrite ON xref_canWrite.id = Biobank.canWrite LEFT JOIN MolgenisRole AS xref_owns ON xref_owns.id = Biobank.owns LEFT JOIN OntologyTerm AS xref_Category ON xref_Category.id = Biobank.Category LEFT JOIN OntologyTerm AS xref_SubCategory ON xref_SubCategory.id = Biobank.SubCategory ORDER BY Biobank_Biodata ASC LIMIT 10 OFFSET 0
CORRECT QUERY
SELECT Biobank.id, Biobank.canRead, Biobank.canWrite, Biobank.owns, Biobank.Cohort, Biobank.Acronym, Biobank.Category, Biobank.SubCategory, Biobank.PanelSize, Biobank.GwaDataNum, Biobank.GwaPlatform, Biobank.GwaComments, Biobank.GeneralComments, Biobank.Publications, Biobank.Approved, xref_canRead.name AS canRead_name, xref_canWrite.name AS canWrite_name, xref_owns.name AS owns_name, xref_Category.name AS Category_name, xref_SubCategory.name AS SubCategory_name FROM Biobank LEFT JOIN MolgenisRole AS xref_canRead ON xref_canRead.id = Biobank.canRead LEFT JOIN MolgenisRole AS xref_canWrite ON xref_canWrite.id = Biobank.canWrite LEFT JOIN MolgenisRole AS xref_owns ON xref_owns.id = Biobank.owns LEFT JOIN OntologyTerm AS xref_Category ON xref_Category.id = Biobank.Category LEFT JOIN OntologyTerm AS xref_SubCategory ON xref_SubCategory.id = Biobank.SubCategory LEFT JOIN Biobank_Biodata ON Biobank.id = Biobank_Biodata.Biobank ORDER BY Biobank_Biodata.Biobank ASC LIMIT 10 OFFSET 0;)
When you try to sort on an xref field and then try to search any field the records of the table do not appear any more.
The sorting on xref is producing this problem since I get an exception :
org.molgenis.framework.db.DatabaseException: Unknown column 'Biobank_Biodata' in 'order clause'
More specifically I tried to run the mysql query that produces this problem and found out that an extra join table is missing and the order field is not formed correctly :
e.g.
//SELECT Biobank.id, Biobank.canRead, ... xref_canRead.name AS canRead_name,... LEFT JOIN MolgenisRole AS xref_can... ORDER BY Biobank_Biodata ASC LIMIT 10 OFFSET 0
//SELECT Biobank.id, Biobank.canRead, ... xref_canRead.name AS canRead_name,... LEFT JOIN MolgenisRole AS xref_can... LEFT JOIN Biobank_Biodata ON Biobank.id = Biobank_Biodata.Biobank ORDER BY Biobank_Biodata.Biobank ASC LIMIT 10 OFFSET 0;
The second query works (added the red part). This is formed in AbstractJDBCMapper.java I am guessing by rewriteMrefRule? which here is abstract.
(Full queries :
FAILING QUERY : SELECT Biobank.id, Biobank.canRead, Biobank.canWrite, Biobank.owns, Biobank.Cohort, Biobank.Acronym, Biobank.Category, Biobank.SubCategory, Biobank.PanelSize, Biobank.GwaDataNum, Biobank.GwaPlatform, Biobank.GwaComments, Biobank.GeneralComments, Biobank.Publications, Biobank.Approved, xref_canRead.name AS canRead_name, xref_canWrite.name AS canWrite_name, xref_owns.name AS owns_name, xref_Category.name AS Category_name, xref_SubCategory.name AS SubCategory_name FROM Biobank LEFT JOIN MolgenisRole AS xref_canRead ON xref_canRead.id = Biobank.canRead LEFT JOIN MolgenisRole AS xref_canWrite ON xref_canWrite.id = Biobank.canWrite LEFT JOIN MolgenisRole AS xref_owns ON xref_owns.id = Biobank.owns LEFT JOIN OntologyTerm AS xref_Category ON xref_Category.id = Biobank.Category LEFT JOIN OntologyTerm AS xref_SubCategory ON xref_SubCategory.id = Biobank.SubCategory ORDER BY Biobank_Biodata ASC LIMIT 10 OFFSET 0
CORRECT QUERY SELECT Biobank.id, Biobank.canRead, Biobank.canWrite, Biobank.owns, Biobank.Cohort, Biobank.Acronym, Biobank.Category, Biobank.SubCategory, Biobank.PanelSize, Biobank.GwaDataNum, Biobank.GwaPlatform, Biobank.GwaComments, Biobank.GeneralComments, Biobank.Publications, Biobank.Approved, xref_canRead.name AS canRead_name, xref_canWrite.name AS canWrite_name, xref_owns.name AS owns_name, xref_Category.name AS Category_name, xref_SubCategory.name AS SubCategory_name FROM Biobank LEFT JOIN MolgenisRole AS xref_canRead ON xref_canRead.id = Biobank.canRead LEFT JOIN MolgenisRole AS xref_canWrite ON xref_canWrite.id = Biobank.canWrite LEFT JOIN MolgenisRole AS xref_owns ON xref_owns.id = Biobank.owns LEFT JOIN OntologyTerm AS xref_Category ON xref_Category.id = Biobank.Category LEFT JOIN OntologyTerm AS xref_SubCategory ON xref_SubCategory.id = Biobank.SubCategory LEFT JOIN Biobank_Biodata ON Biobank.id = Biobank_Biodata.Biobank ORDER BY Biobank_Biodata.Biobank ASC LIMIT 10 OFFSET 0;)