Passing parameters to a Query built via plain-text SQL
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.