Issue: Prefetch many to many fields
Author: Behoston
State: closed
Created: 2019-01-12 22:51:04

I have a problem with prefetching many-to-many field. 
I want to Fetch FieldOfStudy with all tags. I found https://github.com/coleifer/peewee/issues/1707 but it's not applicable for my case. I want to have redy to use object.

My models:

```python
class Model(peewee.Model):
    class Meta:
        abstract = True
        database = db


class Tag(Model):
    name = peewee.CharField()


class FieldOfStudy(Model):
    name = peewee.CharField()
    tags = peewee.ManyToManyField(Tag, backref='fields_of_study')

StudyTag = FieldOfStudy.tags.get_through_model()
```
And my query:

```python
field_of_study = models.FieldOfStudy.select(
    models.FieldOfStudy,
    models.StudyTag,
    models.Tag,
).join(
    models.StudyTag,
).join(
    models.Tag,
).where(
    models.FieldOfStudy.id == 3.
).get()

print(list(field_of_study.tags))
print(list(field_of_study.tags))
print(list(field_of_study.tags))
print(list(field_of_study.tags))

```

Console output:

```python
('SELECT "t1"."id", "t1"."name", "t2"."id", "t2"."fieldofstudy_id", "t2"."tag_id", "t3"."id", "t3"."name" FROM "fieldofstudy" AS "t1" INNER JOIN "fieldofstudy_tag_through" AS "t2" ON ("t2"."fieldofstudy_id" = "t1"."id") INNER JOIN "tag" AS "t3" ON ("t2"."tag_id" = "t3"."id") WHERE ("t1"."id" = %s) LIMIT %s OFFSET %s', [3, 1, 0])
('SELECT "t1"."id", "t1"."name" FROM "tag" AS "t1" INNER JOIN "fieldofstudy_tag_through" AS "t2" ON ("t2"."tag_id" = "t1"."id") INNER JOIN "fieldofstudy" AS "t3" ON ("t2"."fieldofstudy_id" = "t3"."id") WHERE ("t2"."fieldofstudy_id" = %s)', [3])
('SELECT "t1"."id", "t1"."name" FROM "tag" AS "t1" INNER JOIN "fieldofstudy_tag_through" AS "t2" ON ("t2"."tag_id" = "t1"."id") INNER JOIN "fieldofstudy" AS "t3" ON ("t2"."fieldofstudy_id" = "t3"."id") WHERE ("t2"."fieldofstudy_id" = %s)', [3])
('SELECT "t1"."id", "t1"."name" FROM "tag" AS "t1" INNER JOIN "fieldofstudy_tag_through" AS "t2" ON ("t2"."tag_id" = "t1"."id") INNER JOIN "fieldofstudy" AS "t3" ON ("t2"."fieldofstudy_id" = "t3"."id") WHERE ("t2"."fieldofstudy_id" = %s)', [3])
[<Tag: 10>, <Tag: 11>]
[<Tag: 10>, <Tag: 11>]
('SELECT "t1"."id", "t1"."name" FROM "tag" AS "t1" INNER JOIN "fieldofstudy_tag_through" AS "t2" ON ("t2"."tag_id" = "t1"."id") INNER JOIN "fieldofstudy" AS "t3" ON ("t2"."fieldofstudy_id" = "t3"."id") WHERE ("t2"."fieldofstudy_id" = %s)', [3])
[<Tag: 10>, <Tag: 11>]
[<Tag: 10>, <Tag: 11>]
```
I really don't want to query database each time I need those tags. Perfect solution would be 1 query with already prefetched tags.

Because you're still going through the many-to-many accessor, you're incurring a query each time you list the tags.

You might check out the `prefetch` helper: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#using-prefetch

But the problem is this is a relational database, and it doesn't exactly work to say "get me just one of this and then all the related of tags" in a single query. You can of course:

```python
field_of_study = FieldOfStudy.get(FieldOfStudy.id == 3)
tags = [tag for tag in field_of_study.tags]
```

One trick I use is to use `GROUP_CONCAT` to get a list of tags in a single query:

```python
query = (FieldOfStudy
         .select(FieldOfStudy, fn.GROUP_CONCAT(Tag.tag).alias('tag_list'))
         .join(StudyTag, JOIN.LEFT_OUTER)
         .join(Tag)
         .where(FieldOfStudy.id == 3)
         .group_by(FieldOfStudy))

fos = query.get()
print(fos.tag_list)
# tag1,tag2,tag3
```

I have a quick follow-up question. I apologize for the naivete of the question, I'm not very experienced with SQL. I have these two models (simplified), using Sqlite as a backend: 

```
class Node(Model):
    replay_entries = ManyToManyField(ReplayEntry, backref="owners")

class ReplayEntry(Model):
    observation = BlobField(null=True)
```

when I try to run:
```
from peewee import JOIN, fn
query = (Node
         .select(Node, fn.GROUP_CONCAT(ReplayEntry.id).alias('replay_list'))
         .join(Node.replay_entries.get_through_model(), JOIN.LEFT_OUTER)
         .join(ReplayEntry)
         .where(Node.unique_id == 3)
         .group_by(ReplayEntry))
fos = query.get()
```
The object returned has a replay_list that contains just a single integer (e.g. 2), not a list of ReplayEntry ids. Though at the particular moment I run it there should be 4000 ids. I think the single integer returned is specifically the id of the first ReplayEntry in the node's replay_entries list. If I change .get() to .limit(10), then I get 10 Nodes returned, each with a replay_list that corresponds to just the next replay_entry id. I.e. a Node with replay_list=2, then a Node with replay_list=4, then a Node with replay_list=7. Where I would instead expect a single Node with replay_list=[2, 4, 7...]

But I think there might be something more fundamental about this query that I'm not understanding. How does it know that I want the ReplayEntrys specifically on replay_entries? (What if I had another ManyToManyField that referenced ReplayEntry?) 

Overall all I want to do is have a quick way to get all of the replay_entries ids associated with a given node. (The naive way using `[entry.id for entry in node.replay_entries]` is unfortunately too slow for my purposes, so I'm trying to figure out how to speed it up.) 

Thanks!


If you want the ReplayEntry IDs concatenated then you need to be grouping by `Node` rather than `ReplayEntry`.

Oh gotcha, thanks!