title: Calculating Combinations using Django ORM (CROSS JOIN)
body: <p>I've got three related models: <code>Process</code>, <code>Factor</code> and <code>Level</code>. A <code>Process</code> has a many-to-many relationship with <code>Factor</code>s and a <code>Factor</code> will have one or more <code>Level</code>s. I'm attempting to calculate all combinations of <code>Level</code>s involved with a <code>Process</code>. This is straightforward to implement with Python's <code>itertools</code> as a model method, but is somewhat slow in execution speed, so I'm trying to figure out how to use the Django ORM to perform this calculation in SQL.</p>
  <p>Models:</p>
  <pre class="lang-python prettyprint-override"><code>class Process(models.Model):
      factors = models.ManyToManyField(Factor, blank = True)
  
  class Factor(models.Model):
      ...
  
  class Level(models.Model):
      factor = models.ForeignKey(Factor, on_delete=models.CASCADE)
  </code></pre>
  <p>Example: A process <code>'Running'</code> involves three <code>Factor</code>s (<code>'Distance'</code>, <code>'Climb'</code>, <code>'Surface'</code>) each composed of a number of <code>Level</code>s (<code>'Long'</code>/<code>'Short'</code>, <code>'Flat'</code>/<code>'Hilly'</code>, <code>'Road'</code>/<code>'Mixed'</code>/<code>'Trail'</code>). Calculating the combinations in SQL would involve building a query by first determing how many <code>Factor</code>s were involved (3 in this example) and performing a <code>CROSS JOIN</code> of all the levels that many times.</p>
  <p>In SQL, this could be accomplished as such:</p>
  <pre class="lang-sql prettyprint-override"><code>WITH foo AS
      (SELECT * FROM Level
       WHERE Level.factor_id IN
          (SELECT ProcessFactors.factor_id FROM ProcessFactors WHERE process_id = 1)
      )
  SELECT a1.*, a2.*, a3.*
      FROM foo a1
      CROSS JOIN foo a2
      CROSS JOIN foo a3
  WHERE (a1.factor_id &lt; a2.factor_id) AND (a2.factor_id &lt; a3.factor_id)
  </code></pre>
  <p>Result:</p>
  <pre><code>a1.name | a2.name | a3.name
  --------------------------
  Long    | Flat    | Road
  Long    | Flat    | Mixed
  Long    | Flat    | Trail
  Long    | Hilly   | Road
  Long    | Hilly   | Mixed
  Long    | Hilly   | Trail
  Short   | Flat    | Road
  Short   | Flat    | Mixed
  Short   | Flat    | Trail
  Short   | Hilly   | Road
  Short   | Hilly   | Mixed
  Short   | Hilly   | Trail
  </code></pre>
  <p>Currently, I have this implemented as a method on the <code>Process</code> model as:</p>
  <pre class="lang-python prettyprint-override"><code>def level_combinations(self):
      levels = []
      for factor in self.factors.all():
          levels.append(Level.objects.filter(factor = factor))
      
      combinations = []
      for levels in itertools.product(*levels):
          combination = {}
          
          combination[&quot;levels&quot;] = levels
          
          combinations.append(combination)
      
      return combinations
  </code></pre>
  <p>Is this possible using the Django ORM or is it complex enough that it should be implemented as a raw query to improve speed over the Python code implementation?</p>
  <p>There was a similiar question about <a href="https://stackoverflow.com/q/7894214/2167797">performing <code>CROSS JOIN</code> in Django ORM</a> from several years ago (approx. Django v1.3 it looks like) didn't attract appear to attract much attention (the author punted to just using Python itertools).</p>