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
#2

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 : '';
Reply
#3

(This post was last modified: 11-05-2020, 04:26 AM by brianjamestorr.)

(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.
Reply
#4

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 )
Reply
#5

(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.
Reply
#6

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(' '$direction3);

    
$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
Reply
#7

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 )
Reply
#8

[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');

firstwe 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]
Reply




Theme © iAndrew 2016 - Forum software by © MyBB