[brianjamestorr]

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

[vincent78]

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 : '';

[brianjamestorr]

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.

[InsiteFX]

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

Never Modify System Files.

[brianjamestorr]

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.

[vincent78]

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

[InsiteFX]

Or put in a feature request to the development team.