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

[alisabrigok]

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]

yeah i also think there is an issue particularly when runing where query with left join. Let me have a look.

[ujjwalguptaofficial]

fixed and published in v - 4.4.8. @alisabrigok please check and confirm.

[alisabrigok]

Thanks for the swift reply.

I checked, and yes there is an improvement on the where query but the case I mentioned still doesn't work.

This time below query returns no results. However, it should have returned 19 records.

select({
    from: "Customers",
    join: {
        with: "Orders",
        on: "Orders.customerId=Customers.customerId",
        type: "left",
        as: {
            customerId: "orders.customerId"
        },
        where: {
            customerId: "null"
        },
    }
});

I feel we are so close to getting it fixed now. Previously the where clause in Left Join wasn't filtering records at all.

Thanks again for the good work @ujjwalguptaofficial

[alisabrigok]

I was basically trying to filter the join with orders.customerId. Doesn't the where clause inside of the join applies to the result of the joined query?

If not, then how to query that joined result where orders.customerId is null?

Since I am receiving orders.customerId column with an alias, if I try to query with that alias JsStore throws an error.

So, this doesn't work, it says no index found for column "orders.customerId":

select({
    from: "Customers",
    join: {
        with: "Orders",
        on: "Orders.customerId=Customers.customerId",
        type: "left",
        as: {
           customerId: "orders.customerId"
        },
        where: {
           "orders.customerId": "null"
         },
    },
});

Or moving the where clause outside of the join object also doesn't work:

select({
    from: "Customers",
    join: {
        with: "Orders",
        on: "Orders.customerId=Customers.customerId",
        type: "left",
        as: {
            customerId: "orders.customerId"
        },
    },
    where: {
        "orders.customerId": "null"
    },
});

Or when we query without aliasing, in which case we get customerId null in the records. But that can't also be queried with customerId "null".

select({
    from: "Customers",
    join: {
        with: "Orders",
        on: "Orders.customerId=Customers.customerId",
        type: "left",
        as: {
            customerId: "orders.customerId"
        },
    },
    where: {
        customerId: "null"
    },
});

Basically, back to my original question, how to achieve this query:

SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id
WHERE Orders.customer_id IS Null

Everything is working as expected for join queries right now, but querying for null on keys doesn't work as expected.

I hope you understood what I'm trying to achieve @ujjwalguptaofficial

[ujjwalguptaofficial]

ok, let me see. This is nice.

[ujjwalguptaofficial]

seems like issue is not with query engine but the value null. As null is not allowed in where, so query is returning 0 results. I am still investigating will keep you updated.

If you are working on timeline sensitive, i will suggest to use store to provide local data and run query. https://jsstore.net/tutorial/select/store/

[ujjwalguptaofficial]

@alisabrigok fixed in v - 4.4.9. Please check and confirm.

You can use as to query on second table now. Here is an example -

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

[ujjwalguptaofficial]

here is idbstudio link - https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20'Customers'%2C%0A%20%20%20%20join%3A%20%7B%0A%20%20%20%20%20%20%20%20with%3A%20'Orders'%2C%0A%20%20%20%20%20%20%20%20on%3A%20%22Orders.customerId%3DCustomers.customerId%22%2C%0A%20%20%20%20%20%20%20%20type%3A%20%22left%22%2C%0A%20%20%20%20%20%20%20%20as%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20customerId%3A%20%22orderCustomerId%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20where%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20orderCustomerId%3A%20null%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%7D%0A%7D)