I have an object that has a to-many relation called ‘blacklist’.
This column has 2 objects referenced with the following object id’s
10CFAD99-4B7C-E6D0-FFC6-2C5E19779F00 and 216BB46C-5BD7-9DCA-FF3C-A8EF613DFD00
if I have a where clause like this
blacklist.objectId in (‘10CFAD99-4B7C-E6D0-FFC6-2C5E19779F00’)
it returns the object
if I have a where clause like this
blacklist.objectId in (‘216BB46C-5BD7-9DCA-FF3C-A8EF613DFD00’)
it returns the same object
however if I have a where clause like this
blacklist.objectId in (‘10CFAD99-4B7C-E6D0-FFC6-2C5E19779F00’) and blacklist.objectId in (‘216BB46C-5BD7-9DCA-FF3C-A8EF613DFD00’)
I get no results. how can that be, both conditions are met so the row should be returned.
What I am trying to do is only return rows that have ALL of the objects that I specify in the relation.
Am I doing this wrong, is there a better way?
blacklist.objectId in ('10CFAD99-4B7C-E6D0-FFC6-2C5E19779F00', '216BB46C-5BD7-9DCA-FF3C-A8EF613DFD00')
The problem with the syntax you used was that it was matching the same object id against two different values and with the ‘and’ between them, it would always come out with an empty result.
Sorry, I am struggling with understanding what you’d like to get. A single objectId cannot have two different values. Could you perhaps rephrase the question in plain terms? (without mentioning database terminology…))
The scenario is this… a user is associated with multiple genres. I want to return a set of users who have BOTH Jazz and Funk associated with them.
So I added a column to User with a data relation to-many genres
I have a user that has both Jazz and Funk in that relation
I want a query that returns this user because he likes both Genres
but does not return users who only like Jazz
Your solution would return both users as they both like Jazz, but thats not enough, I need to only return the ones who like both.
With the way you currently have it structured, I could not think of a single query that would fetch all the users for a given set of Genres. A multi-query approach would work, but that means more custom code to generate the desired collection.
It would be possible to accomplish what you want is by changing the direction of the relation. If you were to declare a one-to-many relation in the Genres table pointing to Users, then you can get a collection of the users with the following (somewhat twisted) query:
Genres[users].objectId in (‘genre1-objectId’,‘genre2-objectId’,‘genre3-objectId’)
The query assumes the following:
The name of the table where you keep the genres is "Genres"
The 1:N relationship defined in the Genres table is through a column named "users" and it points to the Users table
The query must be sent to the Users table
The quirkiness of the query is in the "Genres[users].objectId" syntax. It is not the "standard SQL-92", of course, and should be interpreted as follows:
get all the objects identified by the "Genres[users]" part. This is going to be a subset of all the user objects which are referenced by all the Genre objects.
for each user object from (1), apply the following rule: "objectId in ('x','y', 'z')". Where objectId is actually a property from the Genres table.
As a result, you get all the users which have specific genres.
I know this is quite confusing - it makes me think every time I run into this scenario, hence is the delay with response )). Please feel free to ask any questions on this topic. I need to figure out a way to explain this to users in better terms. We cover it in the docs (see the “Loading a Subset of Related Child Objects” section here: https://backendless.com/documentation/data/rest/data_relations_retrieve.htm but there is always room for improvement)
Thank you for the help. I will give that a try. Since the genre table is used and associated with other objects for similar purposes, so will make the Genre table look overly complex.
My feeling is that I would suggest that due to the complex answer and the difficulty in explaining this indicates that this is not the right solution.
I would suggest implementing something like an ‘has’ operator in addition to the in operator. That could be used like
blacklist.objectId has ('10CFAD99-4B7C-E6D0-FFC6-2C5E19779F00', '216BB46C-5BD7-9DCA-FF3C-A8EF613DFD00')
This would return the object that have all the associations and work in the same way as in operator
Would that be possible, as it feels like you have only a partial implementation of the ‘to-many’ relation currently and no support for full joins that would enable this to be done in standard SQL.
Ian, you’re right… I am sorry, I should’ve looked closer when I was trying it out. We have SQL “aggregation” functions on our roadmap. This is something that will definitely make it easier. Until then, I’d recommend creating a custom hosted service that would hide the complexity from the client side.
Thanks but even with a hosted service I think that this is not possible without that “aggregation” function. for every user in the system I would have to run a query to fetch all the Genres for them, and see if that included the set I was looking for.
That is not going to scale
Where in your priority list is the “aggregation” function? I think I will have to wait for that. I need to continue my migration from Parse, and I can’t really progress without this, so I am just wondering if I should wait?
You’d need to get all the users that have the genres in question and then perform a “union” grouping to narrow down to the final collection. How do you do it with Parse? What does the query look like?
OK, so perhaps I can do the same thing that I do in Parse actually.
What I do is do a query for all users then iterate though them manually checking to see if the user has ALL of the associated Genres. Not really efficient but actually will do for now, was kinda hoping I could just do this in one query to simplfy the code. I will try that for now.
Thank you for your continued assistance and patience , it is much appreciated
