Issue: Unable to join to model on backreference select()
Author: brandond
State: closed
Created: 2019-05-06 21:13:10

I have the following canned query that I expose as a property on a subclass of one of my models. `plugin_incidendents` is a backreference from the PluginIncident model to this model.

```python
class RemediationGroupBase(peewee.Model)
    # model definition here

class RemediationGroup(RemediationGroupBase)
    @property
    def assigned_plugins(self):
        return (self.plugin_incidents
                    .select(PluginIncident.guid, PluginIncident.number, Plugin)
                    .join(Plugin)
                    .switch(Vulnerability)
                    .order_by(PluginIncident.guid, Plugin.id)
                    .where(PluginIncident.guid != GUID_IGNORE)
                    .where(PluginIncident.number.is_null(False))
                    .where(PluginIncident.plugin_id.is_null(False)))
```

I can `count()` this just fine:
```
('SELECT COUNT(1) FROM (SELECT 1 FROM "pluginincident" AS "t1" INNER JOIN "plugin" AS "t2" ON ("t1"."plugin_id" = "t2"."id") WHERE (((("t1"."remediation_group_id" = ?) AND ("t1"."guid" != ?)) AND ("t1"."number" IS NOT ?)) AND ("t1"."plugin_id" IS NOT ?)) LIMIT ? OFFSET ?) AS "_wrapped"', [766, u'AG_BLACK_HOLE_CREATE_NO_TICKET', None, None, 50, 0])
```
But when I try to paginate and iterate over it I get the following:
```
    for i, ticket in enumerate(tickets):
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 4125, in next
    self.cursor_wrapper.iterate()
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 4041, in iterate
    self.initialize()  # Lazy initialization.
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 7062, in initialize
    self._initialize_columns()
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 6962, in _initialize_columns
    node = raw_node.unwrap()
TypeError: unbound method unwrap() must be called with Plugin instance as first argument (got nothing instead)
```

This is because the query is selecting the `plugin` table name as a literal string, instead of selecting all columns from that model:
```sql
SELECT "t1"."guid", "t1"."number", "plugin" AS "t2"
FROM "pluginincident" AS "t1"
INNER JOIN "plugin" AS "t2"
ON ("t1"."plugin_id" = "t2"."id")
WHERE (((("t1"."remediation_group_id" = ?) AND ("t1"."guid" != ?)) AND ("t1"."number" IS NOT ?)) AND ("t1"."plugin_id" IS NOT ?))
ORDER BY "t1"."guid", "t2"."id"
LIMIT ?
OFFSET ?;
```
with the following bindings:
```
[766, u'GUIDVAL', None, None, 50, 0]
```

Am I getting the syntax wrong here or something?

~~What is `Plugin` in this context? Is it a model class? Model alias?~~

Oh, actually nevermind! When re-selecting from an existing query, it seems like there's a bug when you pass a model class directly in.

For now, just explicitly specify the fields or list them, e.g.:

```python
        return (self.plugin_incidents
                    .select(PluginIncident.guid, PluginIncident.number, *Plugin._meta.sorted_fields)
                    .join(Plugin)
```

I think I got it fixed, just FYI in 5f51598.

That fixed it, thanks! I was going to take a shot at working around it with a prefetch but this is much easier.

FWIW, one of the recent changes seems to have also fixed an issue I was having with using a subquery in a join:

```python
team_name = (Team.select(Team.name)
                 .where((Team.name == Host.tags['TechOwnerName']) |
                        (Team.remedy_group == Host.tags['RemedyGroup']))
                 .limit(1))

for vuln in (self.vulnerabilities
                 .select(fn.COALESCE(Team.guid, Vulnerability.guid).alias('guid'),
                         Plugin.id,
                         Vulnerability.plugin,
                         fn.COUNT(Vulnerability.plugin).alias('count'))
                 .join(Query)
                 .switch(Vulnerability)
                 .join(Plugin)
                 .switch(Vulnerability)
                 .join(Host)
                 .join(Team, JOIN.LEFT_OUTER, on=(Team.name == team_name))):
    # do something

```
Previously it would try to append an table alias (`from "team" as "t5"`) to the subquery inside the join statement, which obviously wasn't valid SQL.