CODEWITHSUNDEEP
pythonmysqlsqldjango
LiavK
LiavK

Reputation: 752

Dynamic SQL join query in django

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?

Upvotes: 1

Views: 824

Answers (2)

Daniel Roseman
Daniel Roseman

Reputation: 599490

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.)

Upvotes: 1

Serafeim
Serafeim

Reputation: 15084

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.

Upvotes: 1

Related Questions