CODEWITHSUNDEEP
djangodjango-views
Cubed Eye
Cubed Eye

Reputation: 5631

Django: Filtering objects with results of another table?

I'm having a mental blank on how to do this in Django, hoping you can help.

I have a table of galleries that I am filtering by type:

public_galleries = models.Gallery.objects.filter(type = 2).filter(root_gallery__isnull = True)

but I also want to see if the gallery doesn't exist in the UserGallery table for a specific user. I have this list of galleries for the user:

user_galleries = models.UserGallery.objects.select_related().filter(clientuser=request.user.id).filter(gallery__root_gallery__isnull = True)

Note** Just started using Django for a real project, so any improvement on either of those statements are also appreciated.

Edit - The Models:

class Gallery(models.Model):
    """Gallery model"""
    name = models.CharField(u"Gallery name", max_length=120)
    type = models.IntegerField(default=0, choices=TYPE_CHOICES)
    root_gallery = models.ForeignKey("self", blank=True, null=True)
    """ Other Fields"""

class UserGallery(models.Model):
    """Model to link Gallery and ClientUser"""
    gallery = models.ForeignKey(Gallery)
    clientuser = models.ForeignKey(ClientUser)
    owner = models.BooleanField(default=False)

Upvotes: 0

Views: 11231

Answers (3)

bcattle
bcattle

Reputation: 12819

This answer will not work for even moderately large numbers of user_galleries, since you're loading all of them into a list every time.

A better way is to use the QuerySet's extra() method, which allows you to specify additional conditions in SQL for a WHERE clause.

From the Django docs:

You can define explicit SQL WHERE clauses — perhaps to perform non-explicit joins — by using where. You can manually add tables to the SQL FROM clause by using tables.

In your case something like

private_galleries = Gallery.objects.filter(type=1, root_gallery__isnull=True) \
    .extra(where=['''
        yourapp_gallery.id NOT IN (SELECT id FROM 
            ...long query used to generate user_galleries...  )
    '''])

would work. Unfortunately it means transcribing the query that produced user_galleries into SQL, so you need to decide whether the DRY/maintainability tradeoff is worth saving the overhead of loading that list into memory on every query. I suspect that with anything but a trivial number of user_galleries it is.

Note that there where= arg takes a list of strings.

More info in from the mailing list here.

Upvotes: 4

Cubed Eye
Cubed Eye

Reputation: 5631

I would like to thank Mayuresh, for his help

Not sure why I got that error, but I found the solution with this:

private_galleries = models.Gallery.objects.filter(type = 1).filter(root_gallery__isnull = True).exclude(id__in = [x.gallery.id for x in user_galleries])

Upvotes: 1

Mayuresh
Mayuresh

Reputation: 1072

Gallery.objects.filter(type = 2).filter(root_gallery__isnull = True).exclude(id__in = [x.id for x in request.user.usergallery_set()])

Should do it.

Upvotes: 7

Related Questions