order_by() how to use NULLS LAST in active records

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

Try:
$this->db->order_by('b.following', 'desc nulls last');

with the modification:
in the file ./system/database/DB_query_builder.php -> public function order_by($orderby, $direction = '', $escape = NULL)
there is a line:
$direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
-> modify it:
$direction = in_array($direction, array('ASC', 'DESC', 'ASC NULLS LAST', 'DESC NULLS LAST'), TRUE) ? ' '.$direction : '';

Try:
$this->db->order_by('b.following', 'desc nulls last');

with the modification:
in the file ./system/database/DB_query_builder.php -> public function order_by($orderby, $direction = '', $escape = NULL)
there is a line:
$direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
-> modify it:
$direction = in_array($direction, array('ASC', 'DESC', 'ASC NULLS LAST', 'DESC NULLS LAST'), TRUE) ? ' '.$direction : '';

Thanks this works.

You should never modify a system file! When you go to upgrade CodeIgniter what do
you think will happen?

Never Modify System Files.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

You should never modify a system file! When you go to upgrade CodeIgniter what do
you think will happen?

Never Modify System Files.

I know this is not smart move but i didn't find any other solution for this.

A better way to code it is:

in DB_query_builder.php:
1) create a function:

PHP Code:

protected function _format_orderby_direction($direction)
{
    return in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
} 


2) in the function order_by($orderby, $direction = '', $escape = NULL), replace the line:
$direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
by:
$direction = $this->_format_orderby_direction($direction);

and in postgre_driver.php, create a function:

PHP Code:

protected function _format_orderby_direction($direction)
{
    $direction = preg_replace('/\s+/', ' ', $direction);

    $direction_array = explode(' ', $direction, 3);

    $direction_item1 = array_shift($direction_array);

    if ( ! in_array($direction_item1, array('ASC', 'DESC'), TRUE))
    {
        return '';
    }

    if (count($direction_array) > 0)
    {
        $direction_item2 = array_shift($direction_array);

        if (($direction_item2 !== 'NULLS')
            || (count($direction_array) == 0)
        ) {
            return '';
        }

        $direction_item3 = array_shift($direction_array);

        if ( ! in_array($direction_item3, array('FIRST', 'LAST'), TRUE))
        {
            return '';
        }
    }

    return ' '.$direction;
} 


Vincent

Or put in a feature request to the development team.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

[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]