Loopback where filter not working inside include

Question

I have this filter written that i am passing to my endpoint written in Loopbackjs.

  const filter = {
            where: { status: { neq: "processed" } },
            include: [
                { relation: "vendor", scope: { fields: ["id", "name"] } },
                {
                    relation: "pickupLocation",
                    scope: { where: { cityId: "5ee134da0138634c27a6e1dd" } },
                },
            ],
            limit: 200,
            skip: 0
        };
        return this.http.get<IPickupRequest[]>(
            `${environment.url}/PickupRequests?filter=${encodeURIComponent(
                JSON.stringify(filter)
            )}`
        );

The pickupRequest collection contains a belongsTo relation to pickupLocation collection. The pickupLocation collection contains a property called cityId on which i am trying to apply a where clause but it does not work. Moreover I also want to get the fields of pickupLocation too inside the pickupRequest object so that i cna use them in my table. The simple include works perfectly and shows the data as desired but when where clause is applied it just doesn't work and also shows only pickupRequest object but does not include pickupLocation data inside it. Moreover since i am using pagination I need it to return me exactly 200 records each time. Am i doing something wrong? What is the exact issue here? I am using Loopback 3

Answer 1

Here, you are expecting that where filter applied on the pickupLocation relation would act something similar to SQL's inner join. However, in loopback, whenever you do an include in the query, the framework internally makes two separate queries on collections or tables of each of those models and combine them; effectively making it work like SQL's left outer join. So, in the above query PickupRequest list will have all object, irrespective of where filter on pickupLocation model. The pickupLocation relation would be present only in those PickupRequest records where cityId = "5ee134da0138634c27a6e1dd" and remaining places, the relation would be an null object.

To overcome this problem, I would say, make your primary query on pickupLocation model as per the { cityId: "5ee134da0138634c27a6e1dd" } where filter and do an include of PickupRequest model.