is there a way to control where a blank date is sorted?

[skyrun]

i have a use-case with a 'deadline' date where if you don't fill in a date (date=blank), then that's like no deadline. i woudl like to sort the column by date ascending to show the closest deadlines first, then the blanks after that.

so it would be like a default sort on the list that would do this:
select * from work_orders ORDER BY (date_column IS NULL), date_column ASC

perhaps an option on the order by to sort nulls first or last on the order by's? and/or an option on the element to sort nulls first or last (for when the list is sorted by clicking the heading).

perhaps it can be done already through some nifty trick?

 

[cheesegrits]

I can't think of a nifty trick. I thought maybe using an onQueryBuilt hook in a PHP list plugin might help, but I don't think the J! query builder lets you change parts of the query once they've been added. So modifying the order by clause already there in a query object would be tricky (I don't offhand know if it's possible to hack it, if J! doesn't support changing it).

So yes, it would need to be added as an option ("Empty last") on element types that would support it, and override the getOrderByName() method in the element model for those types. I'm not entirely sure which types would support the "ISNULL(foo), foo ASC" syntax. And some might do it differently, so the date element would probably need "foo = '0000-00-00 00:00:00', ISNULL(foo), foo ASC", to account for either null or "empty" dates.

This wouldn't be entirely trivial, as in addition to adding YAFO to the element model(s), there's some gnarly handling in the list model to detect what elements are being used in the order by, which has to figure out if the order by name is a simple element name or "something else" (like a CONCAT on a join).

So it's not something I'd do as part of subscription support, it'd have to be billable work.

Also, according to a Stack Overflow comment on this topic, MySQL won't use an index if you add ISNULL(foo) to an order by, so you'd have to take that hit. Which may or may not be significant, depending on the size and cardinality of your table / field.

-- hugh

 

[cheesegrits]

It might be worth experimenting with the onQueryBuilt hook, and see if you can dick around with the existing order by clause, if you want a cheap and cheerful solution.

-- hugh

 

[skyrun]

thanks hugh. would this be something i can hire you to do?

 

[cheesegrits]

Yes.

-- hugh