Advanced Django query with subselects and custom JOINS
- by Bryan Ward
I have been investigating this number theoretic function (found in the Height model) and I need to query for things based on the prime factorization of the primary key, or id. I have created a model for Factors of the id which maintains all of the prime factors.
class Height(models.Model):
b = models.IntegerField(null=True, blank=True)
c = models.IntegerField(null=True, blank=True)
d = models.FloatField(null=True, blank=True)
class Factors(models.Model):
height = models.ForeignKey(Height, null=True, blank=True)
factor = models.IntegerField(null=True, blank=True)
degree = models.IntegerField(null=True, blank=True)
prime_id = models.IntegerField(null=True, blank=True)
For example, if id=24, then the associated entries in the factors table would be
height_id=24,factor=2,degree=3,prime_id=0
height_id=24,factor=3,degree=1,prime_id=1
the prime_id keep track of the relative order of the primes.
Now let p < q < r < s all be prime numbers and a,b,c,d be positive integers. Then I want to be able to query for all Heights of the form id=(p**a)*(q**b)*(r**c)*(s**d). Now this is simple in the case that all of p,q,r,s,a,b,c,d are known in that I can just run
Height.objects.get(id=(p**a)*(q**b)*(r**c)*(s**d))
But I need to be able to query for something like (2**a)*(3**2)*(r**c)*(s**d) where r,s,a,d are unknown and all Heights of such form will be returned.
Furthermore, not all of the rows in Height will have exactly four prime factors, so I need to make sure that I am not matching rows of the form id=(p**a)*(q**b)*(r**c)*(s**d)*(t**e)...
From what I can tell, the following MySQL query accomplishes this, but I would like to do it through the Django ORM. I also don't know if this MySQL query is the proper way to go about doing things.
SELECT h.*,count(f.height_id) AS factorsCount
FROM height AS h LEFT JOIN factors AS f ON
(
f.height_id = h.id AND
f.height_id IN (SELECT height_id FROM factors where prime_id=1 AND factor=2 AND degree=1) AND
f.height_id IN (SELECT height_id FROM factors where prime_id=2 AND factor=3 AND degree=2) AND
f.height_id IN (SELECT height_id FROM factors where prime_id=3 AND factor=5 AND degree=1) AND
f.height_id IN (SELECT height_id FROM factors where prime_id=4 AND factor=7 ANd degree=1)
)
GROUP BY h.id
HAVING factorsCount=4
ORDER BY h.id;
Any ideas or suggestions for things to try?
