Issue: Join with multiple subqueries does not work
Author: willianantunes
State: closed
Created: 2019-06-10 13:27:43

I have the following sample module configuration to setup Peewee:
```python
from peewee import JOIN
from peewee import Model
from peewee import SqliteDatabase
from peewee import TextField

db = SqliteDatabase(f"tmp-sample.db")


class BaseModel(Model):
    class Meta:
        database = db


class Employee(BaseModel):
    code = TextField(null=True, index=True)

    @staticmethod
    def valids(*fields):
        return Employee.select(*fields).where(Employee.code.is_null(False))


class SalesDetails(BaseModel):
    seller_code = TextField(null=True, index=True)
    manager_code = TextField(null=True, index=True)

    @staticmethod
    def valids(*fields):
        seller = Employee.valids(Employee.code).alias("seller")
        manager = Employee.valids(Employee.code).alias("manager")

        return (
            SalesDetails.select(*fields)
            .join(seller, join_type=JOIN.INNER, on=(SalesDetails.seller_code == seller.c.code))
            .join(manager, join_type=JOIN.LEFT_OUTER, on=(SalesDetails.manager_code == manager.c.code))
        )


db.connect()

# CREATE AND POPULATE DB
if not db.table_exists(SalesDetails):
    db.create_tables([Employee, SalesDetails])
    Employee(code="A123456").save()
    SalesDetails(seller_code="A123456", manager_code="NON-EXIST").save()

try:
    # ASSERTION THAT FAILS
    for sd in SalesDetails.valids():
        assert sd.seller_code == "A123456"
        assert sd.manager_code is None
finally:
    db.close()
```
When I execute the code above, the assertions statements does not pass. Sample output result:

