Passing parameters to a Query built via plain-text SQL

businessdad · March 2012

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.

UnderDog · March 2012

businessdad said: 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

Can I ask how you concluded that?

jspautsch · March 2012

UnderDog said:

businessdad said: 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

Can I ask how you concluded that?

I think it's because of the Union All, Vanilla does the same thing with the search query.

businessdad · March 2012

jspautsch said:

UnderDog said:

businessdad said: 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

Can I ask how you concluded that?

I think it's because of the Union All, Vanilla does the same thing with the search query.

Precisely. Database objects don't allow to build a Union (at least, not easily, as I haven't found an example anywhere). Besides, I need to run an aggregate query on the Union query, which also seems not possible in Vanilla.

Some people, in past posts, suggested to "just run the query separately and merge the resulting arrays", but this is a bad approach at best, as I would find myself with a giant array and aggregation would have to be done by code, in the dreaded RBAR way. :(

businessdad · March 2012

I think I found the answer to my own question.
- Function Gdn_SQLDriver->Get() calls Gdn_SQLDriver->Query(), which accept a plain text SQL Statement as a parameter.
- Inside the function, I found the call to Database->Query($Sql, $this->_NamedParameters, $QueryOptions).
- Going deeper, I finally found out that Database->Query() calls PDO::execute, which accepts an array of parameters (named or positional).

Now I have the following options:
1- Override function Gdn_SQL->Query() and allow it to accept an array of parameters. I'm afraid this would complicate things, as it seems like a hack to me.
2- Manually populate array of Gdn_SQL->NamedParams by calling Gdn_SQL->NamedParameter() directly, before I execute the Query.

Personally, I'd see the second option as cleaner. Even better, I'd create a new wrapper method, such as QueryWithParams() and "tack" it to Gdn_SQL class.

I'm going to do some tests in my sandbox, any feedback is welcome. Thanks.

UnderDog · March 2012

businessdad said: Precisely. Database objects don't allow to build a Union (at least, not easily, as I haven't found an example anywhere).

Thanks for the info, I won't interfere too much with the real answering. I figured you meant with "DataBase Objects" the actual PDO library.

If you have suggestions, suggested patches for the Vanilla library, please let us know.

businessdad · March 2012

Will do. I actually just found one more pitfall in the way the parameters are handled by class Gdn_Database. I took note of it, I'll post my finding on this thread.

By the way, my test of manually adding parameters using Model->SQL->NamedParameter() and then calling Model->SQL->Query() with the plain-text SQL worked quite well. I had some difficulties, but I'll give more details next time (3.01 AM here). :)

UnderDog · March 2012

businessdad said: 3.01 AM here

3 AM is the best time to gather your thoughts about the vanilla project (5 AM here)

Howdy, Stranger!

Categories

In this Discussion

Passing parameters to a Query built via plain-text SQL

Answers