+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: converting sql query strings to query builder (/showthread.php?tid=66294)
Pages:
1
2
converting sql query strings to query builder - ozzy mandiaz - 10-05-2016
I'll preface this by saying I'm trying to convert an old system from codeigniter 2.2 to 3.1. it works. and it works fine. just don't want to continue developing for 2.2. decided to move to 3.1 as an experiment.
I have the query below and several others that are similar that I'm trying to modify to use to query builder. and I really am at a loss as to how to do unions with it. its a relatively simple query with 2 (some have more) unions and joins and stuff. And I'm at a loss as to how to do it. somebody/anybody care to throw me a bone on this one?
any help - and i do mean any help - is greatly appreciated.
thanks in advance....
om.
--------------------------------------------------------------------
SELECT
'1' AS pos, IFNULL(SUM(Revenue), 0.00) AS unbilled
FROM
TimeSheet t
INNER JOIN
Clients c ON t.ClientNo = c.ClientNo
WHERE
c.ClientNo IN ('10')
AND TheDate BETWEEN CAST('2012-12-06' AS DATE) AND NOW()
AND (t.InvoiceDate IS NULL
OR t.InvoiceDate > NOW()
OR t.InvoiceDate LIKE '0000-00-00')
UNION ALL SELECT
'2' AS pos, IFNULL(SUM(Revenue), 0.00) AS unbilled
FROM
TimeSheet t
INNER JOIN
Clients c ON t.ClientNo = c.ClientNo
INNER JOIN
Employees e ON e.ID = t.EmployeeID
WHERE
c.ClientNo IN ('10')
AND TheDate BETWEEN CAST('2012-12-06' AS DATE) AND NOW()
AND (t.InvoiceDate IS NULL
OR t.InvoiceDate > NOW()
OR t.InvoiceDate LIKE '0000-00-00')
AND e.Active = '2'
UNION ALL SELECT
'3' AS pos, IFNULL(SUM(Revenue), 0.00) AS unbilled
FROM
TimeSheet t
INNER JOIN
Clients c ON t.ClientNo = c.ClientNo
INNER JOIN
Employees e ON e.ID = t.EmployeeID
WHERE
c.ClientNo IN ('10')
AND TheDate BETWEEN CAST('2012-12-06' AS DATE) AND NOW()
AND (t.InvoiceDate IS NULL
OR t.InvoiceDate > NOW()
OR t.InvoiceDate LIKE '0000-00-00')
AND (e.Active = '1' OR e.Active = '0')
--------------------------------------------------------------------
RE: converting sql query strings to query builder - PaulD - 10-05-2016
LOL - I bow down to the great Ozymandias and do indeed despair when I look at your works!
Relatively simple query!!! - F*** me, that is more complex than any query I have ever written. I cannot help you oh great one, but I just had to comment.
PS Thanks for making me feel like a complete and utter amateur!
RE: converting sql query strings to query builder - Narf - 10-05-2016
You would gain nothing from writing such queries with the Query Builder.
Quite the opposite - you'd (and already have) lose time trying to do it, only to add overhead.
RE: converting sql query strings to query builder - ozzy mandiaz - 10-05-2016
(10-05-2016, 02:17 PM)Narf Wrote: You would gain nothing from writing such queries with the Query Builder.
Quite the opposite - you'd (and already have) lose time trying to do it, only to add overhead.
so then there's no reason to use query builder on queries like this? under what circumstances would it no longer be a waste of time and (mental) energy?
just trying to understand
RE: converting sql query strings to query builder - Narf - 10-06-2016
(10-05-2016, 03:37 PM)ozzy mandiaz Wrote:(10-05-2016, 02:17 PM)Narf Wrote: You would gain nothing from writing such queries with the Query Builder.
Quite the opposite - you'd (and already have) lose time trying to do it, only to add overhead.
so then there's no reason to use query builder on queries like this? under what circumstances would it no longer be a waste of time and (mental) energy?
just trying to understand
When you are building an application for others to run, e.g. something like a generic forum board, and most importantly - when that application should be able to run on multiple/different database platforms.
RE: converting sql query strings to query builder - rtenny - 10-06-2016
Wow, wow, wow, What a query. I will print this out and put in on the wall.
Something to keep me motivated to learn, learn, learn.
I have been programming and using sql for more then 10 years but this is defo the mother of all queries.
RE: converting sql query strings to query builder - Narf - 10-06-2016
(10-06-2016, 06:17 AM)rtenny Wrote: Wow, wow, wow, What a query. I will print this out and put in on the wall.
Something to keep me motivated to learn, learn, learn.
I have been programming and using sql for more then 10 years but this is defo the mother of all queries.
This one's just a UNION of 3 quite simple queries, it only looks complex at first glance.
RE: converting sql query strings to query builder - ozzy mandiaz - 10-06-2016
(10-06-2016, 06:22 AM)Narf Wrote:(10-06-2016, 06:17 AM)rtenny Wrote: Wow, wow, wow, What a query. I will print this out and put in on the wall.
Something to keep me motivated to learn, learn, learn.
I have been programming and using sql for more then 10 years but this is defo the mother of all queries.
This one's just a UNION of 3 quite simple queries, it only looks complex at first glance.
thanks... i was beginning to wonder what all the fuss was. like you said... nothing complicated, just a series of unions joining 3 simple queries...
RE: converting sql query strings to query builder - Narf - 10-06-2016
(10-06-2016, 06:43 AM)ozzy mandiaz Wrote:(10-06-2016, 06:22 AM)Narf Wrote:(10-06-2016, 06:17 AM)rtenny Wrote: Wow, wow, wow, What a query. I will print this out and put in on the wall.
Something to keep me motivated to learn, learn, learn.
I have been programming and using sql for more then 10 years but this is defo the mother of all queries.
This one's just a UNION of 3 quite simple queries, it only looks complex at first glance.
thanks... i was beginning to wonder what all the fuss was. like you said... nothing complicated, just a series of unions joining 3 simple queries...
Your signature:
Quote:And on the pedestal these words appear:
'My name is Ozymandias, king of kings:
Look on my works, ye Mighty, and despair!'
Nothing beside remains. Round the decay
Of that colossal wreck, boundless and bare
The lone and level sands stretch far away.
... might have something to do with all the fuss.
RE: converting sql query strings to query builder - dave friend - 10-07-2016
Quote:under what circumstances would it no longer be a waste of time and (mental) energy? just trying to understand
A couple of possible circumstances come to mind. The first is a case requiring an extra "where" statement (or two, or more) based on some conditions(s).
The second is where you are implementing pagination. Typically you want a count of all relevant records before applying a limit & offset and retrieving that sub-set of records. Query Builder makes this process easy to implement.
That said, most of the time good old
Code:
$this->db->query('YOUR QUERY HERE');