How to filter data using loopback include relation?

Question

I have three database table such as employee,department and leave_details. employee and department have a relation using foreign key department_id .also employee and leave_details have relation using foreign key user_id I need to get data from leave_details where department_id :x

here my code is like that

leave_details.json

 "relations": {
"employee": {
  "type": "belongsTo",
  "model": "employee",
  "foreignKey": "user_id"
},

and employee.json

 "properties": {"department_id": {
  "type": "number"
}}
 "relations": {
"department": {
  "type": "belongsTo",
  "model": "department",
  "foreignKey": "department_id"
},}

and leave_details.js

 filter = {

    include: [{ relation: 'employee', scope: { where: { department_id: 5} }  }],

    fields: ['id', 'user_id', 'requested_time', 'leave_type', 'leave_start_time', 'leave_status', 'approved_by', 'approval_time', 'leave_end_time'],
    order: 'requested_time DESC',
  };

can you help me

Answer 1

To date, this is not possible using the loopback filter, you need to use database-level queries sql/mongodb aggregate etc.

https://github.com/strongloop/loopback/issues/517

The most convenient for complex queries without breaking the loopback logic is mongodb aggregate queries, if you develop a mixin, you will get something like:

let filter = {
    where:{},
    include:[],
    ...
    aggregate: [{
        $match: {
            period: {
                $lt: period || new Date()
            }
        }
    },
    {
        $sort: {
            period: -1
        }
    },
    ...]
};

or use for example this https://github.com/BoLaMN/loopback-mongo-aggregate-mixin