Fork me on GitHub
Current release is 2.1.5 (31 Oct 2014).

Users who have not yet upgraded to 2.1 should get security release 2.0.18.14 (1 Nov 2014). We will stop providing these security releases to 2.0 at the end of this year.

Passing parameters to a Query built via plain-text SQL

businessdadbusinessdad Stealth contributor MVP
edited March 2012 in Vanilla 2.0 Help

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.