Fork me on GitHub
Please upgrade to 2.1.x here. The 2.0 branch is no longer being updated.
Important security fix: 2.1.10, released 4 May.

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.