Tag: postgresql

Find relation between two models

Today one of my teammates and I were working on a complex SQL query which was beyond Django’s ORM normal API, so we had to use the extra method to build the query. After running with a couple of issues with PostgreSQL’s GROUP BY we decided to opt quickly for subquieries, and in no time we had our query done. The problem was though, that we had to do this operation on several models that where similar to each other, the only thing that changed was the WHERE clause depending on the models involved.

So I set off so find a way to automatically discover which was the relation between two models. In short time I discovered this: MyModel._meta.get_all_related_objects(). This returns a list of RelatedObject instances that tell us about which models point to MyModel  though a foreign key, cool! So, let us suppose we have a Car model and a Rental model. The Rental model has a ForeingKey to Car, to know how they are related I built something like this:

for r in target_model._meta.get_all_related_objects():
if r.model == ranking_model:
relation = r
for r in Car._meta.get_all_related_objects():
    if r.model == Rental:
        relation = r
        break

now in relation I have a RelatedObject (I cannot seem to find a lot of information about this on the net) instace, which has very useful fields like:

  • parent_model: the model that is being refered in this relation (in this case Car)
  • model: the Model (in this case Rental) that points to the parent model (in this case Car)
  • field: the field in model that references to parent_model

So now I know that if I want to build a custom SQL in Django and I really want to make it generic as possible, I can use this RelatedObject class along with other interesting model attributes like: Model._meta.db_table (getting the name that the model has on your database) and such to make my code 100% portable 😀 (or at least try to).

Just for the fun of it, this is the resulting code (not that it will make much sense to you… and sorry for the lousy formatting):

return target_model.objects.extra(select={"sum":"SELECT SUM(points) "
                                                "FROM %s "
                                                "WHERE date_part('year', reg_date) = %d "
                                                "AND %s.%s = %s.%s" % (ranking_model._meta.db_table, year, # year
                                                                       ranking_model._meta.db_table, # ranking table name
                                                                       relation.field.column, # column on ranking that points to user/team table
                                                                       target_model._meta.db_table, # user/team table
                                                                       target_model._meta.pk.column)}, # id column on user/team table
                                  order_by=['sum'])[:10]

Use with wisdom!

Now, if there is an easier way to do this, please tell me, I’m eager to learn!

Advertisements

The tools of choice

Well, we have decided which tools we are going to use for our semester project. The winners are:

Eclipse with the following plugins:

And for our development framework (after a period of uncertainty):

Django was the clear winner.

PostgreSQL will be our DBMS.

Eventually we’ll configure the Apache HTTP Server to work with mod_wsgi for Apache-Python interpretation.

Shortly I’ll post on how to get all those working nice together.

Until then.