Support Topics Documentation Slack YouTube Blog

iOS SDK Where Clause


(Renato N) #1

Hi all,

I keep getting this error that I haven’t seen before, and was wondering if you could shed some light (maybe I’m approaching this wrong).

3 tables are involved in this scenario:
Event --| (Optional 1:1) to Business_Location(via Location column)
Business --< (Optional 1:N) to Business_Location(via Locations column)

I want to retrieve all the events whose location’s business (if specified) is “Active”.

With the iOS SDK, I have this where clause specified on the query for the Event:







p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #e44347}
span.s1 {font-variant-ligatures: no-common-ligatures}



(Location is null or (Location.Business[Locations].Active = true)) and ...

but I keep getting this:

Invalid where clause at position 39: no viable alternative at input \'(Location.Business[\'

I have other queries in my app that use this pattern (the lookup on the parent), and they seem to work fine (They just don’t have the scenario of the three levels / tables).

If I am indeed doing this wrong, how do I check a flag against a table’s (event) child’s (Location) parent (Business)?

Much appreciated,
Renato


(Vladimir Upirov) #2

Hi Renato

Please provide your AppId

Regards, Vlad


(Renato N) #3

Hi Vlad,

App ID is A324EB2B-1BF5-EB0A-FF36-62F47D198E00.

Renato


(Sergey Chupov) #4

Hi Renato,

You got the right idea with the inverse relation, but unfortunately our whereClause parser is not smart enough yet to handle the inverse relation inside a relation. :slight_smile:
But the good news is that you may accomplish the same with the following whereClause:

Location IS NULL OR Location.objectId IN (Business[Active=true].Locations.objectId)

This is a new “subquery” syntax introduced in 4.2.0, so it’s not documented yet. Effectively what is does is searches Business objects for which “Active = true” is true, retrieves their “Locations.objectId” properties and puts into the IN clause.


(Renato N) #5

Hi Sergey,

I did notice that in your release notes, and was wondering if that could be of use here!

Thanks for the joining syntax for my use case - just checked it out, and it works great (also solved another similar use case I had)

Impeccable timing, by the way! :wink:

Thank you!
Renato


(Marcio Prudencio) #6

Hi everyone, I have a similar issue using SDK FOR JAVASCRIPT, where I have 2 tables: Products and categories. I create a relation 1:1 in Products (categoryId) which describes which category belongs the product, now I have to retrieve all products from categoryId XYZ with the relation of my categories table.

I’m using:
Backendless.Data.of(“Products”).find({ categoryId: ${categoryId}, loadRelations: “categoryId,userId.userId” })

also

var whereClause = categoryId.objectId = ${categoryId};
var queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause( whereClause );
Backendless.Data.of( “Specials” ).find( queryBuilder )

And getting Error: The first argument should be instance of Backendless.DataQueryBuilder

The objectId is mandatory? How can I apply in this situation?
Regards;


(Sergey Chupov) #7

Hi Marcio,
Since you pass a String argument first (categoryId), you’re effectively calling a findById API and the SDK thinks you’re passing the objectId of the Product (since you’re calling it from .of( "Products" )).
Instead you should use a general find API, here’s a relevant documentation which also shows how to form the query object with BackendlessDataQueryBuilder with examples: https://backendless.com/docs/js/data_search_with_where_clause.html


(Marcio Prudencio) #8

Hi Sergey, I believe I didn’t get it… Do you know where I can get an example of multiples queries using CODELESS?

  1. Products table = list of products with column categoryId (relation 1:1 to categories table). How can I bring all products from a specific categoryId?

Regards;


(Sergey Kukurudzyak) #9

I have added codeless example to your app, please check service TestService there is method findSpecialsByCategoryId: