Perform a SQL JOIN on Django models that are not related?

Question

I have 2 Models, User (django.contrib.auth.models.User) and a model named Log. Both contain an "email" field. Log does not have a ForeignKey pointing to the User model. I'm trying to figure out how I can perform a JOIN on these two tables using the email field as the commonality.

There are basically 2 queries I want to be able to perform. A basic join for filtering

#Get all the User objects that have related Log objects with the level parameter set to 3.
User.objects.filter(log__level=3)

I'd also like to do some aggregates.

User.objects.all().anotate(Count('log'))

Of course, it would be nice to be able to do the reverse as well.

log = Log.objects.get(pk=3)
log.user...

Is there a way to do this with the ORM? Maybe something I can add to the model's Meta class to "activate" the relation?

Thanks!

Answer 1

why not use extra()?

example (untested):

User.objects.extra(
    select={
        'log_count': 'SELECT COUNT(*) FROM myapp_log WHERE myapp_log.email = auth_user.email'
    },
)

for the User.objects.filter(log__level=3) portion here is the equivalent with extra (untested):

User.objects.extra(
    select={
        'log_level_3_count': 'SELECT COUNT(*) FROM myapp_log WHERE (myapp_log.email = auth_user.email) AND (myapp_log.level=3)'
    },
).filter(log_level_3_count__gt=0)

Answer 2

Do the Log.email values always correspond to a User? If so, how about just adding a ForeignKey(User) to the Log object?

class Log(models.Model):
    # ...
    user = models.ForeignKey(User)

With the FK to User, it becomes fairly straight forward to find what you want:

User.objects.filter(log__level=3)
User.objects.all().anotate(Count('log'))

user.log_set.all()
user.log_set.count()

log.user

If the Log.email value does not have to belong to a user you can try adding a method to a model manager.

class LogManager(models.Manager):
    def for_user(self, user):
        return super(LobManager, self).get_query_set().filter(email=user.email)

class Log(models.Model):
    # ...
    objects = LogManager()

And then use it like this:

user = User.objects.get(pk=1)
logs_for_user = Log.objects.for_user(user)

Answer 3

You can add an extra method onto the User class, using MonkeyPatching/DuckPunching:

def logs(user):
    return Log.objects.filter(email=user.email)

from django.contrib.auth.models import User
User.logs = property(logs)

Now, you can query a User, and ask for the logs attached (for instance, in a view):

user = request.user
logs = user.logs

This type of process is common in the Ruby world, but seems to be frowned upon in Python.

(I came across the DuckPunching term the other day. It is based on Duck Typing, where we don't care what class something is: if it quacks like a duck, it is a duck as far as we are concerned. If it doesn't quack when you punch it, keep punching until it quacks).