Filter order of multiselect dbjoin element

[troester]

Setup:
a dbjoin rendered as checkbox
filter setting of this element = dbjoin

The default order of this element (in form and in filter) is set by the xy element of the repeat table - which contains the values, not the labels.

I can order the labels in the form display by adding
order by {thistable}.xy in Data-where /"Joins where..."

But I can't manage to order the filter labels
Fabrik is running the "Filter where" at some point (it's throwing an error message if the syntax is not correct) but it ignores any ordering
"3=3 order by text" is creating the correct query (result ordered by the labels in phpMyAdmin)

 

[cheesegrits]

We seem to be doing a ksort() on the select rows array, databasejoin.php, line 2698.

I can't remember why we do that, or if it'll break anything if we don't.

However, there's another problem, which is that once you filter, with an ORDER BY, it generates another error, because we append an AND to the filter where.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND `us_states`.`id` IN ('43')' at line 4 SQL=SELECT `fab_checkbox_join_repeat_states`.id AS id,`fab_checkbox_join_repeat_states`.parent_id, `fab_checkbox_join_repeat_states`.`states` AS value, `us_states`.`state` AS text FROM `fab_checkbox_join_repeat_states` LEFT JOIN us_states ON `us_states`.`id` = `fab_checkbox_join_repeat_states`.`states` WHERE 20=20 ORDER BY us_states.state_code DESC AND `us_states`.`id` IN ('43')

We account for that with the main WHERE filter, but not the filter WHERE.

I'll see if I can figure it out.

-- hugh

 

[cheesegrits]

OK, I'm getting there.

Question ... if the join has both a regular "Join where" and and "Filter where", and they both have ORDER BY, what should we do?

At the moment, when building filters, we apply both WHERE clauses joined with AND.

Should we apply both ORDER BY, or should the filter one take precedence?

-- hugh

 

[troester]

Trying to use 'order by' in Join-where and especially the Filter-where is only a hack because the usual ordering (labels alphabetically) and the "list view settings"/ filter /order by (value/label/none) is not working with the multiselect dbjoin.
I think the Filter-where (tooltip says it's WIP) is not designed to order.

In "former times" (Fabrik2?) if "list view settings"/ filter /"order by" = none it took the Join-where 'order by'
(maybe this is still the case for a normal dbjoin, I didn't test).

 

[cheesegrits]

Try this:

https://github.com/Fabrik/fabrik/commit/ce91ba5d4cbab95685bd1078de0b05a8defdd9f9

It applies both ORDER BYs.

NOTE - remember you have to set the "Filter order" (under the element's list settings, filter tab) to None.

-- hugh

 

[troester]

hmm...
ok: with this commit I can set Joins-where 'ORDER BY text ASC/DESC' which will order filter and form labels
great, this will cover the most common needs (but shouldn't be text ASC the default?)

But I don't understand the settings principle (maybe it's just corner cases in multiselect dbjoin):
WHERE clauses in Joins-where and Filter-where are "ANDed" in the list filter?

Ordering has issues:
Filter order =Label: is ignored, it's always "Value" (I think this is related to the missing default "label" order in the form)
Filter order=none: Why should the filter take the Join-Where ordering if one is set in the Filter-where?

The Filter-where is run somewhere during form load, throwing errors if the syntax isn't correct at this moment (e.g. table or columns, {thistable}) but obiously not used for the checkbox display (if the syntax is correct e.g. the filter WHERE clause doesn't have any effect on the form labels - as it should be)

But as I've said:
no real issues, corner cases

 

[cheesegrits]

Yeah, it's one of those things we could probably figure out, but it's so corner case, it's not really worth the effort.

As long as you can now do what you need to do, I think we can call this one "resolved".

-- hugh