Peewee returns multiple Objects during an Inner Join

Question

class Parent(BaseModel):
    name = peewee.CharField()

class Child(BaseModel):
    name = peewee.CharField()
    parent = peewee.ForeignKeyField(Parent, related_name='children')

parent = Parent.create(name="Parent1")
child1 = Child.create(name="Child1", parent=parent)
chilld2 = Child.create(name="Child2", parent=parent)

query = (Parent.select()
        .join(Child))

for p in query:
    print p.name
    for c in p.children:
        print "\t",c.name

This results in the following output:

Parent1
    Child1
    Child2
Parent1
    Child1
    Child2

I was expected this:

Parent1
    Child1
    Child2

It would seem that I am iterating over a SQL result set as opposed to a object in the ORM sense. Given that there is only one "Parent object", from an ORM perspective, in the query, how can I modify my query or my iteration over the query to get the expected results?

I want to return the query to my template like in the peewee example app and just iterate over it to display the object, but if I do that it will display the same object twice (or n times for n number of related children).

I know I can do the following:

p = query.get()
print p.name
for c in p.children:
    print "\t", c.name

But when I have multiple parents and just want to loop through them all, I can't identify how many parents there are. The count method of query returns the count of the result set, as opposed to the count of them number of parent objects there are.

This is also a work around:

pars = []
for p in query:
    if p not in pars:
        pars.append(p)

for p in pars:
    print p.name
    for c in p.children:
        print "\t", c.name

Answer 1

There are two problems with your code.

The first is that, even though you've joined on Child, calling parent.children will execute an extra, separate query.

Because of the first problem, you're mistaking what the outer loop is doing, which is just giving you the Parent portion of Parent1+Child1, Parent1+Child2.

To fix this you can do use prefetch or aggregate_rows:

# Execute a single query and de-dupe the duplicated parent rows.
query = Parent.select(Parent, Child).join(Child).aggregate_rows()
for parent in query:
    print parent.name
    for child in parent.children:
        print childname

# Execute a query for each joined table. Generally more efficient
# than aggregate_rows().
query = prefetch(Parent.select(), Child)
for parent in query:
    print parent.name
    for child in parent.children_prefetch:  # NOTE the `_prefetch`
        print child.name

This is documented extensively: http://docs.peewee-orm.com/en/latest/peewee/querying.html#avoiding-n-1-queries