CODEWITHSUNDEEP
djangodjango-queryset
marianobianchi
marianobianchi

Reputation: 8488

problems with django raw query parameters

I was trying to make a custom raw query for a model and i get this error:

DatabaseError: error de sintaxis en o cerca de «E'positions_statusrecord'» LINE 1: SELECT id FROM E'positions_statusrecord' WHERE "type"=E'Leav...

Here is the raw query i'm trying to use (it's a manager method):

def get_status_ids(self, model):
    """
    This query returns the latest statuses ids for each entry
    of "model" in StatusRecord.
    """
    db_table = self.model._meta.db_table
    model_type = model.get_status_type()

    raw_query = (
        "SELECT id "
        "FROM %s "
        "WHERE \"type\"=%s AND "
        "(identifier,date_time) IN "
               "( "
                          "SELECT identifier, Max(date_time) "
                          "FROM %s "
                          "WHERE \"type\"=%s "
                          "GROUP BY identifier"
               ")"
    )

    params = (db_table, model_type, db_table, model_type, )
    return self.raw(raw_query, params)

I've tried with a simpler query (just a SELECT ... FROM ..) and had the same problem. It seems to be that raw queries couldn't have the FROM part completed with a parameter.

Am i right? or have i made a mistake and i'm not finding it?

I'm using postgreSQL 8.4.10, django 1.3 and python 2.6.

I've searched information about raw queries parameters to see if there are some forbidden formatting options, but i didn't find anything that helps me.

Does anyone knows what's causing this error?

Upvotes: 3

Views: 2976

Answers (1)

Paulo Scardine
Paulo Scardine

Reputation: 77251

It seems to be that raw queries couldn't have the "FROM" part completed with a parameter.

Yes, the ORM will quote the string argument making it ... FROM 'some_table' ....

Since the db_table parameter is trusted (not originated from user input), you can do something like:

raw_query = (
    "SELECT id "
    "FROM %s "
    "WHERE \"type\"=%%s AND "
    "(identifier,date_time) IN "
           "( "
                      "SELECT identifier, Max(date_time) "
                      "FROM %s "
                      "WHERE \"type\"=%%s "
                      "GROUP BY identifier"
           ")"
) % (db_table, db_table)
return self.raw(raw_query, (model_type, model_type))

Since Django Aggregation can be used in filters, probably you can get the same results without resorting to raw SQL (look also the in lookup example).

Look if you can replace raw() with QuerySet.extra() and SQL IN with EXISTS.

Upvotes: 2

Related Questions