Include with Filters on Has Many Through Relations

[jcq]

I feel like I must be missing something, but I can't seem to get the "include with filters" functionality to work for me.

The docs give this syntax:

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() { ... });

However, when I try this on my own models (either on the Node side or via API calls), it never seems to properly filter the included items. No matter what I specify in the where clause, it returns all of the items of the relation.

Here's my test code (Loopback 2.10.0):

Post.tagTest = function(cb) {
  Post.find({
    where : {
      id: 428 //sample item that has multiple tags, one with trash:true and one with trash:false
    },
    include: {
      relation: "tags",
      scope: {
        where: {
          trash: false
        }
      }
    }
  }, function(err, res) {
    cb(null, res);
  });
}

Post.remoteMethod('tagTest', {
  http : {verb : 'get' },
  returns: [{arg: 'data', type: 'array'}]
});

No matter what I try here, it returns the full Post object as it should, but includes all associated tags, regardless of the status of the "trash" flag.

This is my (partial) model def:

{
  "name": "Post",
  "plural": "posts",
  "base": "PersistedModel",
  "options": {
    "idInjection": false,
    "mysql": {
      "table": "posts"
    },
    "relations": {
      "tags": {
        "type": "hasMany",
        "model": "Tag",
        "foreignKey": "postId",
        "through": "Tag_Post"
      }
    }
  }
}

Is there a different syntax to use for include with filters when used on Has Many Through relationships?

Thanks,

-jc

[Raymond Feng]

The where should be under scope. I have updated the docs at http://docs.strongloop.com/display/LB/Include+filter to fix the error.

Which DB do you use? You can turn on debugging with ‘DEBUG=loopback:connector:*’ env var.

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

--
You received this message because you are subscribed to the Google Groups "LoopbackJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jcq]

I'm using loopback-connector-mysql.

Unfortunately, when I put the where within a scope block, I end up getting no relations returned. 

This returns all (unfiltered) tags:

include: {
 relation: "tags",
 where: {
 trash: false
 }
}

This returns no tags at all:

scope: {
 where: {
 trash: false
 }
}

I feel like I definitely must be doing something wrong here.  :)

-jc

...

[Raymond Feng]

Please use 'DEBUG=loopback:connector:* node .’ to capture the SQL statements.

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

[Raymond Feng]

I assume you said the following returns no tags:

include: {
 relation: "tags",
 scope: { where: {
 trash: false
 } }
}

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

[jcq]

OK, running it with debugging did help to narrow down the issue. It appears that It's trying to apply the where filter on the junction model, not on the model referenced in the relation property.  Just for kicks, I tried the following, but that didn't work either:

include: {
 relation: "tags",
 scope: {

   relation: "tag",
   scope: {
     where: {
       trash: false
     }
   }
 }
}

That ended up including all the tags again, as if it just discarded the where clause.

What is the correct way to apply the filter to the final related model instead of to the "through" one?

Thanks again,

-jc

[Raymond Feng]

Could you please share your test case?

Sent from my iPhone 6 Plus

[Mattia Accornero]

Same issue here!

I saw my remote method working, but after an update it stops working.

The update was... (from my package.json diff log)

   "dependencies": {

    "compression": "^1.0.3",

    "errorhandler": "^1.1.1",

-    "loopback": "^2.8.0",

-    "loopback-boot": "^2.4.0",

-    "loopback-connector-mongodb": "^1.6.0",

-    "loopback-datasource-juggler": "^2.7.0",

+    "loopback": "^2.15.0",

+    "loopback-boot": "^2.6.5",

+    "loopback-connector-mongodb": "^1.8.0",

+    "loopback-datasource-juggler": "^2.23.0",

    "serve-favicon": "^2.0.1"

  },

  "optionalDependencies": {

-    "loopback-explorer": "^1.1.0"

+    "loopback-explorer": "^1.7.2"

  },

It seems that if I remove the where in scope, it starts working (obviously without filter).

Here the query used in Team.find:

