Cannot sort on 'Bioplate' and 'Biowell row'

[Jari Häkkinen]

I get

Invalid path: 'alj2.bioPlate' [SELECT COUNT(*) FROM
net.sf.basedb.core.data.SampleData smp LEFT JOIN alj2.bioPlate alj1 LEFT JOIN
smp.bioWell alj2 WHERE ((smp.name LIKE :p3986624))]

when I first sort on Bioplate and then try to add secondary sort on Biowell row by shift clicking.

I do this in the sample list and many items are not assigned to bioplates.

[Nicklas Nordborg]

The order of the LEFT JOIN:s are clearly incorrect. Surprisingly it doesn't seem to matter in which order the columns are selected...

It feels like this is related to the issue described in comment:7:ticket:1364. Before this was fixed in [5261] the query would have been something like this:

... LEFT JOIN smp.bioWell.bioPlate alj1 LEFT JOIN smp.bioWell alj2 ...

but this of course triggers the "cross join" problem. NOTE! The above query is actually still generated by the query system. The fix in [5261] is applied at a later stage as a search-and-replace operation that replaces joined paths with their aliases. Eg. in this case smp.bioWell is replaced with 'alj2'.

I think it is difficult to re-order the joins correctly at this stage. They have to correctly ordered in the first place.

[Nicklas Nordborg]

(In [5543]) Fixes #1571: Cannot sort on 'Bioplate' and 'Biowell row'

Order the joins so that shorter paths are joined before longer paths which should make sure that they appear in the correct order.

This also triggered a second issue in the search-and-replace step. We need to reset the regular expression matcher after a replacement have been made since otherwise we are looking for strings that no longer exists in the query.

[Nicklas Nordborg]

(In [5547]) References #1571: Cannot sort on 'Bioplate' and 'Biowell row'

Merged this fix to the trunk.