Issue: Joined and grouped foreign key values are not cached
Author: conqp
State: closed
Created: 2022-01-06 17:54:30

I have a case where I want to select a one-to-many relation.
According to the [docs](http://docs.peewee-orm.com/en/latest/peewee/querying.html#aggregating-records), this can be done with appropriate selects, joins and group-bys.
However, peewee does not cache query, but executes it again, if I access the backref:

```python
from logging import DEBUG, basicConfig

basicConfig(level=DEBUG)

from hwdb import System, Deployment

select = Deployment.select(System, Deployment).join(System, on=System.deployment == Deployment.id).where(Deployment.id == 354).group_by(Deployment)

dep = select.get()

print(dep.systems)
print(list(dep.systems))
```

Result:
```python
DEBUG:peewee:('SELECT `t1`.`id`, `t1`.`Group`, `t1`.`deployment`, `t1`.`dataset`, `t1`.`openvpn`, `t1`.`ipv6address`, `t1`.`pubkey`, `t1`.`created`, `t1`.`configured`, `t1`.`fitted`, `t1`.`operating_system`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync`, `t2`.`id`, `t2`.`customer`, `t2`.`type`, `t2`.`connection`, `t2`.`address`, `t2`.`lpt_address`, `t2`.`scheduled`, `t2`.`annotation`, `t2`.`testing`, `t2`.`timestamp` FROM `hwdb`.`deployment` AS `t2` INNER JOIN `hwdb`.`system` AS `t1` ON (`t1`.`deployment` = `t2`.`id`) WHERE (`t2`.`id` = %s) GROUP BY `t2`.`id`, `t2`.`customer`, `t2`.`type`, `t2`.`connection`, `t2`.`address`, `t2`.`lpt_address`, `t2`.`scheduled`, `t2`.`annotation`, `t2`.`testing`, `t2`.`timestamp` LIMIT %s OFFSET %s', [354, 1, 0])
SELECT `t1`.`id`, `t1`.`Group`, `t1`.`deployment`, `t1`.`dataset`, `t1`.`openvpn`, `t1`.`ipv6address`, `t1`.`pubkey`, `t1`.`created`, `t1`.`configured`, `t1`.`fitted`, `t1`.`operating_system`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync` FROM `hwdb`.`system` AS `t1` WHERE (`t1`.`deployment` = 354)
DEBUG:peewee:('SELECT `t1`.`id`, `t1`.`Group`, `t1`.`deployment`, `t1`.`dataset`, `t1`.`openvpn`, `t1`.`ipv6address`, `t1`.`pubkey`, `t1`.`created`, `t1`.`configured`, `t1`.`fitted`, `t1`.`operating_system`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync` FROM `hwdb`.`system` AS `t1` WHERE (`t1`.`deployment` = %s)', [354])
[<System: 36>, <System: 359>]
```
As you can see from the last debug output, peewee executes another query to retrieve the related records.
Shouldn't it have cached them from the initial join?

Vanilla peewee test script:
```python
from configparser import ConfigParser
from datetime import datetime
from logging import DEBUG, basicConfig

from peewee import Model, MySQLDatabase
from peewee import BooleanField, CharField, DateField, DateTimeField, ForeignKeyField, FixedCharField

basicConfig(level=DEBUG)


CONFIG = ConfigParser()
CONFIG.read('/usr/local/etc/hwdb.conf')
DATABASE = MySQLDatabase(
    'hwdb',
    host='localhost',
    user='hwdb',
    passwd=CONFIG.get('db', 'passwd')
)

class Deployment(Model):
    """A customer-specific deployment of a terminal."""

    class Meta:
        database = DATABASE
        schema = database.database

    scheduled = DateField(null=True)
    annotation = CharField(255, null=True)
    testing = BooleanField(default=False)
    timestamp = DateTimeField(null=True)


class System(Model):
    """A physical computer system out in the field."""

    class Meta:
        database = DATABASE
        schema = database.database

    deployment = ForeignKeyField(
        Deployment, null=True, column_name='deployment', backref='systems',
        on_delete='SET NULL', on_update='CASCADE', lazy_load=False)
    pubkey = FixedCharField(44, null=True, unique=True)
    created = DateTimeField(default=datetime.now)
    configured = DateTimeField(null=True)
    fitted = BooleanField(default=False)
    monitor = BooleanField(null=True)
    serial_number = CharField(255, null=True)
    model = CharField(255, null=True)   # Hardware model.
    last_sync = DateTimeField(null=True)


select = Deployment.select(System, Deployment).join(System, on=System.deployment == Deployment.id).where(Deployment.id == 354).group_by(Deployment)

dep = select.get()

print(dep.systems)
print(list(dep.systems))
```
Result:

```python
DEBUG:peewee:('SELECT `t1`.`id`, `t1`.`deployment`, `t1`.`pubkey`, `t1`.`created`, `t1`.`configured`, `t1`.`fitted`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync`, `t2`.`id`, `t2`.`scheduled`, `t2`.`annotation`, `t2`.`testing`, `t2`.`timestamp` FROM `hwdb`.`deployment` AS `t2` INNER JOIN `hwdb`.`system` AS `t1` ON (`t1`.`deployment` = `t2`.`id`) WHERE (`t2`.`id` = %s) GROUP BY `t2`.`id`, `t2`.`scheduled`, `t2`.`annotation`, `t2`.`testing`, `t2`.`timestamp` LIMIT %s OFFSET %s', [354, 1, 0])
SELECT `t1`.`id`, `t1`.`deployment`, `t1`.`pubkey`, `t1`.`created`, `t1`.`configured`, `t1`.`fitted`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync` FROM `hwdb`.`system` AS `t1` WHERE (`t1`.`deployment` = 354)
DEBUG:peewee:('SELECT `t1`.`id`, `t1`.`deployment`, `t1`.`pubkey`, `t1`.`created`, `t1`.`configured`, `t1`.`fitted`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync` FROM `hwdb`.`system` AS `t1` WHERE (`t1`.`deployment` = %s)', [354])
[<System: 36>, <System: 359>]
```

If I remove the select and join, it still works:
```python
select = Deployment.select().where(Deployment.id == 354).group_by(Deployment)
```
It seems like `lazy_load=False` is being entirely ignored.

Please consult the docs here: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem

Furthermore, you cannot go from "many" -> "many" in a single go without using prefetch. You can join "upwards" no problem, e.g., select all Systems and also grab their Deployment:

```python
# This executes in a single query.
query = System.select(System, Deployment).join(Deployment)
for system in query:
    print(system.id, system.deployment.annotation)
```

We cannot do the reverse automatically, e.g., get deployments and all their systems, because of SQL's tabular nature.

So you can either:

1. Use `prefetch()` (although I strongly advise profiling this beforehand to see if it actually provides any measurable improvements)
2. Use N+1, e.g., for each deployment also query its systems.

Also note that in your first query you are not actually aggregating anything, and the query probably doesn't do what you think it does.

The docs provide many examples: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem

Lastly, queries generated by accessing a backref are not cached -- accessing the backref creates a new query. If you want to iterate over a backref multiple times you can just assign it to a local variable:

```python

user = User.get(User.username == 'charlie')
tweets = user.tweets
# Query is only evaluated once:
for t in tweets: pass
for t in tweets: pass
```