Peewee: Join with multiple subqueries does not work

[willianantunes]

I have the following sample module configuration to setup Peewee:

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:

The generated RAW SQL work properly, but the constructed object does not have the fields it should be.

[willianantunes]

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)

Result:

[coleifer]

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.

[coleifer]

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:

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().

[willianantunes]

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.

[coleifer]

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.

[willianantunes]

@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

[coleifer]

Just a note, you have:

(~(Employee.code >> None))

Don't do that! Use this instead:

Employee.code.is_null(False)

[coleifer]

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)

[willianantunes]

I updated the code with your suggestion and I got the same unexpected behavior.

Your sample code showed here does not work as well.

As a TSHOOT example, if I do the following, it does not work:

list(SalesDetails.raw(SalesDetails.valids().sql()[0], *SalesDetails.valids().sql()[1]))

But if I do like that, it work properly:

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.

[willianantunes]

@coleifer I found a "better" way to make it work:

list(SalesDetails.raw(SalesDetails.valids().sql()[0].replace("\"", ""), *SalesDetails.valids().sql()[1]))

Apparently the " is the guy which causes the bug.

[coleifer]

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

[logannc]

@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

        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

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.