{"action":"created","issue":{"url":"https://api.github.com/repos/strongloop/loopback-connector/issues/31","labels_url":"https://api.github.com/repos/strongloop/loopback-connector/issues/31/labels{/name}","comments_url":"https://api.github.com/repos/strongloop/loopback-connector/issues/31/comments","events_url":"https://api.github.com/repos/strongloop/loopback-connector/issues/31/events","html_url":"https://github.com/strongloop/loopback-connector/pull/31","id":111930956,"number":31,"title":"Query model's relations with SQL","user":{"login":"DiogoDoreto","id":259302,"avatar_url":"https://avatars.githubusercontent.com/u/259302?v=3","gravatar_id":"","url":"https://api.github.com/users/DiogoDoreto","html_url":"https://github.com/DiogoDoreto","followers_url":"https://api.github.com/users/DiogoDoreto/followers","following_url":"https://api.github.com/users/DiogoDoreto/following{/other_user}","gists_url":"https://api.github.com/users/DiogoDoreto/gists{/gist_id}","starred_url":"https://api.github.com/users/DiogoDoreto/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DiogoDoreto/subscriptions","organizations_url":"https://api.github.com/users/DiogoDoreto/orgs","repos_url":"https://api.github.com/users/DiogoDoreto/repos","events_url":"https://api.github.com/users/DiogoDoreto/events{/privacy}","received_events_url":"https://api.github.com/users/DiogoDoreto/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/strongloop/loopback-connector/labels/%23community%20contribution","name":"#community contribution","color":"D4AF37"}],"state":"open","locked":false,"assignee":{"login":"raymondfeng","id":540892,"avatar_url":"https://avatars.githubusercontent.com/u/540892?v=3","gravatar_id":"","url":"https://api.github.com/users/raymondfeng","html_url":"https://github.com/raymondfeng","followers_url":"https://api.github.com/users/raymondfeng/followers","following_url":"https://api.github.com/users/raymondfeng/following{/other_user}","gists_url":"https://api.github.com/users/raymondfeng/gists{/gist_id}","starred_url":"https://api.github.com/users/raymondfeng/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raymondfeng/subscriptions","organizations_url":"https://api.github.com/users/raymondfeng/orgs","repos_url":"https://api.github.com/users/raymondfeng/repos","events_url":"https://api.github.com/users/raymondfeng/events{/privacy}","received_events_url":"https://api.github.com/users/raymondfeng/received_events","type":"User","site_admin":false},"milestone":null,"comments":28,"created_at":"2015-10-16T23:49:49Z","updated_at":"2015-11-23T20:06:30Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/strongloop/loopback-connector/pulls/31","html_url":"https://github.com/strongloop/loopback-connector/pull/31","diff_url":"https://github.com/strongloop/loopback-connector/pull/31.diff","patch_url":"https://github.com/strongloop/loopback-connector/pull/31.patch"},"body":"### Update:\r\n\r\nThis pull-request started as a proof of concept and with a ongoing development, now I have done everything I wanted. The included features are:\r\n\r\n- [x] Query 1:n relations (Example below)\r\n- [x] Query n:n relations (Example in https://github.com/strongloop/loopback-connector/pull/31#issuecomment-158819653)\r\n- [x] Allow ordering using a relation's property (Example in https://github.com/strongloop/loopback-connector/pull/31#issuecomment-156429761)\r\n- [x] Supports `count` queries as well\r\n\r\nThanks to @EmileSpecs and @ianbelcher for helping me test this PR.\r\n\r\nBelow is the original comment, for reference.\r\n\r\n---\r\n\r\nThis feature is a must have on my project, so I started to work on it. I know that currently it is missing a lot of things. But I think that we can discuss it here and use this as a starting point to the final version of the feature, since this is something new to Loopback and will need docs and so on.\r\n\r\nI will start with an example. Let's imagine that a model `Customer` have many `Address` and I want to get all the customers that live on Brazil. I can create the SQL like this:\r\n\r\n```javascript\r\nvar sql = connector.buildSelect('customer', {\r\n  where: {\r\n    address: {\r\n      where: {country: 'Brazil'}\r\n    }\r\n  }\r\n});\r\n```\r\n\r\nAnd `sql` will be:\r\n\r\n```\r\nParameterizedSQL {\r\n  sql: 'SELECT DISTINCT `CUSTOMER`.`ID`,`CUSTOMER`.`NAME` FROM `CUSTOMER` INNER JOIN ( SELECT `ADDRESS`.`CUSTOMERID` FROM `ADDRESS` WHERE `ADDRESS`.`COUNTRY`=? ORDER BY `ADDRESS`.`ID` ) AS `ADDRESS` ON `CUSTOMER`.`ID`=`ADDRESS`.`CUSTOMERID`  ORDER BY `CUSTOMER`.`ID`',\r\n  params: [ 'Brazil' ] }\r\n```\r\n\r\nFormatted SQL:\r\n\r\n```sql\r\nSELECT DISTINCT `CUSTOMER`.`ID`, \r\n                `CUSTOMER`.`NAME` \r\nFROM   `CUSTOMER` \r\n       INNER JOIN (SELECT `ADDRESS`.`CUSTOMERID` \r\n                   FROM   `ADDRESS` \r\n                   WHERE  `ADDRESS`.`COUNTRY` = ? \r\n                   ORDER  BY `ADDRESS`.`ID`) AS `ADDRESS` \r\n               ON `CUSTOMER`.`ID` = `ADDRESS`.`CUSTOMERID` \r\nORDER  BY `CUSTOMER`.`ID` \r\n```\r\n\r\n---\r\n\r\nSo, my proposal is to enable users to use the name of relation as a key inside `filter.where` and the value will be a full filter for this inner select. This allows the use of `order` and `limit` as well (and in the future `groupBy`, in case it gets implemented).\r\n\r\nThe implementation is recursive, allowing to nest multiple filters.\r\n\r\nIt's worth pointing out that all the current failing tests are because I had to include the table name whenever column names were used, to fix ambiguity problems in some cases. As far as I could check, all the resulting SQLs were still valid.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/strongloop/loopback-connector/issues/comments/159048388","html_url":"https://github.com/strongloop/loopback-connector/pull/31#issuecomment-159048388","issue_url":"https://api.github.com/repos/strongloop/loopback-connector/issues/31","id":159048388,"user":{"login":"superkhau","id":1617364,"avatar_url":"https://avatars.githubusercontent.com/u/1617364?v=3","gravatar_id":"","url":"https://api.github.com/users/superkhau","html_url":"https://github.com/superkhau","followers_url":"https://api.github.com/users/superkhau/followers","following_url":"https://api.github.com/users/superkhau/following{/other_user}","gists_url":"https://api.github.com/users/superkhau/gists{/gist_id}","starred_url":"https://api.github.com/users/superkhau/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/superkhau/subscriptions","organizations_url":"https://api.github.com/users/superkhau/orgs","repos_url":"https://api.github.com/users/superkhau/repos","events_url":"https://api.github.com/users/superkhau/events{/privacy}","received_events_url":"https://api.github.com/users/superkhau/received_events","type":"User","site_admin":false},"created_at":"2015-11-23T20:06:30Z","updated_at":"2015-11-23T20:06:30Z","body":"@raymondfeng Can you review this?"}}