Case and conditional statements in Active Record

[eluser]vinofilus[/eluser]
Question: Can I use several CASE and IF statements with Active Record, or should I just go back to loading the sql?

My example:
I'm creating a sort of master temporary table in my model from which I'll manipulate the data in the controller. I have created a query that does what I want in sql, which looks something like this. The relevant stuff is just in the first few lines.

Code:

select S.first_name, S.last_name,  C.classif_name, R.rule_name, J.start_date, G.completion_date, R.ruleid,
CASE
WHEN R.ruleid=3 THEN IF(DATEDIFF(curdate(),G.completion_date) < 365,'within 1 year','FLAG more then 1 year')
WHEN R.ruleid=5 THEN IF(DATEDIFF(curdate(),G.completion_date) < 1095 ,'within 3 years','FLAG more than 3 years')
END AS 'FLAG'
from
    Staff as S
        join Classifications as C
        join Staff_Classif as J
        join Programs as P
        join Prog_Classif as K
        join Rules as R
   etc., etc.,
where
    C.classifid = J.classif_id
        and S.staffid = J.staff_id
        and P.progid = K.prog_id
        and C.classifid = K.classif_id
   etc., etc.,

I can get this query to work fine (doing it in mysql workbench), but when I try to convert to active record in my CI model, the CASE and IF parts throw me off.
I have all the selects and joins working (here's an excerpt of the first few lines):

Code:

$this->db->select('S.first_name, S.last_name, C.classif_name, N.address, P.prog_name, P.short_name, R.rule_name, J.start_date, G.completion_date, R.ruleid')
  ->from ('Staff as S')
  ->join ('Staff_Classif as J', 'S.staffid = J.staff_id')
  ->join ('Classifications as C', 'C.classifid = J.classif_id')
  ->join ('Prog_Classif as K', 'classifid = K.classif_id' )
   etc., etc., etc.,

But any attempt at adding even a single CASE into that just fails.

I did try something like:

Code:

$this->db->select('S.first_name, S.last_name, C.classif_name, N.address, P.prog_name, P.short_name, R.rule_name, J.start_date, G.completion_date, R.ruleid, CASE WHEN R.ruleid=3 THEN R.ruleid ELSE null', FALSE)

as an experiment, but I don't even appear to be close with that.

Is there a way to do this using Active Record?
Is that even wise? Should I just load in the sql and run it?
I have about 15 or more rules/cases that I need to check in addition to the 2 examples above.

Any helpful advice is greatly appreciated.

[eluser]PhilTem[/eluser]
Nope, AR is just helpful for "basic" queries, as soon as you want to use subqueries it does not work anymore (at least not going down the "easy"-road, there are some post on these forums that show it can be done somehow).

However, I'd recommend using binding queries so that escaping will be done but it will be a pure SQL statement nothing built by AR.

[eluser]vinofilus[/eluser]
Thank you!
That fits my hunches. I was starting to wonder why I was feeling pressed to use AR when it was seeming the more difficult choice for this query. Sometimes it's easy to get stuck doing things one way, and missing the obvious and easier way.
Thanks again!

[eluser]CroNiX[/eluser]
You can do a subquery, just not totally using active record. Example using a separate select:

You just have to manually escape anything that is user input, and use FALSE as the 2nd parameter to db:elect() so that AR won't escape the identifiers which will mess up the query.

Code:

$this->db
  ->select('id, field1, field2')
  ->select('(SELECT COUNT(id) FROM tableY WHERE tableY.' . $this->db->escape('field1') . ' = tableX.id) as image_count', FALSE)
  ->get('tableX');

You can do the same with any query, like your CASE statements. It's just more complex queries can't use the built in "protect identifiers" because it's too complex for CI to figure out and escape them properly. They'd have to extensively extend the AR class to be able to work in all situations, which would be quite bloated and complex IMO.

[eluser]vinofilus[/eluser]
[quote author="CroNiX" date="1354043849"]You can do a subquery, just not totally using active record.

You can do the same with any query, like your CASE statements. It's just more complex queries can't use the built in "protect identifiers" because it's too complex for CI to figure out and escape them properly. They'd have to extensively extend the AR class to be able to work in all situations, which would be quite bloated and complex IMO.[/quote]

Thanks for that. That makes sense.
But in this case, I'm quickly realizing that a regular query is going to be easier to understand, easier to throw in arguments from the controller or tweak down the road in a year or two, and as a bonus, none of the query depends on user input, so I just have protect against my own mistakes, which I think I can escape adequately.
Thanks again.