CodeIgniter Forums
ActiveRecords named queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: ActiveRecords named queries (/thread-52422.html)



ActiveRecords named queries - El Forum - 06-10-2012

[eluser]fatman[/eluser]
Hi,

I'm using CodeIgniter 1.7.2, and i have a long query with about 14 parameters to bind.
The catch is that there are actually only 2 params that i need to bind and they repeat themselves.

Is it possible to tell ActiveRecords to use the same variable over and over so for example:

Code:
SELECT agg.createDate, SUM(agg.leadQty) AS leadQty,
  SUM(agg.leadSuccess) AS leadSuccess, ROUND((SUM(agg.leadSuccess)/SUM(agg.leadQty))*100) AS 'leadSuccessP',
  SUM(agg.leadFailure) AS leadFailure
  FROM (
  SELECT DATE(createDate) AS createDate, COUNT(leadId) AS leadQty, 0 AS leadSuccess, 0 AS leadFailure
  FROM analyticengine.lds_Leads_vw
  where createDate >= ?
  and createDate < ?
  GROUP BY DATE(createDate)
  UNION ALL
  SELECT DATE(createDate) AS createDate, 0 AS leadQty, COUNT(leadId) AS leadSuccess, 0 AS leadFailure
  FROM analyticengine.lds_Leads_vw
  WHERE leadStatusId = 1
  AND createDate >= ?
  AND createDate < ?
  GROUP BY DATE(createDate)
  UNION ALL...

instead of sending the same date over and over i'd like replace the '?' with ':date' and tell it to replace all of ':date' with the same variable.





ActiveRecords named queries - El Forum - 06-10-2012

[eluser]CroNiX[/eluser]
Sure, by not using query bindings and using active record instead (which also escapes your variables). Other than that, I don't think so.


ActiveRecords named queries - El Forum - 06-11-2012

[eluser]fatman[/eluser]
I am using the query() method of the active records. I think that using the helper methods in this case would be a nightmare because ,as you can see, this is not a simple query.


ActiveRecords named queries - El Forum - 06-11-2012

[eluser]CroNiX[/eluser]
query() is not active record (notice query() isn't in the active record section of the docs). It's just a straight up query that you supply the entire SQL instead of using the active record methods to build the SQL. I didn't say it would be easier, but it was the answer to your question.