Loopback join relation without selecting relation

Question

I'am trying to build a query with loopback 4 with relation between 2 entities

customer.model.ts:

@model()
export class Customer extends Entity {
  // id, name properties
  @hasMany(() => Order)
  orders?: Order[];
}

order.model.ts:

@model()
export class Order extends Entity {
  // id, desc properties
  @belongsTo(() => Customer)
  customerId: Customer;
}

My goal is to get all cutomers that have at least 1 order but without selecting their orders, this my query

    await customerRepository.find({ 
       include: [{ relation: "orders" }],
   });

I tried too :

    await customerRepository.find({ 
       include: [{ relation: "orders" }],
       fields: {propertyName:  }
   });

Thank you for your help!

Answer 1

The behavior of excluding entries that doesn't have any related data (in your case excluding customers without any orders) is possible using INNER JOIN, which currently isn't supported in loopback by default.

You can use this newly published sequelize extension I recently worked on called loopback4-sequelize which supports inner joins as well.

To achieve the expected results you'll need to set required to true as described here, so your filter object will look like this:

{
  "include": [{
    "relation": "orders",
    "required": true, // returns only those customers who have orders
    "scope": {
      "fields": [] // exclude all fields in orders
    }
  }]
}

A point to keep in mind is that loopback by default never run SQL JOIN Queries internally. It uses inclusion resolvers which is responsible to include related models and is called after your source table's data is returned. Resulting in two different calls when you use include in your filter object.