Welcome Guest, Not a member yet? Register   Sign In
ActiveRecords named queries


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:

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)
  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)

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.


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

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.

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.

Theme © iAndrew 2016 - Forum software by © MyBB