• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Filter order of multiselect dbjoin element

troester

Well-Known Member
Staff member
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)
 
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.

Code:
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
 
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
 
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).
 
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
 
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
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top