How to query Left Join where the key of the 2nd table is null?

[ujjwalguptaofficial]

Discussed in https://github.com/ujjwalguptaofficial/JsStore/discussions/305

^{Originally posted by **alisabrigok** December 6, 2022} Basically, this is what I'm trying to query: In IDBStudio, if I perform the below query; The where clause doesn't return the records where the customerId of "Orders" is null. ``` select({ from: 'Customers', join: { with: 'Orders', on: "Orders.customerId=Customers.customerId", type: "left", as: { customerId: "orders.customerId" }, where: { customerId: "null" }, } }); ``` It just returns the whole "Customers" records with the columns of the "Orders" merged as all null. I would expect this query to return me the records of "Customers" who don't have any "Orders". Just to give a working SQL example, the below query on this website https://www.programiz.com/sql/online-compiler/ returns the exact desired result. ``` SELECT * FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id WHERE Orders.customer_id IS Null ``` Since we can perform Left Join using JsStore as well, how to achieve this behavior? @ujjwalguptaofficial Thanks in advance.

[ujjwalguptaofficial]

fixed and published in v - 4.4.8

[ujjwalguptaofficial]

From version - 4.5.7, join column can be used in root where. the where in join will work like and query -

e.g -

select({
            from: 'Customers',
            join: {
                with: 'Orders',
                on: "Orders.customerId=Customers.customerId",
                type: "left",
                as: {
                    customerId: "orders_customerId"
                }
            },
            where: {
                orders_customerId: -1234
            }
        })