How to make a "IN" SQL query?

Data Service
1

I have a ‘parent’ relationship and an array of objectId in an iPhone app.

How do I make the query for getting all tasks where this parent is in that array?

I have tried

parent.objectId IN %@
parent.objectId IN (%@)

parent.objectId = %@

but none works. The first two gives fault, the last one is just empty as I guess the ‘equals’ doesn’t work with an array.

What is the correct syntax?

backend-data.jpg
backend-data.jpg1559×492 298 KB

loading-data.jpg
loading-data.jpg1324×886 712 KB

2

Hi Jonas,

Please see the Find Operations section at the bottom of this page:
http://backendless.com/documentation/data/js/data_related_data.htm

The general query syntax is:

Assume the parent class is Person which has a relationship called “addresses” containing a collection of Address objects. Then once you know objectId or Person, you can load all related Address objects using the following query:

Person[addresses].objectId=‘personObjectId’

Alternatively, you can use the loadRelations method to get a collection of related objects for a particular parent.

Does it help?

Regards,
Mark

3

I ended up using related to get the data. I must make the request in two queries for some reason though.

I want to make a whereCaluse = @“updated IS NULL AND created > ‘19700101’ OR updated > ‘19700101’” but this is not possible it seems. Especially not with related added…

4

Could you please describe the schema you have (with all relations) and the kind of query you would like to run (or what you would like to load with a single query)?

I’d like to be able to duplicate the problem on our side.

Regards,
Mark

5

I have a Todos parent object which has a relationship to Tasks (one-to-many). The tasks have themselves a relationship to Images (one-to-one).

I’d like to make a query where I make a query for all data that has been created or updated after a set date.

I have tried setting whereClause to ‘updated > 20140222 OR created > 20140222’
which seems to return all objects, no matter what updated or created actually is.

I have also added the relations “tasks” and “images” (maybe I can’t have both when querying from Todos?) but even if I add just “tasks” that property is null. When making the query for a specific objectId it works though. Not sure what’s up here…

6

Do you use REST interface or a some backendless SDK?

7

I am making the calls with the iOS SDK.

    BackendlessDataQuery *query = [BackendlessDataQuery query];
    query.whereClause = [NSString stringWithFormat:@"updated > 20140222 OR created > 20140222"];
    [query.queryOptions addRelated:@"tasks"];
    //[query.queryOptions addRelated:@"images"];
    
    [backendless.persistenceService find:[Todos class] dataQuery:query response:^(BackendlessCollection * collection)
    {
8

Hi Jonas,

I have recreated the table structure and put together some sample code (I used Java as I am not that proficient with obj-c). This should not make any difference as both Android and iOS SDK make the same calls to the backend. Please take a look at the attached images, they show the data on the backend as well as a screenshot of the debugger which shows the data which comes back.

The code which makes the query is:

        BackendlessDataQuery query = new BackendlessDataQuery();
        query.setWhereClause( "created > 20140420000000 and updated is not null" );


        QueryOptions queryOptions = new QueryOptions();
        queryOptions.addRelated( "tasks" );
        queryOptions.addRelated( "tasks.image" );
        query.setQueryOptions( queryOptions );


        BackendlessCollection<Todos> todos = Backendless.Data.of( Todos.class ).find( query );
        System.out.println( "Received " + todos.getData().size() + " objects" );

The important things to note are:

    Date format for created/updated or other Date columns is: YYYYMMDDHHMMSS You can reference as many columns as you need to. In this example I have 2 relations: "tasks" - which is a one-to-many relation between Todos and Task tables. "image" - which is a one-to-one relation between Task and Image tables. When referencing "image", you need to prefix it with the parent relation, as a result, you get "tasks.image".
Hope this helps.

Mark

loading-data.jpg
loading-data.jpg1324×886 712 KB

9

Yes, helped a lot.

First with the date format, and second I thought a relation worked both ways as it showed up in the browser but I guess that’s just for convenience.

I think I have to modify my code some anyway, since I don’t want to update “everything”, just changes. So I will basically destroy my relationship if I update the “tasks” relationship on Todos with just the updated ones. But if I put it on Tasks and try to pull all the results I would get the whole Todos structure back multiple times which seems like a waste. So I guess pulling just the updated Todos first and then the Tasks required.

Anyways, thanks a lot! Excellent support service!