var query = {

"where": { "id": this.teamId },

"include": {

"relation": "players",

"scope": {

"where": {

"hasLeft": false,

"dateTransfer": { "lt": fixedDate }

}

}

}

};

Team.find(query, function(err, team){ //also tried with .findOne( ...

Here the issued relation in my model definition:

"players": {

"type": "hasMany",

"model": "player",

"foreignKey": "teamId",

"through": "transfer"

}

And here the transfer model definition:

{

  "name": "transfer",

  "plural": "transfers",

  "base": "PersistedModel",

  "idInjection": true,

  "properties": {

    "dateTransfer": {

      "type": "date"

    },

    "hasLeft": {

      "type": "boolean"

    }

  },

  "validations": [],

  "relations": {

    "team": {

      "type": "belongsTo",

      "model": "team",

      "foreignKey": ""

    },

    "player": {

      "type": "belongsTo",

      "model": "player",

      "foreignKey": ""

    }

  },

  "acls": [

    {

      "accessType": "*",

      "principalType": "ROLE",

      "principalId": "$everyone",

      "permission": "DENY"

    },

    {

      "accessType": "*",

      "principalType": "ROLE",

      "principalId": "$authenticated",

      "permission": "ALLOW"

    }

  ],

  "methods": []

}

jcq have you found a solution?

[Marc Torruella]

Hi there everyone, 

First of all, I'm new arround so consider the "Hi there" a more solemn thing.

What about this issue? I'm triying to do something of the sort with no luck, is this "where" thing on the relations supposed to work from the angular part? or is it only working server side? or not even that?

Thank you for your time!

Marc

...

[Marc Torruella]

Hi Again,

So I managed to make it "work" but it works as I feared, not as I expected.

When using this:

Picture.find({filter:{
    "include": {
        "relation": "user",
        "scope": {
          where: { 'user.id': 122}
        }
    }
}},function success(data){
    $scope.pictures = data;
});

I would like to get only those pictures that are related to user 122.

But what I actually get is All Pictures, and only the user information on those pictures from user 122. Which make sense with that json structure...

But I tried something bolder, because I'm like that, a cowboy:

Picture.find( {
    filter:{
        "include": {
            "relation": "user",
            "scope": {}
        }
    }
    ,
    where: {
        "user.id":122
}},function success(data){
    $scope.pictures = data;
});

It doesn't blow up, which is promissing, but don't filter either and I got everything back all the same.

I use user.id for simplicity, my intention is to filter by other user fields.

Thank you again for your help and time,

Marc

--
You received this message because you are subscribed to a topic in the Google Groups "LoopbackJS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/loopbackjs/FU7PAkW7VOc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to loopbackjs+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/loopbackjs/46029b22-9702-459d-9fad-eb3520a19fed%40googlegroups.com.

[cameron batt]

Hey Mark, 

Hey Sankalp,

This works: 

    Trip.findById({

      id: id,

      filter: {

        include: [

          'startLocation',

          'endLocation',

          'users',

          'tags'

        ]

      }

    }).$promise

This does not work: 

    Trip.findById({

      id: id,

      filter: {

        include: [

          'tags',

          'startLocation',

          'endLocation',

          {

            relation: 'users',

            scope: {

             fields: ['email', 'id']

            }

          }

        ]

      }

    }).$promise

Related to this:

https://groups.google.com/forum/#!searchin/loopbackjs/cameron$20batt/loopbackjs/jqmtqaB8k-M/DL9IHx63recJ

It's not the angular code, I believe that is generated correctly, it's the backend not handling the filter for relations correctly. 

Checkout a sample app with tests here, you could even add your own filter test to the mix: 

https://github.com/vespertilian/loopback_filter_test

Just npm test

I logged an issue but it has not yet been looked at. 

Cheers

Cam

[Andreas Hegenberg]

Hi Marc,

did you ever get this to work? I'm facing the same issue and have no idea how to solve it :-(

Best,

Andreas