• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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
(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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.