How to SELECT from multiple tables in Django ORM?

user_9390914 0
Viewed 4269

I have two tables, oneup_backend_design and Django's default auth_user. First table have following columns (simplified): human_readable_id, manager, worker. Worker column contains a username from auth_user.

So I want to get human_readable_id plus first_name and last_name of worker associated with username in one query. In raw SQL it would look like this:

SELECT d.human_readable_id, u.first_name, u.last_name
FROM oneup_backend_design as d, auth_user as u
WHERE d.worker = u.username;

I've tried hard reading the docs and Django ORM cookbook, but did not found anything. It's such a simple query, I should be missing something. How one can make it with single database hit?

UPD: models.py:

class Design(models.Model):
    human_readable_id = models.CharField(max_length=10, default=id_generator, db_index=True)
    manager = models.CharField(max_length=40)
    worker = models.CharField(max_length=40, null=True)
select orm django sql
2 Answers

I strongly advise to turn worker into a ForeignKey, for example referring to the username field of the User model:

from django.conf import settings

class Design(models.Model):
    human_readable_id = models.CharField(max_length=10, default=id_generator, db_index=True)
    manager = models.CharField(max_length=40)
    worker = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        to_field='username'
    )

That means that worker_id will be a VARCHAR field with a FOREIGN KEY constraint to the username column of the auth_user table, and it will have an index, which enables fast joining. Furthermore it thus guarantees referential integrity: if the worker is not NULL it refers to a valid username of an auth_user object.

Then we can query with:

Design.objects.select_related('worker')

You can make use of .only() [Django-doc] to narrow the columns fetched, but unless these contain large amounts of data, it is better to include these.

You can also fetch values from a related model with .annotate(..):

from django.db.models import F

Design.objects.annotate(
    first_name=F('worker__first_name'),
    last_name=F('worker__last_name')
)

Here the Design objects from this queryset will have two extra attributes: .first_name and .last_name.

user_675790

assuming your model is BackendDesign and you have ForeignKey worker to User then:

BackendDesign.objects.select_related(
    'worker',
).only(
    'human_readable_id',
    'worker__first_name',
    'worker__last_name',
)

If this won't help please read about extra() in django docs Django docs extra I'm new here but hope that will help

user_68135170
Related