#Recursive CTE (Postgres)

6 messages · Page 1 of 1 (latest)

bronze axle
#

Hello all, I'm having problem with recursive query.

I have 3 models as seen below, Person relates to self using a through table and Company relates to Person with 1:1

My goal is to query Company and get its all people recursively without running n+1

class Person(models.Model):
    name = models.CharField(max_length=50)
    sub_people = models.ManyToManyField(
        "self",
        symmetrical=False,
        through="PersonRelation",
        through_fields=["child", "parent"]
    )


class PersonRelation(models.Model):
    child = models.ForeignKey(Person)
    parent = models.ForeignKey(Person)



class Company(models.Model):
    person = models.OneToOneField(Person)
#

So I favor using django-cte, recursive query seems to be (somehow) working but it runs n+1 for PersonRelation model.
Couldn't figure out why

here is how it looks like

def make_sub_people_cte(cte):
    # Not worrying about filter/values right now
    return PersonRelation.objects.union(
        cte.join(
            PersonRelation,
            parent=cte.col.child
        ),
        all=True
    )

cte = With.recursive(make_sub_people_cte)

all_people = (
    cte.join(
        Person, # join to Person model
        pk=cte.col.child_id
    ).with_cte(
        cte
    )
)

# Now assuming I have relation such as
{
    {
        "company": "1" # pk
        "name": "bob",
        "sub_people": [
            "name": "jane",
            "sub_people": [
                "name": "mike",
                "sub_people": []
            ]
        ]
    }
}

# I would expect this to one single query, not considering the Company 
# relation

for person in all_people:
    print(person)
    # we have people, good but now it runs n+1 query for table
    # PersonRelation, why?

# If I can fix the n+1 query

companies = Company.objects.prefetch_related(
    Prefetch(
        "person__sub_people",
        queryset=all_people, # using the recursive query
        to_attr="all_people"
    )
)

# I would like to join recursive query to Company queryset and access it
# company.person.all_people

I tried running raw SQL for recursive query but then how would I join it to companies queryset?

I would appreciate any help

#

the through table is making things much more complicated for this but that's how it is

bronze axle
#

anyone?

junior forge
#

@bronze axle You can combine select_related and prefetch_related

So your query would be like

company = Company.objects.select_related('persion').filter().prefetch_related('person__sub_people')