List order by dbjoin using labels instead of values

[dimoss]

J! 4.3.3
FabrikDelta
PHP 8.1

Hi,

I use a dbjoin element in Order By in a list.
The dbjoin is using the id values which are integer.
However the Order considers the label and not the values when ordering.
When I use a simple dropdown element it works.
Thanks,

 

[troester]

I can't replicate.
Is it a single dbjoin (no checkbox or multiselect)?

With Fabrik Debug enabled and &fabrikdebug=1 appended to your URL:
What do you get in 'List GetData'?

 

[dimoss]

Hi @troester

Here is a part of the 'List GetData'

FROM `fab_rnk_u21`
LEFT JOIN  `fab_months` AS `fab_months` ON `fab_months`.`id` = `fab_rnk_u21`.`mnth`
WHERE (   LOWER(`fab_rnk_u21`.`name`) regexp LOWER('rembert')  )
ORDER BY  `fab_rnk_u21`.`yr` DESC, `fab_months`.`month` DESC LIMIT 50

The `fab_months`.`month` is the label of the DB Joined table. It should be `fab_months`.`id`

Below you see the setting of the join.

 

[troester]

Sorry, I misunderstood your problem.

Yes, a dbjoin element used in ORDER BY is ordering with the label.

 

[dimoss]

Sorry, I misunderstood your problem.

Yes, a dbjoin element used in ORDER BY is ordering with the label.

Thanks @troester .
I suppose this is by Fabrik design which means I need to change my setup and use Dropdown instead if I want to ORDER BY value and not label, right?

 

[troester]

I think so, yes. In Prefilters there's a xy(raw) selection for dbjoin elements but not in the 'Order by'.

Dropdown vs. Dbjoin:
It depends on what you need, e.g. for Related Data it must be a dbjoin.

You could also add one more dbjoin with id/id (watching the other one or filled via php plugin) and use this for ordering.
Or maybe something can be done with list php_events. There's a OnPreloadData, but no idea what can be done with it.

 

[dimoss]

I think so, yes. In Prefilters there's a xy(raw) selection for dbjoin elements but not in the 'Order by'.

You could also add one more dbjoin with id/id (watching the other one or filled via php plugin) and use this for ordering.

I guess you mean to add a dummy dbjoin element id/id for use only in list ORDER BY.
Or filled by php plugin? I dont know how to do this

 

[troester]

I think I got you wrong. You want to order the dropdown itself?
You can use Data-where: add ORDER BY {thistable}.id