Welcome Guest, Not a member yet? Register   Sign In
order_by() how to use NULLS LAST in active records
#1

I am using Potgresql with Codeigniter3 and i have query that have null values in some columns that i need to use for order_by() on those columns.

When i tried to run this query in pgAdmin it works and moving null values on last place, which is fine

Code:
SELECT a.*, b.*, c.*
FROM accounts a
LEFT JOIN subscriptions b ON a.id = b.provider_account_id AND b.subscriber_account_id = 10
JOIN users c ON c.email = a.name
WHERE a.user_role = 0
ORDER BY b.following DESC NULLS LAST, b.last_updated DESC NULLS LAST


result
Code:
-----------------------------------
| following | last_updated        |
-----------------------------------
| 1         | 2020-11-04 11:08:44 |
-----------------------------------
| 0         | 2020-11-04 11:22:07 |
-----------------------------------
| null      | null                |
-----------------------------------



here is query from model which doesn't put nulls on last place

Code:
$this->db->select('a.*, b.*, c.*');
$this->db->from('accounts a');
$this->db->join('subscriptions b', 'a.id = b.provider_account_id AND b.subscriber_account_id = '.$client_id, 'left');
$this->db->join('users c', 'c.email = a.name');
$this->db->where('a.user_role', 0);
$this->db->order_by('b.following', 'desc');
$this->db->order_by('b.last_updated', 'desc');

result

Code:
-----------------------------------
| following | last_updated        |
-----------------------------------
| null      | null                |
-----------------------------------
| 1         | 2020-11-04 11:08:44 |
-----------------------------------
| 0         | 2020-11-04 11:22:07 |
-----------------------------------

how i can implement NULLS LAST in active record in codeigniter in order_by() function
Reply


Messages In This Thread
order_by() how to use NULLS LAST in active records - by brianjamestorr - 11-05-2020, 01:19 AM



Theme © iAndrew 2016 - Forum software by © MyBB