Issue: Can't do eager loading
Author: maaaks
State: closed
Created: 2016-03-16 14:21:15

Hello. Please help me.

I'm beginning to use Peewee for a project, and I don't understand how to make it eagerly load everything I need in the following situation.

I have a model called HostedSite, which contains (one-to-many) zero or more HostedSitePage's, each of which is connected (many-to-many) with zero or more ObjectTypes, which can be thought of as classic tags. I thought the following should load everything I need in a single query, but it doesn't, and that's even without querying the tags:

``` python
site = HostedSite.select(HostedSite, HostedSitePage, HostedSitePageToType, ObjectType)\
    .join(HostedSitePage, join_type=JOIN_LEFT_OUTER)\
    .join(HostedSitePageToType, join_type=JOIN_LEFT_OUTER)\
    .join(ObjectType, join_type=JOIN_LEFT_OUTER)\
    .where(HostedSite.domain == domain)\
    .order_by(HostedSitePage.order_num)\
    .first() # load just one site, but everything for it

return site.pages[2].title
```

Here's what Peewee executes:

``` sql
SELECT "t1"."company_id", "t1"."domain", "t1"."title", "t1"."is_active",
"t4"."id", "t4"."path", "t4"."title", "t4"."text", "t4"."order_num", "t4"."site_id",
"t2"."page_id", "t2"."type_id",
"t3"."id", "t3"."tablename", "t3"."object_type_id"
FROM "_a"."hosted_site" AS t1
LEFT OUTER JOIN "_a"."hosted_site_page" AS t4 ON ("t1"."company_id" = "t4"."site_id")
LEFT OUTER JOIN "_a"."hosted_site_page_to_type" AS t2 ON ("t4"."id" = "t2"."page_id")
LEFT OUTER JOIN "_a"."object_type_ids" AS t3 ON ("t2"."type_id" = "t3"."id")
WHERE ("t1"."domain" = 'example.test')
ORDER BY "t4"."order_num"
```

``` sql
SELECT "t1"."id", "t1"."name", "t1"."urname", "t1"."region_id",
"t1"."is_locked", "t1"."email", "t1"."use_agent_billing"
FROM "ml"."ml_company_all" AS t1
WHERE ("t1"."id" = 1) LIMIT 1
```

``` sql
SELECT "t1"."id", "t1"."path", "t1"."title", "t1"."text", "t1"."order_num", "t1"."site_id"
FROM "_a"."hosted_site_page" AS t1
WHERE ("t1"."site_id" = 1)
```

Okay, the second query seems to indicate some unrelated problem with my models, but what about the third one? Why is it executed, why doesn't Peewee load data from the result of the first query?


You have a couple options, as described in the docs:

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

Use prefetch (my recommendation). This will run 4 queries (one for each table). Duplicates are not fetched, so there is no de-duping required.

``` python
hosted = HostedSite.select().where(HostedSite.domain == domain)
pages = HostedSitePage.select().order_by(HostedSitePage.order_num)
sites = prefetch(hosted, pages, HostedSitePageToType, ObjectType)
site = sites.first()

print site.domain
# The backref here is whatever the default backref is + '_prefetch':
for page in site.pages_prefetch:
    print '    - ' + page.title
    for page_to_type in page.hostedsitepagetotype_set_prefetch:
        print '        - ' + page_to_type.object_type.table_name
```

Use `aggregate_rows()`. This requires de-duping in Python. Check the [docs here](http://docs.peewee-orm.com/en/latest/peewee/querying.html#using-aggregate-rows) and scroll down to read the "Some things to consider when using aggregate_rows():" section.

``` python
# Note that we specify ordering for the first 3 tables. This is necessary due to the way
# "aggregate_rows" progressively de-dupes data.
sites = (HostedSite
        .select(HostedSite, HostedSitePage, HostedSitePageToType, ObjectType)
        .join(HostedSitePage, join_type=JOIN_LEFT_OUTER)
        .join(HostedSitePageToType, join_type=JOIN_LEFT_OUTER)
        .join(ObjectType, join_type=JOIN_LEFT_OUTER)
        .where(HostedSite.domain == domain)
        .order_by(HostedSite.domain, HostedSitePage.order_num, HostedSitePageToType.page))
site = sites.first()

print site.domain
# Use the normal backref names.
for page in site.pages:
    print '    - ' + page.title
    for page_to_type in page.hostedsitepagetotype_set:
        print '        - ' + page_to_type.object_type.table_name
```


Thank you, `aggregate_rows()` seems to work.

Btw, my `HostedSitePageToType` is a «through» table for the `ManyToManyField`. And it seems to be impossible to load this relationship eagerly.

``` python
HostedSitePage_Deferred = DeferredRelation()

class HostedSitePageToType(BaseModel):
    class Meta:
        db_table = "hosted_site_page_to_type"
        primary_key = False

    page = ForeignKeyField(HostedSitePage_Deferred)
    type = ForeignKeyField(ObjectType)

class HostedSitePage(BaseModel):
    class Meta:
        db_table = "hosted_site_page"

    id = PrimaryKeyField()
    path = CharField()
    title = CharField()
    text = TextField()
    order_num = IntegerField()

    site = ForeignKeyField(HostedSite, related_name="pages")
    """ :type : HostedSite """

    object_types = ManyToManyField(ObjectType, through_model=HostedSitePageToType)
    """ :type : List[ObjectType] """

HostedSitePage_Deferred.set_model(HostedSitePage)
```

> AttributeError: 'bool' object has no attribute 'name'


Ohh... Now I tried to do the whole thing without `ManyToManyField`, and as far as I understand, Peewee doesn't support tables with `primary_key = False` id they are not the «through» tables?


Yeah, everything works!
So, I stopped using `ManyToManyField`, instead I manually join table for the relationship.

``` python
class HostedSitePage(BaseModel):
    class Meta:
        db_table = "hosted_site_page"

    id = PrimaryKeyField()
    path = CharField()
    title = CharField()
    text = TextField()
    order_num = IntegerField()

    site = ForeignKeyField(HostedSite, related_name="pages")

    def object_types(self) -> List[ObjectType]:
        return [ptt.type for ptt in self._pagetotype_rels]

class HostedSitePageToType(BaseModel):
    class Meta:
        db_table = "hosted_site_page_to_type"
        primary_key = CompositeKey('page', 'type')

    page = ForeignKeyField(HostedSitePage, related_name="_pagetotype_rels")
    type = ForeignKeyField(ObjectType)
```


You really should measure the performance of aggregate_rows vs prefetch....in every test I've done prefetch was faster. Aggregate rows can end up duplicating a ton of data, so if your db is on a network that's a lot of traffic.