Custom list ordering implementation

[Makulia]

Hello Community!

I have a list with usernames and the birthdays dates. I want to make a module that render users birthdays of the current month. I tool list module and set custom list ordering like:
SELECT `birthday` FROM `portal_users` WHERE MONTH(birthday) = MONTH(NOW()) ORDER BY DAYOFMONTH(birthday) DESC

I have put this query into prefilter but it ignores the "Order by" part. How to make this work?

Thanks!

 

[troester]

Set the prefilter to select the records and add your birthday element to "Ordering"

 

[Makulia]

Set the prefilter to select the records and add your birthday element to "Ordering"

If I set Order by "birthday" with Desc option it will order dates by year! But I need to order them by day!

 

[troester]

Ah, yes.
So I would create a MySQL view and link a fabrik list to this view.

 

[Makulia]

Ah, yes.
So I would create a MySQL view and link a fabrik list to this view.

Could you, please, be more specific on how to link MySQL view to fabrik list? Thank you!

 

[Makulia]

I have tried to put Mysql view into the prefilter query

$bday = CREATE VIEW Birthdays AS SELECT `birthday` FROM `portal_users` WHERE MONTH(birthday) = MONTH(NOW()) ORDER BY DAYOFMONTH(birthday) DESC

and then add this view through

$q->$bday

to the list prefilter. But list order has not changed.

 

[troester]

Create the view in your DB with e.g. phpMyAdmin.
Create a Fabrik list and select this view from "Database table" in Data tab (you can link a Fabrik list to any existing table or view in your DB).
Save the list, edit again and select an element as primary key (it's not really needed because the view is readonly but it has to be defined).

 

[Makulia]

Thank you, I'll try this and post my results!

 

[Makulia]

I have followed your advice and created mysql view with this sql:

select date_format(`kadisportal`.`portal_users`.`birthday`,'%d.%m') AS `birthday`,`kadisportal`.`portal_users`.`name` AS `name` from `kadisportal`.`portal_users` where (month(`kadisportal`.`portal_users`.`birthday`) between month(now()) and month((now() + interval 30 day))) order by month(`kadisportal`.`portal_users`.`birthday`),dayofmonth(`kadisportal`.`portal_users`.`birthday`)

Instead of using fabrik list, I wrote my own custom joomla module which does the listing.
If you are interested in details just let me know.
And thank you for the great idea with MySQL view!