Query Where One to Many with AND vs OR

Data Service
1

Hello all,

I am trying to do a query where my parent object is related to a list of child items and I am looking to select all items that has all the items I am looking for, vs any of them.

I see that the where clause supports IN list

https://backendless.com/docs/js/data_search_with_where_clause.html

and the documentation clearly shows that this is using an OR operator between each item, and this is cool and all however what I want is the following

give me all carts that contains items with apples AND oranges

right now I can only do the following with the IN command

give me all carts that contains items with apples OR oranges

I do see that logically the IN is being compared on the child item level (apple is apple or orange), thus why my attempt to create an AND query by hand returns no matches (apple is apple and orange), so the actual comparer I am looking for is on the parent collection where I want to compare all children and only match when the number of matched children matches the number of comparers I am looking for (# of matched children = # of list items).

Is this possible? One way that I may do this in SQL would be to do a query for each (all carts with apple, all carts with oranges) then return carts from the first query that also exist IN the subsequent query. My gut tells me that this may work, but there is likely a much cleaner way via aggregates, groups and the having clause, however I am coming up short.

Have I over complicated the issue or am I missing something obvious? Any advice would be great, I have a very large collection of objects and I would like to get subsets that contain one or more related items.

Thanks a ton.

2

Hi Jason,

Do you mind attaching a screenshot of the schema from the Visual Modeler tab of Backendless Console? This would help put the question in perspective.

Kind regards,
Mark

3

First, very nice to meet you Mark, great platform and you have built something very special. Congrats. Also thanks for the t-shirt, very cool.

Thank you for taking the time to help me address my current challenge

Happy to

model

Surveys are my parent, and they contain a list of SurveyTags that have a One to One relationship to Tags. Tags is a unique list of valid tags and is where I am trying to match on

tags.tag.name IN (‘Storefronts’,‘Curtain Wall’)
or
tags.tag.name = ‘Storefronts’ or tags.tag.name = ‘Curtain Wall’

as expected return the same results, all Surveys that have at least one match, but what I am trying to do is get all Surveys that contains both tags, more like

tags CONTAINS ALL tag.name IN (‘Storefronts’,‘Curtain Wall’)
or
(tags.tag.name IN (‘Storefronts’,‘Curtain Wall’)).Count == 2

where the number of matched tags from the IN itself is where I need to compare.

here is a query that I tried, with no luck

where=tagCount = 2
property=Count(“tags”) as tagCount
loadRelations=tags,tags.tag
groupBy=“tags”
having=tags.tag.name IN (‘Storefronts’,‘Curtain Wall’)

however it is an “Invalid use of group function”