Issue: cannot prefetch on a joined class
Author: keredson
State: closed
Created: 2016-05-27 15:26:10

I often need to include all the children of a child table.  For example, if I have a `Post` that FKs to a `Person`, and that `Person` can have multiple `Pets`, I want to query the post with its author and all of that author's pets populated.  Can this be done with peewee?  The documentation for `prefetch` doesn't really say one way or another.

Example:

``` python
import logging, os
import peewee as pw

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

db_fn = 'example.db'
if os.path.exists(db_fn): os.remove(db_fn)
db = pw.SqliteDatabase(db_fn)

class Person(pw.Model):
  name = pw.CharField()
  class Meta:
    database = db

class Post(pw.Model):
  title = pw.CharField()
  author = pw.ForeignKeyField(db_column='author_id', rel_model=Person, to_field='id')
  class Meta:
    database = db

class Pet(pw.Model):
  name = pw.CharField()
  owner = pw.ForeignKeyField(db_column='owner_id', rel_model=Person, to_field='id', related_name='pets')
  class Meta:
    database = db


db.connect()
db.create_tables([Person, Post, Pet])

derek = Person.create(name='Me')
post = Post.create(title='My Post', author=derek)
pet = Pet.create(name='My Pet', owner=derek)

for post in pw.prefetch(Post.select().join(Person), Pet):
  print post, post.author, post.author.pets
```

Gives:

```
$ python test.py 
('CREATE TABLE "person" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL)', [])
('CREATE TABLE "pet" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "owner_id" INTEGER NOT NULL, FOREIGN KEY ("owner_id") REFERENCES "person" ("id"))', [])
('CREATE INDEX "pet_owner_id" ON "pet" ("owner_id")', [])
('CREATE TABLE "post" ("id" INTEGER NOT NULL PRIMARY KEY, "title" VARCHAR(255) NOT NULL, "author_id" INTEGER NOT NULL, FOREIGN KEY ("author_id") REFERENCES "person" ("id"))', [])
('CREATE INDEX "post_author_id" ON "post" ("author_id")', [])
('INSERT INTO "person" ("name") VALUES (?)', [u'Me'])
('INSERT INTO "post" ("title", "author_id") VALUES (?, ?)', [u'My Post', 1])
('INSERT INTO "pet" ("name", "owner_id") VALUES (?, ?)', [u'My Pet', 1])
Traceback (most recent call last):
  File "test.py", line 37, in <module>
    for post in pw.prefetch(Post.select().join(Person), Pet):
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 4913, in prefetch
    fixed_queries = prefetch_add_subquery(sq, subqueries)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 4845, in prefetch_add_subquery
    'query: %s%s' % (subquery, tgt_err))
AttributeError: Error: unable to find foreign key for query: <class '__main__.Pet'> SELECT "t1"."id", "t1"."name", "t1"."owner_id" FROM "pet" AS t1 []
```


First of all this won't work because you need to specifically select values from both the Post and the Person:

``` python
Post.select().join(Person)
```

Should be:

``` python
Post.select(Post, Person).join(Person)
```

More importantly, the way to accomplish what you're trying to specify a chain of models:

``` python
for post in pw.prefetch(Post.select(), Person, Pet):
    print post, post.author,
    for pet in post.author.pets_prefetch:
        print pet,
    print
```

This yields:

``` sql
SELECT "t1"."id", "t1"."name", "t1"."owner_id" 
FROM "pet" AS t1 
WHERE ("t1"."owner_id" IN (
  SELECT "t2"."id" FROM "person" AS t2 
  WHERE ("t2"."id" IN (
    SELECT "t3"."author_id" FROM "post" AS t3))))

SELECT "t1"."id", "t1"."name" FROM "person" AS t1 
WHERE ("t1"."id" IN (
  SELECT "t2"."author_id" FROM "post" AS t2))

SELECT "t1"."id", "t1"."title", "t1"."author_id" FROM "post" AS t1
```


Be sure to read everything here: http://docs.peewee-orm.com/en/latest/peewee/querying.html#avoiding-n-1-queries


I see.  So is it accurate to say that joins and prefetches cannot be mixed?  Is there any way to get just two queries?  (One for the post/person, the second for the pets?)