Loopback: How can i set multiple include with filter on include models ?

[bluestaralone]

Post.find({
  include: {
    relation: 'owner', // include the owner object
    scope: { // further filter the owner object
      fields: ['username', 'email'], // only show two fields
      include: { // include orders for the owner
        relation: 'orders', 
        where: {orderId: 5} // only select order with id 5
      }
    }
  }
}, function() { ... });

As your example in document i just see that it supports to include only one model and submodel but i want to include more than one how can i do that ? Please help me, thanks much.

[raymondfeng]

The include value can be an array of objects. We'll fix the doc to show that too.

[bluestaralone]

Post.find({
  include: [
  {
    relation: 'owner', // include the owner object
    scope: { // further filter the owner object
      fields: ['username', 'email'], // only show two fields
      include: { // include orders for the owner
        relation: 'orders', 
        where: {orderId: 5} // only select order with id 5
      }
    }
  },
  {
    relation: 'thanks', // include the thank object
    scope: { // further filter the owner object
      fields: ['username']
    }
  }
]
}, function() { ... });

When i changed it to array it's not work and showed error syntax.

[raymondfeng]

See https://github.com/strongloop/loopback-datasource-juggler/pull/349

[bluestaralone]

@raymondfeng
scope: { fields: [ 'id', ...] }
in relations type "hasMany" without "through" it works well. but relation "hasMany" with "through" it doesn't work. Please help me. thanks much

Another bug: When i set fields on main filter, all of include info is disappeared.
Ex: In my example when i run find function i just got only 'title', 'description' and don't have 'owner' info. at least if it is empty please show null value or something like that.

Book.find(
{
  where: {userId: id},              
  fields: ['title', 'description'],
  include: {
    relation: 'owner',
    scope: {fields: ['id', 'name']}
  }
}, function() {....});

[raymondfeng]

If multiple includes defined in array is working now, let's close this PR. For the hasMany/through issue, we should track it with a separate one.

[bluestaralone]

Ok, multiple includes defined in array is working perfectly. Thanks much.

[yagobski]

How it works using REST format?

[raymondfeng]

We use the same convention to pass in json objects as query parameters from REST:

• stringified json - ?filter=<stringified json of your query object>

?filter={"where": {"userId": "12"},"fields": ["title", "description"], "include": {"relation": "owner","scope":{"fields": ["id", "name"]}}}

• qs style - https://github.com/hapijs/qs for syntaxes

See http://docs.strongloop.com/display/LB/Querying+data

[yagobski]

