order_by() how to use NULLS LAST in active records

[quote pid="381498" dateline="1604564376"]

PHP Code:

you can try this

$this->db->select('a.*, b.*, c.*');
[color=#000000][size=small][font=monospace]$this->db->from('accounts a');[/font][/size][/color]
[color=#000000][size=small][font=monospace]$this->db->join('subscriptions b', 'a.id = b.provider_account_id AND b.subscriber_account_id = '.$client_id, 'left');[/font][/size][/color]
[color=#000000][size=small][font=monospace]$this->db->join('users c', 'c.email = a.name');[/font][/size][/color]
[color=#000000][size=small][font=monospace]$this->db->where('a.user_role', 0);[/font][/size][/color]
[color=#000000][size=small][font=monospace]$this->db->order_by('CASE WHEN b.following IS NULL THEN 0 ELSE 1 END DESC', NULL, FALSE);[/font][/size][/color]
[color=#000000][size=small][font=monospace]$this->db->order_by('b.following', 'desc');[/font][/size][/color]
[color=#000000][size=small][font=monospace]$this->db->order_by('CASE WHEN b.last_updated IS NULL THEN 0 ELSE 1 END DESC', NULL, FALSE);[/font][/size][/color]
$this->db->order_by('b.last_updated', 'desc');

first, we order by its's value NULL or not, and then order again with its real value
we can use the third parameter of order_by() function to disable column name checking, so we are free to use query like that as column name 


[/quote]