Run SQL JOIN on Django models that are not related?

Question

I have 2 Models, User (django.contrib.auth.models.User) and a model called Log. Both contain an email field. The journal does not specify a ForeignKey value that indicates the user model. I am trying to figure out how I can execute JOIN in these two tables using the email field as a community.

Basically there are 2 queries that I want to fulfill. The main connection 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 would also like to make some assemblies.

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

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

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

Is there any way to do this with ORM? Maybe something that I can add to the model of the Meta-class to "activate" the relationship?

Thank!

Answer 1

User, MonkeyPatching/DuckPunching:

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

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

(, ):

user = request.user
logs = user.logs

Ruby, , , Python .

( DuckPunching. Duck Typing, , : , , . , , , ).

Answer 2

extra()?

():

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

User.objects.filter(log__level=3) ():

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 3

Log.email ? , ForeignKey () ?

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

With FK, it becomes quite easy for the user 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 value of Log.email should not belong to the user, you can try adding a method to the 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)