How to do `itertools.product(queryset_a, queryset_b)` via Django ORM?

Question

I have two queryset, and need the product (aka "CROSS JOIN").

I could use this:

itertools.product(queryset_a, queryset_b)

BUT the result is too big, and I need to paginate it.

This means, I need to avoid itertools.product() and use the Django ORM to create the product in the database.

How to do itertools.product(queryset_a, queryset_b) via Django ORM?

I use PostgreSQL.

Example of what I mean with "product":

itertools.product(['a', 'b', 'c'], [1, 2, 3])

[('a', 1), ('a', 2), ('a', 3), 
 ('b', 1), ('b', 2), ('b', 3),
 ('c', 1), ('c', 2), ('c', 3)]

The resulting Queryset needs to support .count() since I want to paginate it.

class Term(models.Model):
    name = models.CharField(max_length=1024)

class Location(models.Model):
    name = models.CharField(max_length=1024)

Answer 1

This does not work, since RawQuerySet does not support count()

class LocationTermCombination(models.Model):
    term = models.CharField(max_length=1024)
    location= models.CharField(max_length=1024)

    class Meta:
        app_label = 'foo'
        managed = False

LocationTermCombination.objects.raw('''SELECT 1 as id, a.term, b.location FROM
  (select name as term from foo_term) a,
  (select name as location from foo_location) b
''').count()

AttributeError: 'RawQuerySet' object has no attribute 'count'

This does not work, but maybe it helps someone who does not need the count().

Answer 2

Unfortunately Django's ORM does not have much built in support for arbitrary joins. Looking at your models there is no relation between them, if there was any relation one could try some things like select_related and prefetch_related but then that is also limited that you likely won't be able to paginate like what you want.

Moving on to your question even if we can't arbitrarily make joins that does not mean we can't (somewhat hackily) use Views in the database that will perform these joins.

First we will create a view in the database like:

create view term_location_combination as
select 1 as id, a.name as term, b.name as location
from foo_term as a, foo_location as b

Now we will create a model to use this view:

class LocationTermCombination(models.Model):
    term = models.CharField(max_length=1024)
    location= models.CharField(max_length=1024)
    
    class Meta:
        managed = False
        db_table = "term_location_combination"

Now you will simply query this model:

combinations = LocationTermCombination.objects.all()

Note: As this is a view trying to update something or write would give you an error.