CodeIgniter Forums
Is there any way to write union with query builder? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Is there any way to write union with query builder? (/showthread.php?tid=70174)



Is there any way to write union with query builder? - xiius - 03-04-2018

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

Code:
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 thing, you can see I created a virtual column named 'source', i want to filter this column using where clause like below


Code:
WHERE source like "%""%" 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.


RE: Is there any way to write union with query builder? - InsiteFX - 03-05-2018

See this article:

UNION query with codeigniter's active record pattern


RE: Is there any way to write union with query builder? - php_rocs - 03-05-2018

@xiius,

Don't forget you can also make a database view (out of the query above) and use query builder to call the view. (Just a suggestion)


RE: Is there any way to write union with query builder? - xiius - 03-05-2018

(03-05-2018, 08:20 AM)php_rocs Wrote: @xiius,

Don't forget you can also make a database view (out of the query above) and use query builder to call the view. (Just a suggestion)

How could I? I don't have any idea about this, will you please give me some resource link please.


RE: Is there any way to write union with query builder? - php_rocs - 03-05-2018

@xiis
What tool do you use to manage your MySQL database? ...or you could just Google "create MySQL view"