Loopback: Filter on level 2 properties
I am trying to use the ?filter rest method, in order to query on nested properties. However, this doesn't seem to work:
/someResource?filter[where][field.subField][like]=abc
neither does this (which I think is wrong anyway):
/someResource?filter[where][field][subField][like]=abc
Any hints?
daankets
All 244 comments
Which connector are you using?
fabien
on 29 Aug 2014
Memory in dev, mongo in test and beyond. It's the memory connector that has the issues. We use this one for our dev setup as it resets upon restart...
It's the 'dot' notation that is working with mongo.
daankets
on 29 Aug 2014
This is currently a limitation of the memory connector.
@raymondfeng perhaps we can use something like https://github.com/logicalparadox/filtr or https://github.com/crcn/sift.js to enable this
fabien
on 29 Aug 2014
Well, the nice thing about the memory connector is that it resets, so you test every run with a clean sheet. And of course it's rather speedy ;-)
daankets
on 29 Aug 2014
Of course - however, you can easily reset the test database before you start, like this:
app.datasources['db'].automigrate(next);
Will this drop the entire schema?
daankets
on 29 Aug 2014
Yes, but in the case of MongoDB collections will be empties and indexes re-created. There's no real schema, like with SQL databases.
fabien
on 29 Aug 2014
Sure. Just calling it a 'schema' as a 'habit'
daankets
on 29 Aug 2014
Of course! While collections are being recreated, they are not really removed from the database first. So any obsolete collections will stay around, until you delete them, or the database itself. AFAIK loopback will create the db for you, in case it doesn't exist.
fabien
on 29 Aug 2014
@fabien These libraries are interesting:
I also looked at:
Keep in mind, RDBs won't support nested queries.
raymondfeng
on 29 Aug 2014
@raymondfeng sift.js looks good to me, but it doesn't support skip/limit. nedb does, but it seems quite a bit more involved. It can probably be implemented as a new connector altogether.
Users should be made aware of the limits of RDBM's, which is perhaps easiest when having this as a separate connector - so users have to be explicit about it, instead of relying on the memory connector (for tests and so forth) to support any type (both SQL as well as NoSQL style) of query. It's probably easy to copy the mongodb connector's query normalization code and implement this.
Here are some more libraries of interest:
- https://github.com/kawanet/obop
- http://kawanet.github.io/kagodb/
- https://github.com/sergeyksv/tingodb (but why use this, over mongodb?)
https://github.com/bevry/query-engine~~ (nevermind, too heavy)
fabien
on 30 Aug 2014
Loosely related: #683 How can i set condition where on include model
bajtos
on 23 Oct 2014
How this can work for mysql connector? I have same issue
yagobski
on 24 Nov 2014
Looks like Loopback currently does not support nested include filtering. This seems like a huge limitation. I don't believe the db connector should make any difference because ideally the Loopback framework would work the same regardless of what db connector you're using
stringbeans
on 19 Dec 2014
i'm having the same problem trying to do a nested include. Any solution ?
weslley39
on 20 Jan 2015
So are there any plans to support nested properties? Is there any way to get the "native" mongodb-connection so we can query the database without the loopback abstraction?
GameScripting
on 22 Jan 2015
Hey! I've got the Same Problem. I want to query a List of objects based on the property of an related model as well as the property of the wanted model e.g:
Event.find( { where: { and: [ { endDate : { lt : givenDate}}, { location.name : "Abc" }]} }, cb );
So, am I right, that this is not supported atm?
dominikriedel
on 27 Jan 2015
I want to query a List of objects based on the property of an related model as well as the property of the wanted model.
So, am I right, that this is not supported atm?
You are right, this it not supported yet.
bajtos
on 28 Jan 2015
so the easiest way would be to just query via SQL? Without any further knowlage: is there any caching mechanism I could use?
dominikriedel
on 28 Jan 2015
Can we upvote on this feature?
pungoyal
on 27 Feb 2015
Sure, leave a comment containing one of :+1: or +1 (as regular text, not like I did).
bajtos
on 27 Feb 2015
:+1:
pungoyal
on 27 Feb 2015
+1
Jeff-Lewis
on 27 Feb 2015
+1
GameScripting
on 27 Feb 2015
+1
weslley39
on 27 Feb 2015
:+1:
violet-day
on 28 Feb 2015
+1
manlito
on 2 Mar 2015
+1
stringbeans
on 3 Mar 2015
+1
jsam
on 3 Mar 2015
+1
mrbatista
on 4 Mar 2015
I guess we can make it happen for NoSQL DBs, but it will be difficult for RDBs.
raymondfeng
on 5 Mar 2015
+1
rabchev
on 6 Mar 2015
As far as I am concerned, a good start would be to support nested filters for embedded objects and embedded models.
bajtos
on 6 Mar 2015
+1
johnsoftek
on 15 Mar 2015
+1
hotaru355
on 16 Mar 2015
+1
yagobski
on 17 Mar 2015
+1
AurelieV
on 26 Mar 2015
+1
jbeurel
on 26 Mar 2015
+1
wxqp
on 26 Mar 2015
+1
carliwis
on 26 Mar 2015
What databases are you using? Is it enough to support filtering on second-level properties for memory and MongoDB, or do you need it for SQL databases too?
bajtos
on 27 Mar 2015
https://github.com/strongloop/loopback-datasource-juggler/pull/544 is adding support for dot-notation to the memory connector, to make it consistent with MongoDB.
This should hopefully fix the issue for @daankets as described in his earlier comment:
Memory in dev, mongo in test and beyond. It's the memory connector that has the issues. We use this one for our dev setup as it resets upon restart...
It's the 'dot' notation that is working with mongo.
Implementing this feature for non-embedded relations (e.g. hasMany, belongsTo) and supporting SQL connectors (both embedded and non-embedded relations) requires significant amount of time, which we can't invest right now.
bajtos
on 1 Apr 2015
+1
mattiaaccornero
on 7 Apr 2015
Thanks for this! It's making testing quite a bit easier ;-)
daankets
on 8 Apr 2015
+1
sisardor
on 24 Apr 2015
:+1:
eugenehp
on 24 Apr 2015
+1
I really love the way PHP's Doctrine ORM has implemented this feature. For some databases filtering takes place on the database level. When a specific database connector does not support the filtering interface Doctrine does the filtering for you using PHP.
http://doctrine-orm.readthedocs.org/en/latest/reference/working-with-associations.html#filtering-collections
I understand it would take a lot of time to provide this feature for all databases, so perhaps it is an option to adapt such a pattern.
erikverheij
on 18 May 2015
+1
jasiedu
on 31 May 2015
+1
jgpacheco
on 10 Jun 2015
:+1:
bryannaegele
on 28 Jun 2015
+1
akshayKrSingh
on 28 Jun 2015
+1
fabriciomassula
on 29 Jun 2015
+1
valllabh
on 1 Jul 2015
:+1:
robi-van-kinobi
on 6 Jul 2015
+1
holaontiveros
on 7 Jul 2015
+1
rhymn
on 10 Jul 2015
+1
2015-07-10 11:24 GMT+02:00 David [email protected]:
+1
—
Reply to this email directly or view it on GitHub
https://github.com/strongloop/loopback/issues/517#issuecomment-120315877
.
yagobski
on 10 Jul 2015
+1 Using MySQL (sadly)
maddes
on 14 Jul 2015
+1
ghislainf
on 29 Jul 2015
+1
framp
on 29 Jul 2015
+1
jlvdh
on 12 Aug 2015
+1
taoyuan
on 21 Aug 2015
+1
husseiny-mohamed
on 27 Aug 2015
+1 (Postgre)
geekguy
on 28 Sep 2015
+1 (Postgre)
conradj
on 4 Oct 2015
+1
hourliert
on 5 Oct 2015
+1
solher
on 5 Oct 2015
+1
nuba
on 9 Oct 2015
:+1:
ziad-saab
on 14 Oct 2015
For those who need this in SQL relations, I've created a fork of loopback-connector and already got something good.
Here's a example. Suppose that a model customer have many orders, then you can create a query like this:
var sql = connector.buildSelect('customer', {
where: {
vip: true,
orders: {
where: {
id: 42
}
}
}
});
// which results in
ParameterizedSQL {
sql: 'SELECT DISTINCT `CUSTOMER`.`NAME`,`CUSTOMER`.`VIP`,`CUSTOMER`.`ADDRESS` FROM `CUSTOMER` INNER JOIN ( SELECT `ORDER`.`CUSTOMERID` FROM `ORDER` WHERE `ORDER`.`ID`=$1 ORDER BY `ORDER`.`ID` ) AS `ORDER` ON `CUSTOMER`.`NAME`=`ORDER`.`CUSTOMERID` WHERE `CUSTOMER`.`VIP`=$1 ORDER BY `CUSTOMER`.`NAME`',
params: [ 42, true ]
}
with some line breaks for readability:
SELECT DISTINCT `CUSTOMER`.`NAME`,
`CUSTOMER`.`VIP`,
`CUSTOMER`.`ADDRESS`
FROM `CUSTOMER`
INNER JOIN (SELECT `ORDER`.`CUSTOMERID`
FROM `ORDER`
WHERE `ORDER`.`ID` = $1
ORDER BY `ORDER`.`ID`) AS `ORDER`
ON `CUSTOMER`.`NAME` = `ORDER`.`CUSTOMERID`
WHERE `CUSTOMER`.`VIP` = $1
ORDER BY `CUSTOMER`.`NAME`
You can already query multiples relations together and even nest then.
I will work more on this over the weekend and create some tests as well.
Regarding tests, my modifications didn't break any available tests, with the exception of one single line in the function columnEscaped, where I needed to add the table name before the column name to resolve some ambiguity problems. This broke a lot of tests, but the SQL output is still valid.
DiogoDoreto
on 16 Oct 2015
Hey @DiogoDoreto looks good.
What about REST filters?
Thanks.
eugenehp
on 16 Oct 2015
@eugenehp buildSelect is a low level function that is used by REST filters, so they get the feature as well.
DiogoDoreto
on 16 Oct 2015
Would you mind @DiogoDoreto if I ask you to show me the similar query in the REST format (just the same one you posted above)?
Thank you.
eugenehp
on 16 Oct 2015
I think it would be something like /customers?filter[where][vip]=true&filter[where][orders][where][id]=2.
It's important to note that orders is the name of the relation defined in customer model and that the value in it is a full filter, which means that besides where you can also use limit, order, etc
DiogoDoreto
on 16 Oct 2015
interesting. Thanks Diogo. I'll give it a try and let you know.
Have a great weekend ahead!
Cheers.
On Fri, Oct 16, 2015 at 9:57 PM, Diogo Doreto [email protected]
wrote:
I think it would be something like
/customers?filter[where][vip]=true&filter[where][orders][where][id]=2.It's important to note that orders is the name of the relation defined in
customer model and that the value in it is a full filter, which means
that besides where you can also use limit, order, etc—
Reply to this email directly or view it on GitHub
https://github.com/strongloop/loopback/issues/517#issuecomment-148819398
.
eugenehp
on 16 Oct 2015
Thank you! if you are using loopback-connector-mysql you can change the dependency version on package.json to DiogoDoreto/loopback-connector-mysql#query-relations to get my modifications.
DiogoDoreto
on 16 Oct 2015
@DiogoDoreto Would you like to submit a PR? I can get the PR prioritized for review during sprint planning since everyone in the community really wants this feature.
superkhau
on 16 Oct 2015
DiogoDoreto
on 17 Oct 2015
+1
mike-aungsan
on 13 Nov 2015
I use mongo connector and having error. Any help? Anything wrong with query string?
user obj
{
"profile": {
"username": "[email protected]"
}
}
?filter[where][profile.username][email protected]
{"error":{"name":"Error","status":404,"message":"Unknown \"user\" id \"undefined\".","statusCode":404,"code":"MODEL_NOT_FOUND"}}
mike-aungsan
on 13 Nov 2015
It does not work with find REST API.
Anyway all of those info should be in this link :)
https://docs.strongloop.com/display/public/LB/Where+filter
mike-aungsan
on 14 Nov 2015
+1
the dot notation doesn't seem to work with Angular SDK / mongo
eg:
Published.find({
filter: {
where: {
'post.id' : 'xyz'
}
}
}
returns no documents
Published.find({
where: {
'post.id' : 'xyz'
}
}
returns all the documents
seanbrookes
on 1 Dec 2015
+1
gustavomick
on 10 Dec 2015
+1
sculebras
on 10 Dec 2015
+1
drwaldo
on 10 Dec 2015
+1
deweydb
on 11 Dec 2015
Can I pledge a donation amount to ensure this issue is worked on in the near future?
deweydb
on 11 Dec 2015
@deweydb Haha, if things only worked that way. Let's do this, I will bump this to critical to make sure this comes up during sprint planning.
superkhau
on 11 Dec 2015
However, that said, it's still in triage status and we need to figure out all the use cases. If you guys could provide me with more sample repos, we can use them to create tests to make sure we get the coverage needed.
superkhau
on 11 Dec 2015
+1
FelipeAguayo
on 14 Dec 2015
I have data stored in JSON columns in Postgres - it would be nice if I could do nested filters there as well.
For example, something simple like:
{
"customerId": 0,
"customer": {},
"source": "string"
}
where the customer is a Object field with a postgres JSON column type (it displays as {} in the Explorer) and the customerId is an int with source as a string. I'd want to query/filter on something like customer.dateofbirth.
talvola
on 15 Dec 2015
+1
eugeniol
on 11 Jan 2016
+1
danielrvt
on 11 Jan 2016
+1
jobezic
on 15 Jan 2016
:+1: for RDB
rmartins90
on 2 Feb 2016
+1
DiogoMarques29
on 2 Feb 2016
+1
evinw
on 2 Feb 2016
+1
rpalacio86
on 3 Feb 2016
+1 (Postgresql)
ank-it
on 5 Feb 2016
+1
defaultstate
on 5 Feb 2016
+1 for mysql
nicktee89
on 15 Feb 2016
+1 for mysql
gryp17
on 15 Feb 2016
+1
Viewing your scrum board I see that this is still in backlog. Is there any ETA on this considering there is already some PR work done?
PharaohMaster
on 27 Feb 2016
+1
amanalili
on 27 Feb 2016
Using mongo connector I have discovered that the where filter does not work on nested properties where the value includes spaces:
{
"company" {
"name":"strongloop company"
}
}
/api/Customers/?filter[where][company.name][like]="strongloop comp"
does not work, however
/api/Customers/?filter[where][company.name][like]="strongloop"
does work
the same results for the "regexp" operator.
it looks like SL is not allowing spaces as literal, perhaps treating them as a end of string delimiter?
joelosx
on 27 Feb 2016
+1 PostgreSQL
Ascarbek
on 29 Feb 2016
+1
pkgulati
on 2 Mar 2016
@joelosx Using mongo connector I have discovered that the where filter does not work on nested properties where the value includes spaces
please open a new GH issue to track that problem.
@PharaohMaster Viewing your scrum board I see that this is still in backlog. Is there any ETA on this considering there is already some PR work done?
I am afraid this isn't among our top priorities right now, I cannot give you any ETA.
bajtos
on 2 Mar 2016
+1
Elindorath
on 2 Mar 2016
+1
stanBienaives
on 2 Mar 2016
:+1: (postgres)
FreakTheMighty
on 3 Mar 2016
+1
bbito
on 8 Mar 2016
:+1:
jbcpollak
on 16 Mar 2016
+1
donaldG21
on 21 Mar 2016
+1
mrjrdnthms
on 22 Mar 2016
+1 (mssql)
christiaanwesterbeek
on 23 Mar 2016
So far it's the work in progress:
https://github.com/strongloop/loopback-connector/pull/40
kolach
on 24 Mar 2016
:+1:
tjosbon
on 25 Mar 2016
:+1:
celber
on 5 Apr 2016
+1
farlymedicaboo
on 6 Apr 2016
+1
ahmedmoneam
on 11 Apr 2016
:+1:
eduludi
on 12 Apr 2016
+1
Webjin
on 16 Apr 2016
:+1:
nicgirault
on 27 Apr 2016
+1
walkev13
on 27 Apr 2016
+1
akrasov
on 6 May 2016
+1
juanpujol
on 10 May 2016
+1
jeff3yan
on 10 May 2016
+1
sklein12
on 10 May 2016
+1 oracle
nateha1984
on 10 May 2016
+1 fields on nested attributes + dotted notation
FredericLeuba
on 11 May 2016
+1
lucaslopez
on 11 May 2016
While waiting on this feature, I'd like to create a remoteMethod that does an INNER JOIN, resulting in the dataset I want, but I'd like to be able to keep a relatively similar filter/sort/order REST API parameter set if possible.
Is there any method to compile some of the query parameters into SQL? Or possibly a subset of the filters.
jeff3yan
on 16 May 2016
+1
zbarbuto
on 17 May 2016
+1 (Postgre)
Kumuluzz
on 17 May 2016
:+1:
RDSolink
on 19 May 2016
+1
teropes
on 19 May 2016
+1
emmanuelpautet
on 31 May 2016
+1
lughino
on 1 Jun 2016
+1
agustinaliagac
on 1 Jun 2016
+1
HarishVara
on 3 Jun 2016
+1 Using MySQL (not sadly)
cpatti97100
on 7 Jun 2016
+1 using mongo connector
The ability to query and filter/exclude data based on relations like belongsTo and hasMany is really very important. Please try to prioritize this!
btdevn
on 8 Jun 2016
+1
KaanErkol
on 8 Jun 2016
+1
mikesteele
on 8 Jun 2016
+1 (mssql)
zamb3zi
on 9 Jun 2016
+1
ahmed-abdulmoniem
on 11 Jun 2016
+1
sasivarnan
on 16 Jun 2016
+1
CarlosCuevas
on 21 Jun 2016
+1
santhoshscs
on 24 Jun 2016
+1
codyolsen
on 27 Jun 2016
+1
ron137
on 1 Jul 2016
+1
MartinCerny-awin
on 7 Jul 2016
+1
mharkus
on 9 Jul 2016
+1
foodaka
on 13 Jul 2016
+1
bekopharm
on 13 Jul 2016
+1
juanscolari
on 20 Jul 2016
+1. This is seriously inconvenient, because the order of included relations isn't stable if limit changes, making it impossible to reliably paginate across them.
stretchkennedy
on 20 Jul 2016
+1
Bohmaster
on 20 Jul 2016
👍
rflipo-dev
on 27 Jul 2016
Hi,
I tried querying level 2 properties and i was able to get the data right.
Eg. I used
{"where": {"and": [{"name": "Eureka Outsourcing"},{"pocs.phone":{ "inq": [2225302400] }}, {"costToUs.name":{ "inq": ["Kiosk"] }}]}}
ON JSON Data:
{
"name": "Eureka Outsourcing",
"pocs": [
{
"name": "Hemchandra Pujari",
"phone": 2225302433,
"email": "[email protected]"
},
{
"name": "Ranshinghe",
"phone": 2225302400
}
],
"id": "5770feb6268c56b00cc708c6",
"costToUs": [
{
"name": "Kiosk"
}
]
}
princecharmx
on 2 Aug 2016
What would be the trouble of making it work at RDBs, like MySQL or MsSQL?
richardaum
on 10 Aug 2016
@richardaum Apparently, given that this issue has been open since 2014, a lot.
FTR for anyone who finds this issue, I don't think this is related to INNER JOIN style queries as some like @jeff3yan have mentioned. The OP seems to be referring explicitly to querying on sub-properties of object properties. E.g if a use has an Address which is an object, querying on street number. The reason this isn't really possible in RDS (short of selecting all records and filtering manually) is because loopback stores Object properties as stringified JSON. The only other way might be to construct some sort of regexp that would apply to the underlying JSON string and querying on that.
As far as related (JOIN) queries go (e.g. find customers who have not made any purchases) that doesn't seem possible without executing SQL directly via ds.connector.execute. I believe that is a really important feature that would definitely be doable but probably warrants a separate issue.
zbarbuto
on 11 Aug 2016
+1 (For search features across related MySQL tables)
louisl
on 20 Aug 2016
+1 Oracle DataBase
ericalves
on 3 Sep 2016
+1 (Postgre)
acabreragnz
on 5 Sep 2016
+1 (Oracle)
kennergrings
on 5 Sep 2016
@zbarbuto just clarifying, but this issue is specifically related to filtering on object type properties of the current model, _not_ filtering on properties on related models?
If so, should there be another issue created for filtering on related model properties? I have seen this issue referenced in a few stackoverflow questions asking about filtering on related models, so still not 100% sure.
jeff3yan
on 8 Sep 2016
@jeff3yan I believe this is the case. You are correct in that many questions etc. link to this issue, but in reading @daankets op it sounds like he is referring to nested object properties only: /someResource?filter[where][field.subField][like]=abc.
I have already created an issue for related model properties issue over on loopback datasource juggler:
https://github.com/strongloop/loopback-datasource-juggler/issues/1042
I haven't had time to setup the sandbox demo for the issue yet but will do soon hopefully (or anyone else feel free to drop over to that issue and post one)
zbarbuto
on 8 Sep 2016
👍
acrodrig
on 15 Sep 2016
+1 (Postgres)
tatianacmh
on 23 Sep 2016
+1 (Postgres)
ghost
on 23 Sep 2016
+1 (Postgresql)
EddSuarez
on 23 Sep 2016
:+1: for Postgres
rkmax
on 23 Sep 2016
I have taken a look on how to do the filtering based on the included model in mongodb.
The current mongodb connector results in a query like this:
db.model1.find({"model2.recordNo": 123})
but model2 object is not included so we get zero results.
So, We need to include the related model first.
I've found that using aggregate method along with $match instead of find enables including the related model using $lookup like this:
db.model1.aggregate(
[
{$lookup: {
from:"model2",
localField: "model2Id",
foreignField: "_id",
as: "model2"
}},
{$match: {"model2.recordNo": 123}}
])
where model1 is the main model and model2 is the related model
this should return a set of model1 filtered based on the related model model property recordNo
Well, next I will try to implement the above code to be used in the mongodb connector.
It will be used based on two conditions
- there is a related model included
- related model property is in the query
Please let me know your opinions on this, and if this is a good approach.
regards,
Mostafa
shalkam
on 12 Oct 2016
+1 (Postgresql)
xander-monk
on 17 Oct 2016
+1
bostondv
on 26 Oct 2016
+1
gvillenave
on 26 Oct 2016
Have there been any updates on this pull request? https://github.com/strongloop/loopback-connector/pull/40
jeff3yan
on 27 Oct 2016
strongloop/loopback-connector-mongodb/pull/301
So I went on my own and tried to make the dot notation work on non-embedded relations for mongodb.
And it's working for me now, I hope someone finds this helpful.
shalkam
on 29 Oct 2016
+1
juanfergaviria
on 25 Nov 2016
+1
zeriss
on 28 Nov 2016
+1
lobabob
on 13 Jan 2017
+1 (MySQL)
SkyStalker
on 13 Jan 2017
+1 (postgre) This is really important to have
innerexception
on 13 Jan 2017
+1 (MYSQL
JaredCowan
on 13 Jan 2017
+1 (postgres)
mradzinski
on 14 Jan 2017
+1 Mysql
totalard
on 15 Jan 2017
Please, stop posting +1 comments which are only spamming all people subscribed to this thread. Instead, click on the big yellow "thumbs up" button at the top of this issue to upvote it.
bajtos
on 16 Jan 2017
- [x] memory
- [x] mongodb
- [x] cloudant 3/5 test cases pass, need to fix https://gist.github.com/jannyHou/113cc88758f606d49c65760f6ffaeb1a
- use
elemMatchfor array elements: https://github.com/strongloop/loopback-connector-cloudant/issues/78 - use an equality operator as the basis of the query when
regexis used: https://github.com/strongloop/loopback-connector-cloudant/issues/77 - append :string or :number when sorting: https://github.com/strongloop/loopback-connector-cloudant/issues/75
- use
- [x] document - Add document to explain which connectors support nested query. https://github.com/strongloop/loopback.io/pull/292
~SQL(N/A)~
jannyHou
on 26 Jan 2017
Verification of the feature:
- [x] Memory
- [x] MongoDB
- [x] Cloudant
Verification done using a loopback app and connecting it to the three datasources mentioned above.
It worked with the three datasources.
Feature Verified
loay
on 27 Feb 2017
@jannyHou Why is SQL n/a? I feel like this should still be possible in Postgresql - but would require using the native JSON type for storage rather than strings as is done now.
zbarbuto
on 27 Feb 2017
@kjdelisle Closed? Does it mean I can now filter by 2 level properties using Postgres or MySQL?
kolach
on 28 Feb 2017
@zbarbuto @kolach There's a more fundamental change coming in loopback 4 which might be able to support 2nd level filter across all connectors.
cc @raymondfeng
Can you elaborate more about our plan on supporting nested filter in sql connectors? Thanks :)
jannyHou
on 28 Feb 2017
@zbarbuto Don't you think that this issue should be left open until you guys implement that filtering?
kolach
on 28 Feb 2017
My bad; I closed this via ZenHub. I thought it was the internal issue we were using to track the work across our pipeline. Reopening this until it hits npmjs.
kjdelisle
on 28 Feb 2017
Thank you @kjdelisle
kolach
on 28 Feb 2017
and when may LB4 be coming ?
jmls
on 1 Mar 2017
@kjdelisle can you post a link to the documentation page that explains how this feature is now implemented?
kolach
on 3 Apr 2017
cc @jannyHou
kjdelisle
on 3 Apr 2017
👍
rigobcastro
on 5 May 2017
+1
safwen1
on 7 May 2017
Guys, please stop posting +1 comments. You're waking up everybody. Use this icon in the top-right corner of the comment or original post you are consent with.
christiaanwesterbeek
on 7 May 2017
+1 for the suggestion or please use the unsubscribe button
El dom., 7 de may. de 2017 1:47 PM, Christiaan Westerbeek <
[email protected]> escribió:
Guys, please stop posting +1 comments. You're waking up everybody. Use
this icon in the top-right corner of the comment or original post you are
consent with.—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/strongloop/loopback/issues/517#issuecomment-299726158,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AA9bk7iBW_2rK5uj-bgzWJ2pC-8VZqQnks5r3hGmgaJpZM4Ccngj
.
rigobcastro
on 7 May 2017
Please in the next version of loopback-next, loopback-datasource-juggler use alias in the generated queries, for example buildWhere, buildSelect, buildColumnNames don't use alias, so if i want to take advantage of this method to build my custom find which includes other table joins , i have to manually process all generated sql and add the alias field by field! ALIAS in the new juggler is a must!! And support to add table joins to the find methods will be nice too :) an extra parameter joins something like joins:[{modelJoin:team,as:'teams',modelForeignKey:'user_id',associationKey:'id',joinWhere:[{teams.name:{inq:['admin','level 2'}].... that and a "having" condition to append to the end of parameterizedSQL :), I know is a lot, haha. loopback-next typescript + new juggler + fireloop = The perfect framework
tellex
on 11 Jul 2017
+1
mgmohitgawande
on 20 Jul 2017
Please in the next version of loopback-next, loopback-datasource-juggler use alias in the generated queries, for example buildWhere, buildSelect, buildColumnNames don't use alias, so if i want to take advantage of this method to build my custom find which includes other table joins , i have to manually process all generated sql and add the alias field by field! ALIAS in the new juggler is a must!! And support to add table joins to the find methods will be nice too :) an extra parameter joins something like joins:[{modelJoin:team,as:'teams',modelForeignKey:'user_id',associationKey:'id',joinWhere:[{teams.name:{inq:['admin','level 2'}].... that and a "having" condition to append to the end of parameterizedSQL :), I know is a lot, haha. loopback-next typescript + new juggler + fireloop = The perfect framework
@tellex If you could formalize this into a new issue on loopback-next that would definitely get more eyes on your suggestion. :)
kjdelisle
on 21 Aug 2017
+1
rNexeR
on 23 Aug 2017
[email protected] will turn to LTS soon, and we are sorry to say that we don't have plan or say bandwidth to implement the filter with related properties, or the filter applied on the nested model properties with SQL connectors.
In [email protected], our NoSQL connectors for Mongodb, Cloudant and [email protected] support query with nested model properties(not related properties), and everything regarding data persistence is open for discussion in https://github.com/strongloop/loopback-next.
I am closing this issue.
jannyHou
on 5 Sep 2017
it`s very hard to write a non trivial application with good perfomance and clear code without this functionality. I think this should be seriously reconsidered if you plan to turn loopback in a robust framework for more complex applications.
vanderdill
on 14 Sep 2017
@vanderdill This was the reason I posted about #3407 and created the objection-filter module.
The main disadvantage that I've found with the Loopback ORM is that the REST API filter is identical to the model filter.
It would be more useful to have more powerful queries directly on the model (joins etc.), and have a subset of those features exposed on the API.
jeff3yan
on 15 Sep 2017
I found a workaround for my issue. Posting it here for the community.
Some context : MySQL; I have a Company table and a Job table, each containing a published field. When I list all the jobs, I don't want published jobs from not-published companies to be returned.
I created a VIEW job_full that aggregates the whole row and the level 2 field I want to check :
CREATE VIEW job_full AS
SELECT job.*, company.published AS company_published
FROM job, company
WHERE company.id=job.company_id;
And linked the Job model to the fob_full table in the LB model definition.
That way, I can filter on company_published (which is done with an operation hook in my case).
create and updatework out of the box, as long as I don't pass a company_published property.
I had to override the destroy methods, though :
Job.on('dataSourceAttached', function() {
const JobRaw = app.models.JobRaw;
Job.destroyById = function(...args) {
return JobRaw.destroyById(...args);
};
Job.destroyAll = function(...args) {
return JobRaw.destroyAll(...args);
};
});
(where JobRaw is the LB model definition linked to the real job table)
It's hacky, and performance is likely to be crap, but it helps by the time you figure this issue out ;-)
xurei
on 6 Oct 2017
Hey i finish the first alpha version of a model method extension for postgresql >= 9.2, it allow any deep of filter includes with features like: relation aliases, join types, new where condition syntax including the option for using functions in the where statement, a "having" condition for hasMany or hasManyThrough, orderBy option. It generates one big sql query using new features of postgres like json_build_object(), json_agg() and optimized new "LATERAL" join for join subqueries, thanks to this new postgres features the data serialization is done in the database returning the results with the corresponding relations structure, i tested it with the classic movie rental postgres database, i need help to test it in bigger databases.
Code and instructions:
https://github.com/tellex/findComplex
tellex
on 30 Oct 2017
Looks like a good start @tellex .
I think it needs to utilize some of the existing loopback-connector to avoid SQL injection (https://github.com/tellex/findComplex/blob/master/findComplex.js#L647 for example should escape the column names, see: https://github.com/strongloop/loopback-connector/blob/master/lib/sql.js#L456).
An alternative might be to leverage something like knex to build the queries (or parts of them) to get around this.
zbarbuto
on 30 Oct 2017
Congragulations for the initiative @tellex. i'll use it as soon as possible. It seems very nice.
vanderdill
on 30 Oct 2017
@zbarbuto
If you look closely the code, i check if the name of the property exists in the model definition, since loopback properties naming convention i don't see how it can be possible to sql inject, column names can't be parameterized, the same goes for aliases can't be parameterized, i check alias names against a regex, the same regex and rules that are needed for javascript function names. When i encounter a value i parameterize it with the same library that loopback uses var ParameterizedSQL = require('loopback-connector/lib/parameterized-sql');
Then i use the same libraries that loopback uses for sql queries.
sql = this.datasource.connector.parameterize(mainSql);
this.datasource.connector.execute(sql.sql,sql.params,function(error,data){
cb(error,data);
});
I started using knex, but when i needed to define sql join subqueries knex have no support...
Knex is nice.. but is more for not so complex queries, keeps the code clean. I trashed 2 weeks of work because of knex. The code is hard to read, when i have more time i will comment it, some parts are complex because there is no other way than using recursive functions to generates the sql backwards.
tellex
on 31 Oct 2017
Fair enough, sounds good. Really looking forward to testing it out.
zbarbuto
on 31 Oct 2017
FWIW, some support for querying nested properties was added by https://github.com/strongloop/loopback-datasource-juggler/pull/1239, plus https://github.com/strongloop/loopback-datasource-juggler/pull/1515 is going to fix coercion of nested values.
bajtos
on 14 Dec 2017
@tellex: After fixing two small bugs it worked very well! I think it is a very good starting point for those using postgres. Thank you for the initiative.
Since I had to copy the files of your repo to different places it's not so easy to provide you the fixes with a pull request…
joschne
on 18 Dec 2017
in the doc i can read: Loopback supports filtering nested properties in three NoSQL connectors: Mongodb, Cloudant, Memory.
so a query like this it's impossible on mysql
User.find({where: {'address.tags.tag': 'business'}}
i can't see any valid workarount to achieve multiple join with where condition... that will require multiple queries leading performance issues (and resource waste)
any suggestion?
kurtommy
on 30 Dec 2017
I am lost at the moment. This ticket is closed, but does filtering on nested properties work with mongodb?
F3L1X79
on 2 Feb 2018
@F3L1X79 It works as long as the nested properties are embedded on the same document.
nVitius
on 2 Feb 2018
This does not work for MySQL. It should be possible to translate something like:
User.find({where: {'address.tags.tag': 'business'}}
Into JSON_EXTRACT conditions instead of simple SQL equality.
Can someone point me in the direction of the code where the translation from JSON to SQL is done? Thanks.
acrodrig
on 2 Feb 2018
@acrodrig I think most of the code you're looking for will be here: https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js.
nVitius
on 2 Feb 2018
Also for anyone interested - there is support for this to some degree in postrgres using the json data type as per this PR
https://github.com/strongloop/loopback-connector-postgresql/pull/304
zbarbuto
on 3 Feb 2018
Why the filter with property id does not work? If I change it to any other property name it works (ie: customer.name). I'm using mongodb connector.
// Does not work
var filter = {
where: {
'customer.id': customerId
},
order: 'order_number DESC'
}
// Works
var filter = {
where: {
'customer.name': customerName
},
order: 'order_number DESC'
}
Order.find(filter, function (err, orders) {..})
elviocb
on 3 Mar 2018
Hey @elviocb , this must be problem with customer.id stored as String and not ObjectId. Can you share how your Order model looks. It should be related to https://loopback.io/doc/ja/lb3/MongoDB-connector.html#strictobjectidcoercion-flag
princecharmx
on 3 Mar 2018
hey @princecharmx .. thanks for your reply!
That's my order model definition:
{
"name": "order",
"base": "PersistedModel",
"idInjection": true,
"options": {
"validateUpsert": true
},
"properties": {
"status": {
"type": "string",
"required": true,
"default": "open"
},
"customer": {
"type": "object"
},
"customer_id": {
"type": "string"
},
"discounts": {
"type": [
"object"
]
},
"discount_codes": {
"type": [
"string"
]
},
"comissions": {
"type": [
"object"
]
},
"email": {
"type": "string",
"required": true
},
"cart": {
"type": "object"
},
"financial_status": {
"type": "string",
"required": true,
"default": "pending"
},
"tags": {
"type": "string"
},
"number": {
"type": "number",
"required": true,
"default": 1000
},
"order_number": {
"type": "number",
"required": true
},
"name": {
"type": "string",
"required": true
},
"subtotal_price": {
"type": "string",
"required": true
},
"total_discounts": {
"type": "string",
"required": true
},
"total_price": {
"type": "string",
"required": true
},
"total_tax": {
"type": "string",
"required": true
},
"total_comissions": {
"type": "string",
"required": true
},
"tax_lines": {
"type": [
"object"
]
},
"cancel_reason": {
"type": "any"
},
"canceled_at": {
"type": "date"
}
},
"validations": [],
"relations": {
"shop": {
"type": "belongsTo",
"model": "shop",
"foreignKey": "shop_id"
},
"customers": {
"type": "hasOne",
"model": "customer",
"foreignKey": "customer_id"
}
},
"acls": [],
"methods": {}
}
And this is how an order model is stored:
{
"_id" : ObjectId("5a9a9b3b4fd628739efb4972"),
"status" : "open",
"customer" : {
"name" : "Laila ",
"lastname" : "Gonçalves",
"email" : "[email protected]",
"gender" : "female",
"age" : "23",
"cpf" : "24898932100",
"id" : "5a9352c29fe55f9a981661a8",
"partner_id" : "5a80d66992856a1c701ab2c2"
},
"customer_id" : "5a9352c29fe55f9a981661a8",
"discounts" : [],
"discount_codes" : [],
"email" : "[email protected]",
"cart" : {
"items" : [
{
"id" : 994925047,
"product_id" : "5a77690bd3715f5127e10cff",
"title" : "Feijoada monster",
"price" : "14.90",
"quantity" : 1,
"image" : "https://dummyimage.com/400x400/000000/ffffff.jpg"
},
{
"id" : 518365228,
"product_id" : "5a77694ad3715f5127e10d00",
"title" : "Escondidinho Power",
"price" : "14.90",
"quantity" : 1,
"image" : "https://dummyimage.com/400x400/cc14cc/ffffff.jpg"
}
],
"subtotal_price" : "29.80",
"total_price" : "29.80",
"formatted_price" : "R$29.80",
"item_count" : 2,
"total_discount" : 0,
"discounts" : [],
"tax_lines" : [],
"total_taxes" : 0,
"allow_payment_method" : true,
"allow_discount_override" : false,
"payment_method" : {
"id" : "003",
"display_name" : "Dinheiro",
"name" : "money",
"short" : "d"
},
"customer" : {
"name" : "Laila ",
"lastname" : "Gonçalves",
"email" : "[email protected]",
"gender" : "female",
"age" : "23",
"cpf" : "24898932100",
"id" : "5a9352c29fe55f9a981661a8",
"partner_id" : "5a80d66992856a1c701ab2c2"
}
},
"financial_status" : "pending",
"tags" : "",
"number" : 1000,
"order_number" : 1002,
"name" : "#1002",
"subtotal_price" : "29.80",
"total_discounts" : "0.00",
"total_price" : "29.80",
"total_tax" : "0.00",
"total_comissions" : "5.60",
"tax_lines" : [],
"cancel_reason" : null,
"canceled_at" : null,
"shop_id" : ObjectId("5a8ddeba4f11315cb6076cc3"),
"comission_ids" : [
"5a9a9b3b4fd628739efb4973"
],
"id" : ObjectId("5a9a9b3b4fd628739efb4972")
}
You have incorrect model. You are having property customer and also have a relation customers with hasOne.
You should use either.
Also, as pointed note, your customer.id is stored as string and hence you are having problem.
Add strictObjectIDCoercion to true in above model.
"options": {
"validateUpsert": true,
"strictObjectIDCoercion": true
}
Fyi, your usage is incorrect at many places. First you confirm if my suggestion works. I will then guide you next steps.
princecharmx
on 3 Mar 2018
Hey @princecharmx .. It works. Thanks!.. Could you guide me which other parts of the model are incorrect please?
elviocb
on 4 Mar 2018
I can't see valid solution for MYSQL inner join yet, has anyone found at least a workaround?
kurtommy
on 16 May 2018
I've made a fork of loopback-connector-mysql to support joins. It's not an ideal solution but it works, I'm using it in production in my applications. It only supports read operations.
This is a filter working over the User built-in model, to find all users with an ADMIN name role (the relation between User and Role is needed):
{"where": {"roles": {"name": "ADMIN" }}}
You can check it out https://github.com/jolivervidal/loopback-connector-mysql
jolivervidal
on 17 May 2018
@jolivervidal just tested and that work perfectly !!! you save me a lot of time, why this is not included in the standard library? i'll check what you have done in your fork!
kurtommy
on 17 May 2018
Right now we have a workaround solution for this case ? :( for mongoDB
bellgetlinks
on 18 Jun 2018
@bellgetlinks For mongodb I've ended up opening a connection to mongodb in a boot script and running direct queries and filters on related models using .aggregate and $lookup through remote methods. Essentially sidestepping loopback.
I found this stack overflow post helpful for including related documents in queries.
This is the boot script I used to connect to mongodb and expose it as mongoDB for use in the models:
```'use strict';
const MongoClient = require('mongodb').MongoClient;
// Connection URL
const url = '< mongodb connection URL>';
// Database Name
const dbName = '< db name >';
module.exports = function connectToMongoDB(server) {
// Use connect method to connect to the server
MongoClient.connect(url).then(client => {
console.log("Connected successfully to server");
const db = client.db(dbName);
server.mongoDB = db
})
};
jackrvaughan
on 24 Aug 2018
I've made a fork of loopback-connector-mysql to support joins. It's not an ideal solution but it works, I'm using it in production in my applications. It only supports read operations.
This is a filter working over the User built-in model, to find all users with an ADMIN name role (the relation between User and Role is needed):
{"where": {"roles": {"name": "ADMIN" }}}You can check it out https://github.com/jolivervidal/loopback-connector-mysql
@jolivervidal
Thanks dude, it works
I hope it won't create new errors on another things though
how about creating a PR on the official module about this feature you just made ? 😃
ranggarifqi
on 1 Oct 2018
What's the status of this issue? Seems a very important feature is missing. @jolivervidal your fork seems working very well, why not merging with the master branch?
michelgokan
on 3 Mar 2019
@michelgokan as you'll see from the thread:
- There is some support in postgres connector
- Mongo also has some basic support (only for dot nested properties)
- Further support isn't going to be added to other RDB connectors as loopback 3 is in LTS and no longer accepting new feature PRs
- Loopback 3 ends LTS at the end of 2019 so it's even less likely to be added.
Options are:
- Write the custom SQL yourself as a custom endpoint
- Create an alternate connector for something like Knex or Bookshelf that has support (big job)
- Do the filtering in memory
- Use json for the nested data to use dot queries in something like mongo or postgres
- Look at migrating to loopback next which may add support in the future
zbarbuto
on 3 Mar 2019
Thanks, @zbarbuto but none of your mentioned alternatives works, at least
for me at the moment:
- I can’t write custom SQL scripts/custom endpoints as it ruins the whole
architecture of my application (but maybe the most usable solution at the
moment!!!!!) - For the same reason, it's not possible to use Bookshelf or Knex
- Then we should basically ignore the whole idea behind using relational
DBs: to be able to perform complex queries!! - Same as 3
- This feature is not working even in Loopback 4, so can't see a point for
migration.
I didn't find a problem in @jolivervidal fork at the moment. I hope nothing
bad happens 😬
On Sun, 3 Mar 2019 at 23:43, zbarbuto notifications@github.com wrote:
@michelgokan https://github.com/michelgokan as you'll see from the
thread:
- There is some support in postgres connector
https://github.com/strongloop/loopback/issues/517#issuecomment-362767424- Mongo also has some basic support (only for dot nested properties)
- Further support isn't going to be added to other RDB connectors as
loopback 3 is in LTS and no longer accepting new feature PRs- Loopback 3 ends LTS at the end of 2019 so it's even less likely to
be added.Options are:
- Write the custom SQL yourself as a custom endpoint
- Create an alternate connector for something like Knex
https://knexjs.org/ or Bookshelf https://bookshelfjs.org/ that has
support (big job)- Do the filtering in memory
- Use json for the nested data to use dot queries in something like
mongo or postgres- Look at migrating to loopback next which may add support in the
future—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/strongloop/loopback/issues/517#issuecomment-469072881,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AARH6W9n5vY5vqmX9fyotI6573Hl__nfks5vTFArgaJpZM4Ccngj
.
michelgokan
on 4 Mar 2019
Does anyone know if the loopback datasource is compatible with the knex connector? It'd be great to use the same connections for both, to migrate to knex if possible. Then wrap around something like objection.js for filtering on nested relations.
jeff3yan
on 4 Mar 2019
Does anyone know if the loopback datasource is compatible with the knex connector?
@jeff3yan You'd have to write your own loopback-knex-connector. I've tried a few times but it's a big job and never really got anywhere with it. Having said that it would be an amazingly useful took if someone did manage to crack it. Particularly since join style queries (include) are pretty inefficient in loopback.
@michelgokan if that fork is working for you with MySQL then sounds like a good solution. I agree it's an unfortunate situation for users of other RDBs (like Postgres) but it's just the way loopback works - it does not assume your models are in any particular datasource so it does not leverage the full power of the underlying datasource (you _may_ be querying two models in postgres or one might be in postgres and one might be in Mongo - it makes no assumptions)
zbarbuto
on 4 Mar 2019
@tellex solution works very well for PostgreSQL. Thank you so much!
I've tested many scenarios, with where conditions and order/limit situations and it is exactly what I needed.
Code and instructions:
https://github.com/tellex/findComplex
vanderdill
on 3 Mar 2020
alexdee2007
on 21 Mar 2020
For those who are using PostgreSQL I'm using @tellex solution, and it's working greate on a large project.
I've done some bug fixes and I'm keeping this fork updated with it:
https://github.com/vanderdill/findComplex
vanderdill
on 7 May 2020
Related issues
rkmax
·
3Comments
bryannaegele
·
4Comments
AnupDeshpande23
·
4Comments
daankets
·
4Comments
devotox
·
4Comments
Most helpful comment
it`s very hard to write a non trivial application with good perfomance and clear code without this functionality. I think this should be seriously reconsidered if you plan to turn loopback in a robust framework for more complex applications.