Using union in codeigniter query builder, and filter in a virtual mysql column

Question

In my project I am using datatables plugin with serverside processing. It works fine untill i do a search or order(sort) operation because it needs active record to do that.

My scenario is, i have an account table, revenue table and payment table, and I want to view all the data of revenue and payment table, thats why I need a union. my query is like below---

SELECT 'Income' as source, fld_type, fld_amount, ta.fld_account as account,  fld_date, tbl_revenue.fld_description as fld_traninfo, tbl_revenue.fld_created as created
        FROM tbl_revenue JOIN tbl_accounts as ta on tbl_revenue.fld_account_id = ta.fld_id
UNION
SELECT 'Expense' as source, fld_type, fld_amount, tae.fld_account, fld_date, tbl_payment.fld_description as fld_traninfo, tbl_payment.fld_created as created
        FROM tbl_payment JOIN tbl_accounts as tae on tbl_payment.fld_account_id = tae.fld_id

Is there any way to use query builder in this query?

And second question, you can see I created a virtual column named 'source', i want to filter this column using where clause with append this query like below

WHERE source like "%a%" limit(10,0)

But this returns that I don't have any column name 'source', how can I filter this column?

Any help is appreciated.

Yes I can, but after this i cannot add any modification to this query, like after this i want to order it, $this->db->order_by('id',asc); it wont work — aziz, Mar 05 '18 at 05:25
It will be order from outside as per provided column name and direction asc or desc — aziz, Mar 05 '18 at 05:29

score 1 · Accepted Answer · answered Mar 05 '18 at 06:55

1

there is a way to do that but its a bit hacky because codeigniter's querybuilder adds an auto SELECT statement to the query if you didn't specify it by yourself

In order to get what you want, you've to split your select statements in 2 queries and add the where clause to this query

Something like that should work:

$strQuery1 = $this->db
    ->select('income as source, fld_type, fld_amount, ta.fld_account as account,  fld_date, tbl_revenue.fld_description as fld_traninfo, tbl_revenue.fld_created as created')
    ->from('tbl_revenue')
    ->join('tbl_accounts as ta', 'tbl_revenue.fld_account_id = ta.fld_id')
    ->get_compiled_select();

$strQuery2 = $this->db
    ->select('Expense as source, fld_type, fld_amount, ta.fld_account as account,  fld_date, tbl_revenue.fld_description as fld_traninfo, tbl_revenue.fld_created as created')
    ->from('tbl_payment')
    ->join('tbl_accounts as ta', 'tbl_revenue.fld_account_id = ta.fld_id')
    ->get_compiled_select();

$strWhere = substr($this->db->like('source', 'a', 'both')->get_compiled_select(), 8);

$query = $this->db->query($strQuery1.' UNION '.$strQuery2.$strWhere);

answered Mar 05 '18 at 06:55

Atural

5,389
5
18
35

what is the meaning of 8 at strWhere? – aziz Mar 05 '18 at 07:02
as i said in my answer the where clause would look like `SELECT * WHERE source LIKE '%a%' ESCAPE '!'` so it removes the `SELECT *` part because the querybuilder adds the `select` statement per default – Atural Mar 05 '18 at 07:03
did it help you ? – Atural Mar 05 '18 at 07:31
how can i order this table? and , source is not a column in fld_list – aziz Mar 05 '18 at 07:38
what do you mean with `source` is not a column, this query does exact what you described in your question - and the order by part isn't that hard - just add an `order_by` after the `like` function - something like `$this->db->like('source', 'a', 'both')->order_by('your_field', 'ASC')->get_compiled_select()` should work – Atural Mar 05 '18 at 07:45
yeah hope that works fine, thank you so much for your kind help, – aziz Mar 05 '18 at 07:52

Using union in codeigniter query builder, and filter in a virtual mysql column

1 Answers1