Django filter against ForeignKey and by result of manytomany sub query

Question

I've looked at doing a query using an extra and/or annotate but have not been able to get the result I want.

I want to get a list of Products, which has active licenses and also the total number of available licenses. An active license is defined as being not obsolete, in date, and the number of licenses less the number of assigned licenses (as defined by a count on the manytomany field).

The models I have defined are:

class Vendor(models.Model):
    name = models.CharField(max_length=200)
    url = models.URLField(blank=True)


class Product(models.Model):

    name = models.CharField(max_length=200)
    vendor = models.ForeignKey(Vendor)
    product_url = models.URLField(blank=True)
    is_obsolete = models.BooleanField(default=False, help_text="Is this product obsolete?")


class License(models.Model):

    product = models.ForeignKey(Product)
    num_licenses = models.IntegerField(default=1, help_text="The number of assignable licenses.")
    licensee_name = models.CharField(max_length=200, blank=True)
    license_key = models.TextField(blank=True)
    license_startdate = models.DateField(default=date.today())
    license_enddate = models.DateField(null=True, blank=True)
    is_obsolete = models.BooleanField(default=False, help_text="Is this licenses obsolete?")
    licensees = models.ManyToManyField(User, blank=True)

I have tried filtering by the License model. Which works, but I don't know how to then collate / GROUP BY / aggregate the returned data into a single queryset that is returned.

When trying to filter by procuct, I can quite figure out the query I need to do. I can get bits and pieces, and have tried using a .extra() select= query to return the number of available licenses (which is all I really need at this point) of which there will be multiple licenses associated with a product.

So, the ultimate answer I am after is, how can I retrieve a list of available products with the number of available licenses in Django. I'd rather not resort to using raw as much as possible.

An example queryset that gets all the License details I want, I just can't get the product:

License.objects.annotate(
    used_licenses=Count('licensees')
).extra(
    select={
        'avail_licenses': 'licenses_license.num_licenses - (SELECT count(*) FROM licenses_license_licensees WHERE licenses_license_licensees.license_id = licenses_license.id)'
    }
).filter(
    is_obsolete=False,
    num_licenses__gt=F('used_licenses')
).exclude(
    license_enddate__lte=date.today()
)

Thank you in advance.

EDIT (2014-02-11): I think I've solved it in possibly an ugly way. I didn't want to make too many DB calls if I can, so I get all the information using a License query, then filter it in Python and return it all from inside a manager class. Maybe an overuse of Dict and list. Anyway, it works, and I can expand it with additional info later on without a huge amount of risk or custom SQL. And it also uses some of the models parameters that I have defined in the model class.

class LicenseManager(models.Manager):

def get_available_products(self):
    licenses = self.get_queryset().annotate(
        used_licenses=Count('licensees')
    ).extra(
        select={
            'avail_licenses': 'licenses_license.num_licenses - (SELECT count(*) FROM licenses_license_licensees WHERE licenses_license_licensees.license_id = licenses_license.id)'
        }
    ).filter(
        is_obsolete=False,
        num_licenses__gt=F('used_licenses')
    ).exclude(
        license_enddate__lte=date.today()
    ).prefetch_related('product')

    products = {}
    for lic in licenses:
        if lic.product not in products:
            products[lic.product] = lic.product
            products[lic.product].avail_licenses = lic.avail_licenses
        else:
            products[lic.product].avail_licenses += lic.avail_licenses

    avail_products = []
    for prod in products.values():
        if prod.avail_licenses > 0:
            avail_products.append(prod)

    return avail_products

EDIT (2014-02-12): Okay, this is the final solution I have decided to go with. Uses Python to filter the results. Reduces cache calls, and has a constant number of SQL queries.

The lesson here is that for something with many levels of filtering, it's best to get as much as needed, and filter in Python when returned.

class ProductManager(models.Manager):

    def get_all_available(self, curruser):
        """
        Gets all available Products that are available to the current user
        """
        q = self.get_queryset().select_related().prefetch_related('license', 'license__licensees').filter(
            is_obsolete=False,
            license__is_obsolete=False
        ).exclude(
            license__enddate__lte=date.today()

        ).distinct()

        # return a curated list. Need further information first
        products = []
        for x in q:
            x.avail_licenses = 0
            x.user_assigned = False

            # checks licenses. Does this on the model level as it's cached so as to save SQL queries
            for y in x.license.all():
                if not y.is_active:
                    break

                x.avail_licenses += y.available_licenses

                if curruser in y.licensees.all():
                    x.user_assigned = True
            products.append(x)

        return q

Django filter against ForeignKey and by result of manytomany sub query

Answers (1)

Related Questions