CODEWITHSUNDEEP
mysqldatabasecodeigniteractiverecordquery-builder
aziz
aziz

Reputation: 336

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

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.

Upvotes: 0

Views: 1049

Answers (1)

Atural
Atural

Reputation: 5439

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

Upvotes: 1

Related Questions