Sorting of dropdown database join elements in searchfilter

[pwouda]

Hi,

I want to sort the data (a list of authors) in a dropdown of a database-join element in the filter, but I can't figure out how to do that.

Here is my unsorted dropdown:


THis is the way I tried to order the list by "auteur" (author)


And I used this to order the list but it does noet work.

 

[startpoint]

ORDER BY {thistable}.auteur ASC or
ORDER BY {thistable}.auteur DESC

 

[pwouda]

Thanks for the suggestion, but it makes no difference. Dropdown is still not sorted after adding ASC of DESC to the ORDER BY query.

 

[troester]

You can order the filter by Label or Value or None in the element List view filter settings.
With "None" it's using you data-where setting.

 

[pwouda]

Did that also, both in the field-plugin element "Auteur" as the databasejoin plugin element "Auteuruitlijst". Tried "value" and "label", but both without any result.

 

[troester]

What are your dbjoins's Details/Data setting?
Which table column is "Auteur" linked to, which data does it contain (in the database).

Enable FabrikDebug in Fabrik Options and append &fabrikdebug=1 to your list URL. This will show you somewhere the generated filter query.

 

[lousyfool]

It must go into "Filter Where" (further down in the "Data - Where" tab).
See the tooltip there (also here attached): the WHERE is already there, so to speak, and must not be used, so your code there must look like

1 = 1 ORDER BY {thistable}.auteuruitlijst ASC

with "1 = 1" being the WHERE which is always true.

 

[pwouda]

What are your dbjoins's Details/Data setting?
Which table column is "Auteur" linked to, which data does it contain (in the database).

There is a table "auteurs" (authors) and a table "auteursuitlijst" (authors from list). In the form "Boeken" (Books) there is an option to add one or more authors from a list.








Enable FabrikDebug in Fabrik Options and append &fabrikdebug=1 to your list URL. This will show you somewhere the generated filter query.

This is the result (2 screenshots) of the query after adding ?fabrikdebug=1 to the url:

 

[pwouda]

It must go into "Filter Where" (further down in the "Data - Where" tab).
See the tooltip there (also here attached): the WHERE is already there, so to speak, and must not be used, so your code there must look like

1 = 1 ORDER BY {thistable}.auteuruitlijst ASC

with "1 = 1" being the WHERE which is always true.

Thanks, but this did not help.

 

[pwouda]

Hi,

I guess I found the reason for the strange sorting behaviour. The sorting is on the "value" of the join, and that is the "id" of the joined table, not the name of the author. So the sorting is on the ID.
Is there a way to do the sorting on the Author, without using the "author" name as the value ? I agree that the "id" is recommended to use for the value.

 

[lousyfool]

Thanks, but this did not help.

Hmm, sorry.

Actually, just tested, and troester's post here worked for me when set to "Label": it would then sort the filter dropdown options from a dbjoin alphabetically.

 

[pwouda]

Thanks for testing. This option did not work directly for me, but after using the debug-function I use "1=1 ORDER BY `text` ASC" and set the Order by in the list-view setting to "none" (as troester's post here suggested)

 

[lousyfool]

... I use "1=1 ORDER BY `text` ASC"
View attachment 20321

And that works? Because in "Joins where..." the WHERE would be required for a condition (as opposed to the "Filter - where" field, which does not allow the WHERE but only the conditional expression), and that means it should be only "ORDER BY `text` ASC" in "Joins where...".

Just wondering...

 

[troester]

@lousyfool :
You can omit the WHERE in Joins where... (it's then added by Fabrik) and so 1=1 won't hurt here.
But you must not use WHERE in Filter where...

@pwouda
You are using a multiselect dbjoin. This will create a child 'repeat' table and so the dbjoin column in your main table is empty (that's why I asked about your settings). So it seems Value/Label settings in the Element/ListView are not working in this case.
By using 'text' Fabrik will do some 'fetch label from repeat table' for you.

 

[pwouda]

@lousyfool :
You can omit the WHERE in Joins where... (it's then added by Fabrik) and so 1=1 won't hurt here.
But you must not use WHERE in Filter where...

@pwouda
You are using a multiselect dbjoin. This will create a child 'repeat' table and so the dbjoin column in your main table is empty (that's why I asked about your settings). So it seems Value/Label settings in the Element/ListView are not working in this case.
By using 'text' Fabrik will do some 'fetch label from repeat table' for you.

Would it be possible and usefull to make this an extra option in the dropdown where you can choose for sorting on "Label", "Value" en "None" in the List view settings, by adding "Text" as an extra option?