Thanks for your answer but i still get error using exactly the same syntaxe.
{"error":{"name":"Error","status":500,"message":"Relation \"relation\" is not defined for Book model"

if i replace "relation" by directly "owner" it works but i can't add "scope". any idea?

This works for me but i want to add scope
?filter={"where": {"userId": "12"},"fields": ["title", "description"], "include": {"owner"}}

[raymondfeng]

I suspect that you don't have the latest version of loopback-datasource-juggler. What version do you have?

[yagobski]

"version": "2.9.0"
I upgrade it and it works now. Thanks :)

[mattiaaccornero]

And if the relation is a HasManyThrough type, how can I include a filter on the through model?
For instance:
Model A has Model C through Model B.
Model B has modelAId and modelCId obviously, and a boolean field (filteringFld).
Is it possible querying it to get all the Model C for Model A where the filteringFld of Model B is true?

[roychoo]

yeah i wan to know this too.

[F3L1X79]

I'd like to know too, if this is possible. Thanks by advance.

[lesterzone]

It's possible to include where on filter and includes ? I researched but no luck.

Book.find(
{
  where: {userId: id},
  include: {
    relation: 'owner',
    where: {id: ownerId }
  }
}, function() {....});

I'm missing something? I tried

Book.find(
{
  where: {userId: id},
  include: {
    relation: 'owner',
    scope: {
      where: {id: ownerId }
    }
  }
}, function() {....});

Thank you.

[hellsan631]

@lesterzone I believe you _can_ use a scope for that 'where' filter. Weird that it isn't working.

On another note, It seems like, currently, the filters are bugged when you send them in as objects instead of array's.

Example system:

I have 3 objects. Locations, Media, and People. Each location hasMany Media, and each Media belongsTo Person (a very basic gallery with a location map).

I want to get a list for a category filter for each location. This means getting each location, each location's media, media's person. this is so I can include the category of each person and media when a user selects a specific location on a map. This is a lot of data for a big system, so I want to limit each of the include scopes only to one field, 'category', for both media and person.

{
  "relation": "media",
  "scope": {
    "fields": {"id": true, "category": true, "personId": true, "person": true},
    "include": {
      "relation": "person",
      "scope": {
        "fields": {"id": true, "category": true},
      }
    }
  }
}

Returns (Actual)

Locations = [
  {
    id: 123,
    category: 'guide'
    media: [
      {itemId: 123},
      {itemId: 123},
      {itemId: 123}
    ]
  }
]

Returns (Expected)

Locations = [
  {
    id: 123,
    category: 'marina'
    media: [
      {id: 124, category:'freshwater', person: {id: 1, category: 'guide'}},
      {id: 125, category:'fly-fishing', person: {id: 1, category: 'guide'}},
      {id: 126, category:'freshwater', person: {id: 2, category: 'lodge-resort'}},
    ]
  }
]

As a work around, I have to use a fields filter that uses both arrays AND an object. This filter correctly produces the above expected results.

/* This Works */
{
  "relation": "media",
  "scope": {
    "fields": ["id", "category", "person", "personId"], /*need to include both Person and PersonID fields for this to work*/
    "include": {
      "relation": "person",
      "scope": {
        "fields": {"id": true, "category": true},
      }
    }
  }
}

[lesterzone]

Thanks @hellsan631 we have locked versions and looks like it's not possible with our versions.

[sonia1588]

Hi,

I have a couple of queries on field filters

1.We tried following query to exclude date property from response as below, which is not working as expected._{"include": { "relation": "orders","scope": { "fields": {"date":false}}}}_

1. fields filters doesn't works with embedded relations.However it works with where filter using dot notation. I tried as below:-
   _{"fields":{"billingAddress.street":true}}_

[renegat59]

Hi!
I have a very simple query that is failing the same way as @bluestaralone. I can't figure out what I can do. The code:

Recipe.find({
      fields: {
        title: true,
        slug: true
      },
      include: {
        relation: 'tags'
      }
    }, function(err, recipes){
      cb(err, recipes);
    });

Recipe has relation "hasAndBelongsToMany" to Tag. With this query I am always getting empty tags. When I run the same query without "include" then the Tags are propagated. Any ideas what I am doing wrong?

[hellsan631]

@renegat59 try including the fields you want for tags as well.

ex:

const filter = {
      fields: {
        title: true,
        slug: true,
        tags: true // you might need to include this field as well
      },
      include: {
        relation: 'tags',
        scope: {
          fields: { id: true, text: true, recipeId: true } //add the recipe ID field, though it may differ depending on how you associate the two data structures.
        }
      }
    };

Recipe
  .find(filter)
  .then((recipies) => {
    // might be populated with recipes and tags
  });

[bgpedersen]

Here is the fix to have fields filter on the main filter and at the same time, have an include relation (also with field filters if wanted). Solution is to include the foreign key as a field, because that field is needed to "connect" the relation:

NOT WORKING:

filter: {
   fields: ['id', 'title'],
   include: {
     'relation': 'Resources',
     'scope': {
       'fields': ['type', 'path']
     }
   }
}

WORKING:

filter: {
   fields: ['id', 'title', 'resourceId'],
   include: {
     'relation': 'Resources',
     'scope': {
       'fields': ['type', 'path']
     }
   }
}

[style2joy]

As in @Moelgaard85's example above I noticed if 'resourceId' is null then property for related object/array doesn't get created. it will help if the result is consistent and include all properties even when blank

[amandeep33]

@raymondfeng
How to include only one property from included relation model as property only not as object.
For eg:

"scope": {
    "include": [
      {
        "relation": "brand",
        "scope": {
          "fields": "brandname"
        }
      }
    ]
  }

is returning:

"brand": {
    "brandid": 1,
    "brandname": "Audi"
  }

but I want this as:
brand:"Audi"
Any Help?

[hellsan631]

@amandeep33

your best bet is to write a little function that takes the data for each object and manipulates it like you want. Your not going to get your data like that from loopback.

mutateBrand({ brand }) {
  return brandname;
}

brands = brands.map(mutateBrand);