• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
ActiveRecords named queries

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



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

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

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.