order_by() how to use NULLS LAST in active records

order_by() how to use NULLS LAST in active records - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: order_by() how to use NULLS LAST in active records (/showthread.php?tid=77925)

order_by() how to use NULLS LAST in active records - brianjamestorr - 11-05-2020

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

RE: order_by() how to use NULLS LAST in active records - vincent78 - 11-05-2020

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

RE: order_by() how to use NULLS LAST in active records - brianjamestorr - 11-05-2020

(11-05-2020, 03:54 AM)vincent78 Wrote: 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.

RE: order_by() how to use NULLS LAST in active records - InsiteFX - 11-05-2020

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

Never Modify System Files.

RE: order_by() how to use NULLS LAST in active records - brianjamestorr - 11-05-2020

(11-05-2020, 12:32 PM)InsiteFX Wrote: 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.

RE: order_by() how to use NULLS LAST in active records - vincent78 - 11-06-2020

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

RE: order_by() how to use NULLS LAST in active records - InsiteFX - 11-06-2020

Or put in a feature request to the development team.

RE: order_by() how to use NULLS LAST in active records - Reynaldi Ruspandi - 05-29-2021

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