It looks like you're new here. If you want to get involved, click one of these buttons!
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.