Issue: Problem with alias when joining subquery
Author: srolija
State: closed
Created: 2015-10-20 21:50:13

Maybe I assume wrong that following sould work, but there seems to be a issue with alias when I join subquery with table. Since entire subquery will be inserted in SELECT and JOIN clause but should be only in JOIN. Following is simplified version that includes most basic form where I could show problem.

```
from peewee import Model, CharField, DateTimeField, ForeignKeyField, BooleanField
import peewee

database = peewee.SqliteDatabase("data2.db")


class BaseModel(Model):
    class Meta:
        database = database

class Ticket(BaseModel):
    code = CharField(max_length=16, index=True)

class Scan(BaseModel):
    code = CharField(max_length=24)
    confirmed = BooleanField(default=True)

database.create_tables([Ticket, Scan])
Ticket.create(code="a")
Ticket.create(code="b")
Ticket.create(code="c")
Ticket.create(code="d")
Scan.create(code="a", confirmed=True)
Scan.create(code="a", confirmed=False)
Scan.create(code="a", confirmed=False)
Scan.create(code="b", confirmed=False)
Scan.create(code="b", confirmed=False)
Scan.create(code="c", confirmed=True)

sq = Scan.select(Scan).where(Scan.confirmed == True).group_by(Scan.code)
sq = sq.alias("x")
q = Ticket.select(Ticket, sq) \
          .join(sq, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == sq.c.code).alias("scan")) \
          .group_by(Ticket.code)

for t in q:
    print(t.code + ": " + str(t.scan.confirmed))
```

My attempt to fix it generates SQL that returns expected result set (all tickets joined only with confirmed scans), but it is not possible to access joined data (via t.scan, results in AttributeError) which in real models contain additional fields.

```
q = Ticket.select(peewee.SQL("*")) \
          .join(sq, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == sq.c.code).alias("scan")) \
          .group_by(Ticket.code)
```

While I would expect it to output:

```
a: True
b: None
c: True
d: None
```

Meanwhile following works as expected, but query is not the one I want, since it could do join a with scan whose confirmed=False while there do exist some with confirmed=True:

```
q = Ticket.select(Ticket, Scan) \
          .join(Scan, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == Scan.code).alias("scan")) \
          .group_by(Ticket.code)
```


What exactly is your query supposed to be calculating? It seems odd that you've got a `group_by(Scan.code)` but no aggregation. Additionally, you're explicitly selecting all the columns from `Scan` in your subquery. Typically when grouping you select only the column(s) that you want to group by.

To get tickets with confirmed scans, you could write:

``` python
exists = fn.EXISTS(Scan.select().where((Scan.confirmed == True) & (Scan.code == Ticket.code)))
query = Ticket.select(Ticket, exists.alias('confirmed_scan'))
for ticket in query:
    print ticket.code, ticket.confirmed_scan
```

Another way would be:

``` python
confirmed = fn.COUNT(Scan.id).alias('confirmed')
query = Ticket.select(Ticket, confirmed).join(
    Scan, JOIN.LEFT_OUTER, on=(
        (Ticket.code == Scan.code) &
        (Scan.confirmed == True))).group_by(Ticket)
for ticket in query:
    print ticket.code, ticket.confirmed
```


My query should return all records that are confirmed joined with their scan information, since there is slight chance that record could be scanned as confirmed twice (and without confirmation many times - both of those by design) I am grouping them together since I need only to know whether it was confirmed and if it was when (additional field on Scan). Records that are not confirmed or do not have matching scan at all shouldn't be joined with anything but should be returned in resulting dataset (having scan field equal to None).

I do know how to work around this problem using two queries (which works for my needs):

```
q1 = Ticket.select(Ticket, Scan) \
          .join(Scan, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == Scan.code).alias("scan")) \
          .where((Ticket.event == self) & (Scan.confirmed == True)) \
          .group_by(Ticket.code)

sq = Scan.select(Scan.code).where((Scan.event == self) & (Scan.confirmed == True)).alias()
q2 = Ticket.select(Ticket, Scan) \
          .join(Scan, peewee.JOIN.LEFT_OUTER, on=(Ticket.code == Scan.code).alias("scan")) \
          .where((Ticket.event == self) & (~(Ticket.code << sq))) \
          .group_by(Ticket.code)
```

However I would expect query from my original post to work, since it seemed logical (given how API is generaly nice) that one could join subquery in given way.

Don't know if it helps but raw SQL would be something like (and yes, I do need all fields of both ticket and scan):

```
SELECT * FROM "ticket" AS t1 
LEFT OUTER JOIN (
    SELECT * FROM "scan" AS t3 
    WHERE (t3.confirmed = 1) 
    GROUP BY t3.code
) AS x 
ON (t1.code = x.code) 
GROUP BY t1.code
```

However I do not know way how to execute given query using Ticket.raw to make scan data available (using .scan or similar notaion) when result contains it.

Thank you for support and for great library! :)


Did you try either of the example queries I included in my above comment? Both of those should get you where you need to be, or at least get you most of the way there.

I still think that the way you're grouping in the join query is incorrect. If a column is selected and is not an aggregate, it should technically be included in the grouping.


Yes.
Both would work if I just wanted to know whether item is confirmed and I didn't need additional fields that are on Scan model (for simplicity and since it wasn't needed to demonstrate what I thought was an issue I didn't include all fields that models contain just ones used in query).

Items are grouped that way since if ticket does have confirmed scan I do not care which one it is (it is expected that all but one of confirmed scans will be ignored this way) since I can use its details to respond to request.

Main reason for opening this ticket was because I thought that it is possible to do select and then do join on it as on subquery (and still be able to access additional data from subquery using aliased name).


Then you should be able to write:

``` python
query = (Ticket
         .select(Ticket, Scan, confirmed)
         .join(
             Scan,
             JOIN.LEFT_OUTER,
             on=(
                 (Ticket.code == Scan.code) &
                 (Scan.confirmed == True)).alias('scan_obj'))
         .group_by(Ticket))

for ticket in query:
    print ticket.code, ticket.scan_obj.code, ticket.scan_obj.confirmed, ticket.confirmed
```


Fantastic, that was exactly what I wanted to do, but did not know that it is possible to use alias on multiple conditions when using it as join on parameter.

Thank you very much!


Awesome! Glad to help
On Oct 23, 2015 4:48 PM, "Sergej Jakovljev" notifications@github.com
wrote:

> Fantastic, that was exactly what I wanted to do, but did not know that it
> is possible to use alias on multiple conditions when using it as join on
> parameter.
> 
> Thank you very much!
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/733#issuecomment-150701209.