Codeigniter mysql query using UNION to select values equal to today's date first

Question

I want to modify the following query from a model on Codeigniter:

public $table                 = 'fi_client_notes';
public $primary_key           = 'fi_client_notes.client_note_id';

public function default_select()
{
    $this->db->select("SQL_CALC_FOUND_ROWS fi_client_notes.*, fi_clients.client_name", FALSE);

    if ($this->session->userdata('user_id') <> 1)
    {
        $this->db->where('fi_clients.user_id', $this->session->userdata('user_id'));
    }
}

public function default_order_by()
{
        $this->db->order_by('fi_client_notes.client_note_date DESC');
}

public function default_join()
{
    $this->db->join('fi_clients', 'fi_clients.client_id = fi_client_notes.client_id');
}

My goal is to select first only the rows where the value "fi_client_notes.client_note_end" is equal to today's date, then show all the rest of the rows arranged in descending order based on the value "fi_client_notes.client_note_date".

This is the query I'm trying:

(SELECT SQL_CALC_FOUND_ROWS fi_clients.client_name, fi_client_notes.* FROM (`fi_client_notes`) JOIN `fi_clients` ON `fi_clients`.`client_id` = `fi_client_notes`.`client_id` WHERE `fi_client_notes`.`client_note_end` = CURDATE())
UNION
(SELECT fi_clients.client_name, fi_client_notes.* FROM (`fi_client_notes`) JOIN `fi_clients` ON `fi_clients`.`client_id` = `fi_client_notes`.`client_id`
ORDER BY `fi_client_notes`.`client_note_date` DESC LIMIT 15)

But I don't know how to use it on Codeigniter, since CodeIgniter's ActiveRecord doesn't support UNION

splash58 · Accepted Answer · 2018-01-18 16:59:30Z

1

in this case you can get result without union, just add n.client_note_end <> CURDATE() as top level for order by clause

SELECT c.client_name, n.* 
     FROM `fi_client_notes` as n 
       JOIN
          `fi_clients` as c
         ON c.`client_id` = n.`client_id`
     ORDER BY (n.`client_note_end` <> CURDATE()),
              `n.`client_note_date` DESC LIMIT 15

edited Jan 18, 2018 at 16:59

answered Jan 18, 2018 at 16:54

splash58

26.2k3 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DavidM Over a year ago

That works perfectly, although I don't understand the meaning of ORDER BY (n.client_note_end <> CURDATE()), n.client_note_date DESC LIMIT 15. What does <> mean in this case?

splash58 Over a year ago

really it sorted by 0 . n.client_note_date and 1 . n.client_note_date because mysql casts boolean false and true to 0 and 1

splash58 Over a year ago

<> means not equal so with today date it returns false

splash58 Over a year ago

@DavidM have you understood?

splash58 Over a year ago

mysql cast it to 1 and all this records will be placed after that for which 0 (false) returned, because we sort the first key ascending. And then keys are sorted descending in each group

|

Collectives™ on Stack Overflow

Codeigniter mysql query using UNION to select values equal to today's date first

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related