![Screenshot 2019-06-10 13 55 20](https://user-images.githubusercontent.com/4432783/59211970-9a795700-8b87-11e9-908d-2e785e60386d.png)

The generated RAW SQL work properly, but the constructed object does not have the fields it should be.

As a fantastic workaround, I could make it work by doing the following steps:

- Copy the Peewee generated RAW SQL
- Paste it inside `raw` function (look the details [here](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.raw))

Result:

![Screenshot 2019-06-10 11 00 27](https://user-images.githubusercontent.com/4432783/59200590-f97ea200-8b6e-11e9-94d5-e76e556a12f3.png)


What in the fuck is this, do you actually expect me to read this massive wall of "code"?

If you can provide a *minimal* example then I'm happy to reopen and discuss.

Also, Peewee resolves joins w/multiple selections into a graph of models.

If your usage is simplistic and you just need the attrs all available on a single instance, then I suggest you append the following to all your queries: 

```python
query = SomeModel.valids().objects()
query2 = Another.invalids().objects()
```

This will patch all attributes, including any joined attributes, onto a single model.

You can also experiment with `.tuples()` or `.dicts()`.

Sorry, @coleifer! I know what you mean. I'll update the question to be as simple as possible . About your suggestion, I had already done it and got the same error.

No problem, yes a minimal example would be fantastic, esp if it uses well-understood conventions like user-has-many-tweets, etc. Also you forgot to include the traceback (if there was one). That would be helpful.

I'll reopen when I've received additional info.

@coleifer I simplified the code with minimum input in order to reproduce de error just executing the module giving you have Peewee installed. Some additional info that might be helpful:

- I’m processing large CSV files. Each file has its own model
- Some models may have references to other models, but without database integrity (FK)
- `SalesDetails` has two references from `Customer`
- I use `alias` as I am using the same table for two different JOINs

Just a note, you have:

```python
(~(Employee.code >> None))
```

Don't do that! Use this instead:

```python
Employee.code.is_null(False)
```

```python

seller_query = Employee.select().where(Employee.code.is_null(False))
Manager = Employee.alias()
manager_query = Manager.select().where(Manager.code.is_null(False))

query = (SalesDetails
        .select(SalesDetails, Employee, Manager)
        .join(seller_query, on=(SalesDetails.seller_code == seller_query.c.code), attr='seller')
        .join(manager_query, on=(SalesDetails.manager_code == manager_query.c.code), attr='manager'))
for sales_details in query:
    print(sales_details.id, sales_details.seller.code, sales_details.manager.code)
```

I updated the code with [your suggestion](https://github.com/coleifer/peewee/issues/1948#issuecomment-500505104) and I got the same unexpected behavior.

Your sample code showed [here](https://github.com/coleifer/peewee/issues/1948#issuecomment-500506931) does not work as well.

As a TSHOOT example, if I do the following, it does not work:
```python
list(SalesDetails.raw(SalesDetails.valids().sql()[0], *SalesDetails.valids().sql()[1]))
```
But if I do like that, it work properly:
```python
list(SalesDetails.raw("""
SELECT t1.id, t1.seller_code, t1.manager_code
FROM salesdetails AS t1
         INNER JOIN (SELECT t2.code FROM employee AS t2 WHERE (t2.code IS NOT NULL)) AS seller ON (t1.seller_code = seller.code)
         LEFT OUTER JOIN (SELECT t2.code FROM employee AS t2 WHERE (t2.code IS NOT NULL)) AS manager
                         ON (t1.manager_code = manager.code)
"""))
```
@coleifer it seems there is a bug during the bind logic between the RAW SQL and its parameters.

Just to make it clear: the RAW SQL I set on the "work properly" example I got from Peewee.

@coleifer I found a "better" way to make it work:
```python
list(SalesDetails.raw(SalesDetails.valids().sql()[0].replace("\"", ""), *SalesDetails.valids().sql()[1]))
```
Apparently the `"` is the guy which causes the bug.


What all of this tells me is that Peewee is generating the correct SQL. I have *no* idea why or where you are getting confused. For example:

> I got the same unexpected behavior.

**What** unexpected behavior? You still have not specified, but remain stubbornly attached to your own idea of "piping" peewee's *correct* sql into a direct interface to the cursor.

> Your sample code showed here does not work as well.

In what way does it "not work"?

As far as I can tell https://github.com/coleifer/peewee/issues/1948#issuecomment-500506931 should be the right general approach.

Please read:

* http://docs.peewee-orm.com/en/latest/peewee/relationships.html
* Specifically, [Selecting from multiple sources](http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources)

@willianantunes Just a note, in your minimal example, if I modify the query to include `.dicts()` and then print out the record, I get `{'id': 1, 'seller_code': 'A123456', 'manager_code': 'NON-EXIST'}`

And if you look at where you declare the record, it is, in fact, `NON-EXIST`.

Are you expecting `sd.manager_code` to be `None` because it should be the null side of the outer join from the query? That is not intended to be the case given what you've written.

If I wrote something like

```python
        return (
            SalesDetails.select(SalesDetails.seller_code, SalesDetails.manager_code, seller.c.code.alias('scode'), manager.c.code.alias('mcode'))
            .join(
                seller,
                join_type=JOIN.INNER,
                on=(SalesDetails.seller_code == seller.c.code),
            )
            .join(
                manager,
                join_type=JOIN.LEFT_OUTER,
                on=(SalesDetails.manager_code == manager.c.code),
            )
        ).dicts()
```

Then I would generate

```sql
SELECT "t1"."seller_code", "t1"."manager_code", "seller"."code" AS "scode", "manager"."code" AS "mcode"
FROM "salesdetails" AS "t1" INNER JOIN (SELECT "t2"."code" FROM "employee" AS "t2" WHERE ("t2"."code" IS NOT ?)) AS "seller" ON ("t1"."seller_code" = "seller"."code") LEFT OUTER JOIN (SELECT "t2"."code" FROM "employee" AS "t2" WHERE ("t2"."code" IS NOT ?)) AS "manager" ON ("t1"."manager_code" = "manager"."code")
```

and receive the following object (which corresponds to the attributes on a peewee object if I wasn't using a dict): `{'seller_code': 'A123456', 'manager_code': 'NON-EXIST', 'scode': 'A123456', 'mcode': None}`.

`scode` and `mcode` correspond to the records on the joined table. They aren't included by default.