CODEWITHSUNDEEP
phpmysqlcodeigniter
OM The Eternity
OM The Eternity

Reputation: 16234

How to implement Codeigniter syntaxed single where and orderby clause to multiple "get" queries?

I have a 2 get queries which is merged at the end using array_merge(), but before that my where and order_by clause gets implemented on first executing get query only as per OOPS concept. But I want this where and order_by clause to be implemented on both the queries, without writing a separate where and order_by clause for second executing query. My Code is below:

$this->db->where('lower(status) <>', 'completed');
$this->db->order_by('calldate','asc');

$q1 = $this->db->get('records'); //where and orderby applies to this query only
$q2 = $this->db->get('other_records'); //where and orderby does not apply to this
$data1 = $q1->result_array();
$data2 = $q2->result_array();
$data = array_merge($data1, $data2);

Upvotes: 0

Views: 46

Answers (2)

Zamrony P. Juhara
Zamrony P. Juhara

Reputation: 5262

You can write raw SQL with UNION. for example

SELECT * FROM records WHERE lower(status)<>'completed' ORDER BY calldate ASC
UNION
SELECT * FROM other_records WHERE lower(status)<>'completed' ORDER BY calldate ASC

Then you can avoid sending 2 separate queries and merging array. It reduces network round-trip to send queries and CPU resource needed to merge array.

Upvotes: 1

safin chacko
safin chacko

Reputation: 1390

Try this

        $this->db->where('lower(status) <>', 'completed');
        $this->db->order_by('calldate','asc');

        $q = $this->db->get('records'); //where and orderby applies to this query only
        $data1 = $q->result_array();
        $q = $this->db->get('other_records'); //where and orderby does not apply to this

        $data2 = $q->result_array();
        $data = array_merge($data1, $data2);

Upvotes: 1

Related Questions