Issue: Join multiple foreign keys on same table.
Author: conqp
State: closed
Created: 2020-09-22 11:04:34

I have a use case, where I need to join a table that has two foreign keys on another table to this table:

```
COND_DEPLOYMENT = (System.deployment == Deployment.id).alias('deployment')
COND_DATASET = (System.dataset == Deployment.id).alias('dataset')
SYSTEMS = System.select().join(
    Deployment, on=COND_DEPLOYMENT, join_type=JOIN.LEFT_OUTER
).select(System).join(
    Deployment, on=COND_DATASET, join_type=JOIN.LEFT_OUTER
)
```

I later want to select records, that either match fields of deployment, either when referenced by `System.deployment` or `System.dataset`.
Unfortunately, this query does not execute correctly:

```
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1066, "Not unique table/alias: 't2'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 6797, in __iter__
    self.execute()
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 1886, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 1957, in execute
    return self._execute(database)
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 2129, in _execute
    cursor = database.execute(self)
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 3112, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/local/lib/python3.7/dist-packages/peeweeplus/database.py", line 44, in execute_sql
    return super().execute_sql(*args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 3106, in execute_sql
    self.commit()
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 2873, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/dist-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.InternalError: (1066, "Not unique table/alias: 't2'")
```
The query string-serializes to:
```
SELECT `t1`.`id`, `t1`.`deployment`, `t1`.`dataset`, `t1`.`openvpn`, `t1`.`wireguard`, `t1`.`manufacturer`, `t1`.`created`, `t1`.`configured`, `t1`.`operating_system`, `t1`.`monitor`, `t1`.`serial_number`, `t1`.`model`, `t1`.`last_sync` FROM `hwdb`.`system` AS `t1` LEFT OUTER JOIN `hwdb`.`deployment` AS `t2` ON (`t1`.`deployment` = `t2`.`id`) LEFT OUTER JOIN `hwdb`.`deployment` AS `t2` ON (`t1`.`dataset` = `t2`.`id`)
```
As you can see, peewee assigned `t2` two times.
If I manually edit the query to change the second definition of `t2` to `t3` it works as intended.
How can I make this join work in peewee?

As a follow-up question: How can I write the subsequent `WHERE` clause to match either `t2` or `t3`?
A simple `.where(Deployment.attribute == value)` won't suffice, since I want to match explicitely either the deployment from `System.deployment` or `System.dataset`.

Thank you.

You should not call `.select()` twice. Just build up your joins. I suggest you familiarize yourself with the documentation, which covers many topics like querying and joins:

* http://docs.peewee-orm.com/en/latest/peewee/querying.html
* http://docs.peewee-orm.com/en/latest/peewee/relationships.html

Referencing the same table twice, but in different contexts, is done with an alias:

```python
DA = Deployment.alias()

query = (System
         .select()  # If you want all columns, use ".select(System, Deployment, DA)" instead
         .join(Deployment, on=(System.deployment == Deployment.id), attr='deployment')
         .join_from(System, DA, JOIN.LEFT_OUTER, on=(System.dataset == DA.id), attr='dataset'))
```