Fork me on GitHub
Current Releases are 2.1 stable (major release) and 2.0.18.11 (security patch for 2.0)
We've also rebooted our GitHub repo, docs, and more!

Passing parameters to a Query built via plain-text SQL

edited March 2012 in Questions

Hi again,
I'm finally progressing with my plugin, but now I'm stuck on an apparently small issue. I created a Model and need to run a query which, due to some limitations in Database objects, has to be written as in plain SQL. That's not a big deal and, in fact, such SQL works perfectly if ran manually.

However, this query needs parameters to run, and I can't figure out how to pass them to the SQL object together with the statement. The original query runs against custom tables, but I created a simpler one with an equivalent structure to illustrate the concept:

-- Retrieve the top 10 posters in a period of time
SELECT
Totals.UserID
,SUM(Totals.Total) AS GrandTotal
,U.Name
FROM
(
-- Retrieve total amount of discussions started by each User
-- in a period of time
SELECT
D.InsertUserID AS UserID
,COUNT(D.InsertUserID) AS Total
FROM gdn_discussion D
WHERE
(D.DateInserted >= :StartDate) AND
(D.DateInserted < :EndDate)
GROUP BY
D.InsertUserID

UNION ALL

-- Retrieve total amount of comments by each User
-- in a period of time
SELECT
C.InsertUserID AS UserID
,COUNT(C.InsertUserID) AS Total
FROM gdn_comment C
WHERE
(C.DateInserted >= :StartDate) AND
(C.DateInserted < :EndDate)
GROUP BY
C.InsertUserID
) AS Totals
JOIN
gdn_user U ON
(U.UserID = Totals.UserID)
GROUP BY Totals.UserID
ORDER BY GrandTotal DESC
LIMIT 10

The parameters I need to pass are :StartDate and :EndDate, which appear twice in the query. These dates will be chosen by the User and, obviously, they must be properly sanitized. I usually rely on framework's Database API to do it, but Vanilla doesn't seem to support this logic.

Any help is welcome, thanks.

Tagged:

Answers

Sign In or Register to comment.