Tag: db

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

Fixtures, default data for your models

So, we started working on our project (finally) and although some of the requirements of the project will change in the course of the following days, we are working on the more general aspects of the site. Among these general “apps” is the help app. Basically it has a couple of static pages and then a hierarchy of categories (folders) and topics presented in a similar way to Google’s Gmail Help Page.

This help app is a variation of the usual Polls or Books examples you get in Django’s Writing you first Django App Tutorial, and is a very good exercise to get up to speed with the development in Django. As the app started to take shape we began to wonder if Django provided a mechanism to load default data into the models…

Fortunately it does! You can read all about loading initial data into models it in the docs, but if you want to continue reading about our experiences and choices, read on!

In a nutshell: You can choose between two methods, fixtures or SQL. Fixtures are database agnostic representations of database rows in either XML, JSON, YAML, or Python objects notation. While SQL, you know, consists of SQL insert statements to populate your data back. Both of these can be stored in files and can later be loaded with the magical manage.py script. Obviously we chose fixtures because of their loose coupling with database layer and using XML for maximum compatibility!

So, to generate a fixture containing the current data in the database of the models of an app you only need to run the following manage.py command in the root folder of your Django project:

python manage.py dumpdata <app_name> --format <format>

Where app name is the name of the Django app whose models and format can either be xml, json or yaml. Is you run that command you’ll get the resulting fixture onscreen, that is of no use for us, we need to save it on a file! No worries, you just need to use the output redirection symbols of your console to push that data to a file. So lets say you want to create a fixture for your catalog app…

Do this under GNU/Linux (I assume it is the same under MacOS):

python manage.py dumpdata catalog --format xml > catalog/fixtures/catalog_data.xml

Do this under Windows:

python manage.py dumpdata catalog --format xml > catalog\fixtures\catalog_data.xml

You may have noticed I redirected the output to a fixtures directory inside the app, this is because the Django documentation recommends that for data dumping (using this path is crucial for the last step in this post!)

So, how do you load the data? Simple, use mange.py loaddata!

manage.py loaddata catalog/fixtures/catalog_data.xml

And you are done.

But the whole idea of using fixtures was to provide a simple way to transport data across the team of developers (we are going to be working with SVN, remember?). So reading the doc a little bit more we find that these fixtures can be automatically loaded when performing a syncdb! You only need to save the fixture file with a special name:  initial_data.xml (xml in our case, for you it can be xml, json or yaml) in your app’s fixtures directory.

Now every time you perform a syncdb, the models will be created on the database and the data from the fixture will be automagically loaded, isn’t that great? It is!

Hope this helps!

Until next time.

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.