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