Django: Querying Multiple Tables Using Models

Question

I am currently wanting to run a SQL statement that will filter my database but it accesses multiple tables. From the other examples I have read on this website, they all use foreign keys not on the primary key; however, that is my current setup. I am having two issues, the first being the SQL filter. These are my models:

class UserProfile(models.Model):
        user = models.ForeignKey(User, unique=True);

        isATutor = models.BooleanField();
        timeAvailable = models.CharField(max_length=3);

class TutorProfile(models.Model):
        user = models.ForeignKey(User);

        language = models.CharField(max_length=30);
        unique_together = (("user", "language"), );

class Tutor(models.Model):
        user = models.ForeignKey(User);

        subject = models.CharField(max_length=30);
        unique_together = (("user", "subject"), );

I am using raw SQL at the moment.

def sqlQuery(avail, lang, sub):
    from django.db import connection, transaction
    cursor = connection.cursor();

    cursor.execute("SELECT a.first_name, a.last_name, a.email FROM auth_user a, books_tutor b, books_tutorprofile c, books_userprofile d WHERE a.id = b.user_id AND a.id = c.user_id AND a.id = d.user_id AND b.subject=%s AND c.language=%s AND d.timeAvailable=%s", [sub, lang, avail]);
    row = cursor.fetchall();

    return row;

timeAvailable is taking a three character string is in the form 'MAE' where M = morning, A=Afternoon, E=Evening and if they are not needed, we'll replace with - e.g. 'M--' is only available in morning.

So the first issue is I'd love to be able to keep the above SQL as a django model. The second issue is I need a good way to query for MAE. I'm thinking I may have to do 8 different SQL parts and UNION them depending on what is chosen.

Thanks,

Steve

Thomas · Accepted Answer · 2010-11-18 07:14:25Z

6

why not use subclassing?

class UserProfile(User):
    time_available = models.CharField(max_length=3);

class Tutor(UserProfile):
    subject = models.CharField(max_length=30);

class TutorProfile(Tutor):
    language = models.CharField(max_length=30);
    class Meta:
        unique_together = ['username','subject','language'] # <--- username field from User base class.

UserProfile has access to all User fields, plus time_available.
Tutor has access to all User, UserProfile fields, plus subject.
TutorProfile has access to all User, UserProfile and Tutor fields, plus language.

in this way, you can achieve the join simply by looking up an instance of TutorProfile.

I should mention that when using subclassing, a OneToOneField is automatically created from the subclass to the superclass.

edited Nov 18, 2010 at 7:14

answered Nov 18, 2010 at 3:57

Thomas

11.9k4 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user511622 Over a year ago

That is pretty cool. Question for you: Is there any way to still enforce the uniqueness of user & subject/language? I've never used subclassing and will read more about it. I greatly appreciate that answer.

Thomas Over a year ago

For more info, see: docs.djangoproject.com/en/dev/topics/db/models/…. also, don't forget to mark accepted answers ;)

user511622 Over a year ago

Thanks so much. I've been using djangobook.com for my tutorials.

Collectives™ on Stack Overflow

Django: Querying Multiple Tables Using Models

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related