Dynamic SQL join query in django

Question

Here are three very simplified class I'm working with:

class User(AbstractBaseUser):
    email = models.EmailField()
    name = models.CharField()
    is_admin = models.BooleanField()
    phone_number = models.CharField()    

class Accounts(models.Model):
    name = models.CharField()
    users = models.ManyToManyField(settings.USR_MODEL, through='Membership',
        null=True, blank=True)
    customer_id = models.IntegerField()    

class Membership(models.Model):
    user = models.ForeignKey(User)
    company = models.ForeignKey(Accounts)
    is_admin = models.BooleanField(default=False)
    is_billing = models.BooleanField(default=False)
    is_tech = models.BooleanField(default=False)

I'd like to be able to get the Users associated with an Account and filter them by the boolean attributes is_admin, is_billing, is_tech. Right now I'm doing:

microsoft = Accounts.objects.get(customer_id=1)

I can then get tech contact by doing

ms_tech = microsoft.filter(membership__is_tech=True)

This works, however, I'd like to be able to dynamically create the queries for membership__is_tech / __is_billing / __is_admin / __is_foo / __is_bar / __is_quux / etc What is the most pythonic/djangonic way of doing this?

what would be the filter criteria ? Do you want an AND or OR depending on some criteria ? — karthikr
– karthikr, Commented Nov 10, 2013 at 18:12
I just need to filter by a single attribute types from an Account. In the example above I would just return ms_tech. In a seperate query I might want to filter by is_foo, or whatever. — LiavK
– LiavK, Commented Nov 10, 2013 at 18:45

Daniel Roseman · Accepted Answer · 2013-11-10 19:20:56Z

1

I'm not really sure what you mean by dynamically creating the queries, given that you only have a set number of fields. But you can perhaps use the fact that the query is a keyword argument to a function, and as such can be replaced with a dictionary and the ** syntax:

kwargs = {'membership__is_tech': True}
ms_tech = microsoft.filter(**kwargs)

(Note that there should be no objects in the filter you give, as microsoft is already a queryset, not a model.)

answered Nov 10, 2013 at 19:20

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

1 Comment

LiavK Over a year ago

Right, I think this should work, thanks! BTW, I corrected the objects typo -- I was writing that part of the question from memory.

Serafeim · Accepted Answer · 2013-11-10 18:26:38Z

1

The most pythonic/djangonic (and also the most normalized) way to do that would be to not include is_billing/is_admin/is_tech to your Membership model as fields but to add a "department" field that could get "admin/billing/tech" values (or is a ForeignKey to your Department model).

If you want to have two departments (for instance admin and blling) then add a ManyToMany field to the Department model.

answered Nov 10, 2013 at 18:26

Serafeim

15.1k15 gold badges96 silver badges136 bronze badges

1 Comment

LiavK Over a year ago

Interesting. I can explore this possibility, but this is part of a larger code base, and I doubt I can change that implementation detail.

Collectives™ on Stack Overflow

Dynamic SQL join query in django

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related