How to limit records of relations with include-filter in loopback?

Question

I want to query records from a specific model via REST-Api from a LoopBack-application. Also i want to include related objects via the include-filter. This works fine, but returns ALL related objects. Is it possible to limit them and also to order them by a field of related objects?

Models:

-   DEPARTMENT
    Fields:
        - id
        - name 
        - ...
    Relations_ -> hasMany: Messages
    Relations_ -> hasMany: Members

- MESSAGE
    Fields:
       - id
       - senderId
       - body
       - ...

- MEMBER
    Fields:
       - id
       - email
       - ...

Queries:

What i want to achieve is to query all Departments with all their members, but only the last message ordered by a specific field (created-timestamp).

The first approach could be the plain query-string variant of a GET-Request:

http://loopback-server:3000/api/departments?filter[include]=members&filter[include]=messages

This will return all departments with all messages and all members. However, i would like to limit the number of returned messages to the last one (or last 5 or whatever, sorted by a specific field of MESSAGE-model.

I also tried the jsonfied query syntax:

http://loopback-server:3000/api/departments?filter={"include":{"relation": "messages","limit":1}}

Unfortunately the "limit"-parameter is not used here for the relation of messages.

The following variant will return only first department, means the limit-param is applied to the departments-model, not the relation model.

http://loopback-server:3000/api/departments?filter={"include":{"relation": "messages"},"limit":1}

Then i discovered the scope-parameter and tried this:

http://loopback-server:3000/api/departments?filter={"include":{"relation": "messages","scope":{"limit":1, "skip":0}}}

This gives a really strange result. This ommits all messages related to departments, instead of one specific record returning one message (it has over 10), what i would expect. Removing the scope-parameter shows that the departments indeed has many messages each.

(I know that the parameters of an URL with all these special characters like {",:"} need to be url-encoded. I leave it clean here for better readability)

My question:

How to achieve that query with a single request?

Answer 1

It's not possible to query relationships by their properties (yet). As for the limit, your last approach with the scope should be modified a little:

"scope":{{"include":{"relation": "messages","limit":1, "skip":0}}}

Here you can read about queries on relations by their properties:

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

Answer 2

I dunno what version you are in, but for Loopback 3

you can do this..

include: {
    {
        relation: 'Messages', // include the messages object
        scope: { // this is where you do a normal filter
            where: {<whatevercondition>},
            order: "<fieldname> <ASC/DESC>",
            limit:1,
            include:{
                //yes, you can include 3rd level relation as well.
            }
        }
    },
    {
        relation: 'Members', // include the Members object
        scope: { // further filter the related model
            order: "<fieldname> <ASC/DESC>",
            limit: <whateverlimityoument>
        }
    }
}