Welcome Guest, Not a member yet? Register   Sign In
converting sql query strings to query builder
#1
Question 

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')
--------------------------------------------------------------------
OM...

-------------------------
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.
Reply
#2

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!
Reply
#3

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.
Reply
#4

(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
OM...

-------------------------
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.
Reply
#5

(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.
Reply
#6

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.
On the package it said needs Windows 7 or better. So I installed Linux.
Reply
#7

(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.
Reply
#8

(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...
OM...

-------------------------
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.
Reply
#9

(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. Smile
Reply
#10

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');
is easier and more efficient.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB