WHERE clauses in Data Service queries in Android

General
1

At this link
http://backendless.com/documentation/data/android/data_relations_retrieve.htm

down the page an example of using the “Where” clause in a BackendlessDataQuery:
Find all contacts in a city for a specific phone book

The example is a simple phone book where the search is for “Contacts”. The “Contact” class does not seem to include a member “PhoneBook[contacts]” that is referenced in the “Where” clause being built. Indeed, “Contact” is a child record of the “contacts” list member and the “owner” member of the “PhoneBook” record.

Two questions arise:

  1. Can one indeed specify parent records in a “where” clause as may be happening here? E.g. presumably “PhoneBook.contacts” (PhoneBook[contacts]?) is a list member that can include the ID of a one or more “Contact” records, and “PhoneBook.owner” is a scalar member that can be the ID of a “Contact” record.

  2. If we suppose the only “Contact” member of the “PhoneBook” record is “PhoneBook.owner”, how can one specify that one wants only the “Contact” records that are NOT owners of a “PhoneBook” record? For a test example with some other records analogous to this, it appears the equivalent of neither of these work:
    whereClause.append(“PhoneBook.owner IS null”)
    whereClause.append(“PhoneBook IS null”)

Is it impossible to accomplish this with a single query? If it is possible to do this, can someone tell us how?

Thanks.

2

Hi, Kevin!

  1. Yes, that’s true, common form of where clause is
ParentTableName[ relatedPropertyForChildrenCollection ].parentColumnName COLUMN-VALUE-CONDITION
  1. You cannot reference an object - only a particular property of it. Member “owner” has class “Contact”, which has a required not-null property “objectId”. This property can be used to find out if the object exists. So, the where clause that would return all objects of “Contacts” class, which are not “owners” would look like the following:
whereClause.append( "PhoneBook[owner].objectId IS NULL" );

with best regards,
Alex Navara

3

Thanks, this is very helpful.

Two follow-on questions if I may:

  1. In the response to the second question above, if this request:
    a) was made server-side in a Custom Event handler by an Authenticated User,
    b) the “HeadersManager” class was properly updated with the “userToken” from the request,
    c) the objects in the “Contact” class are not owned by any user,
    d) but the objects in the “PhoneBook” class are owned by users,
    would the result only be those objects of the “Contacts” class which are not owned by the AuthenticatedUser as one might expect?

  2. Does this construction for parent objects only work in the “Where” clause of a query, but not the “QueryOptions” of the query?

Thanks.

4

Hi, Kevin!

  1. If I understood you correct, then in response you’ll still get “Contacts” which are referenced by “PhoneBook” without owner. If you want to get only “Contacts” which are referenced by “PhoneBook” where owner is “AuthenticatedUser”, the clause should be the following:
 "PhoneBook[owner].ownerId = '" + 
Backendless.UserService.CurrentUser().getObjectId() + "'"

Note that I reference system property “ownerId” from class “PhoneBook”. This field can be filled manualy, or automatically if entity of class was created by authenticated user. This field contains objectId of BackendlessUser which is the owner. More about owner policies you can read here: http://backendless.com/documentation/data/android/data_security.htm

  1. Yes, that’s right. Construction can be used only with where clause.

best regards,
Alex Navara

5

Thanks Alex.

With regard to 1), it seems we might have touched upon an important detail since the “owner” member is a “Contacts” object and the “ownerId” member is a “Users” object. If we first assume:

  1. “Contacts” objects are simply a pool of objects that for convenience we assume are not owned by any user (or perhaps are owned by the system).
  2. A “Contacts” object can be the owner of one or more PhoneBook objects.
  3. A “PhoneBook[owner].ownerId” can be null, the value of the current user, or the value of another user (perhaps including the system).

Now if we consider “Contacts” objects based on their appearance in “PhoneBook” objects relative to the current authenticated user, because the owner member and ownerId member of the “PhoneBook” object can refer to different users, there are seven disjoint sets of Contacts possible:

  1. “Contacts” objects that are the owner member of only one or more “PhoneBook” objects whose “PhoneBook[owner].ownerId” member value is null.
  2. “Contacts” objects that are the owner member of only one or more “PhoneBook” objects whose “PhoneBook[owner].ownerId” member value is that of the authenticated user.
  3. “Contacts” objects that are the owner member of only one or more “PhoneBook” objects whose “PhoneBook[owner].ownerId” member value is that of any user except the authenticated user.
  4. “Contacts” objects that are the owner member of multiple “PhoneBook” objects whose “PhoneBook[owner].ownerId” member values are a mix of only null and the authenticated user.
  5. “Contacts” objects that are the owner member of multiple “PhoneBook” objects that include a least one each of objects whose
    “PhoneBook[owner].ownerId” member values are null and any user except the authenticated user.
  6. “Contacts” objects that are the owner member of multiple “PhoneBook” objects that include a least one each of objects whose
    “PhoneBook[owner].ownerId” member values are the authenticated user and any user except the authenticated user.
  7. “Contacts” objects that are the owner member of multiple “PhoneBook” objects that include a least one each of objects whose
    “PhoneBook[owner].ownerId” member values are null, the authenticated user, and any user except the authenticated user.

It seems an interesting use case is referenced to the authenticated user. That is, where one is only interested in partition of “Contacts” objects into two disjoint sets comprised of:

1)’ is the union of 1), 3), and 5). These are “Contacts” objects that are the owner only of “PhoneBook” objects not owned by the authenticated user.

2)’ is the union of 2), 4), 6), and 7). These are “Contacts” objects that are the owner member of some “PhoneBook” owned by the authenticated user.

From your helpful answers, one can retrieve 2)’ in a single query. But it seems it may take more than one query to retrieve 1)’. Is there any particular info in the Backendless documentation that might shed more light on how the Backendless advanced query methods take the AuthenticatedUser into account when the authenticated user-token is incorporated into the retrieval process, such as a Custom Event, with the HeadersManager.getInstance().addHeader(…) routine?

Thanks for all your assistance.

6

Hi, Kevin!

I shall try to answer you, but first I’m able to explain owner concept. As you’ve noticed, and you were absolutely right, owner policies is an important detail. Maybe, you already know it, but I think that it can be useful.
Each class of Backendless data service has a few system columns like “created”, “updated”, “objectId”, “ownerId”. I’d like to explain more about the last one.
Lets consider that there is a class “Item”. Scenario is the following: logged in BackendlessUser with email “foo@foo” and objectId “ABC” creates a few new entities of class “Item”. Automatically these new entities are created with field “ownerId” filled with value “ABC”. Later another logged in BackendlessUser with email “bar@bar” and objectId “DEF” creates a few entities of class “Item”. After that we create a few new rows in table “Item” through Backendless console. Their “ownerId” fields are set to null.
Now lets consider that we deny “Find” action in owner policy tab for table “Item”. Retrieve actions ( Backendless.Data.of( Item.class ).find() ) will now have the following results:

  1. If foo@foo is logged in - collection would contain “Items” where ownerId = “ABC” OR ownerId = null
  2. If bar@bar is logged in - collection would contain “Items” where ownerId = “DEF” OR ownerId = null
  3. For non-authenticated user - collection would contain only “Items” where ownerId = null
    I hope that this information was understandable and useful.

Ok, now about your question. If I understood you correct, than the following clause would retrieve collection you’re looking for:

"PhoneBook[owner].ownerId != '" + Backendless.UserService.CurrentUser().getObjectId() + "'"

I assume the following structure: class “PhoneBook” has relation 1:N to table “Contact”. Entities of class “PhoneBook” are created either by Users or from console - so they may have or may not have value in “ownerId” field. Owner policies are not used for table “PhoneBook”.

About custom events. For example, you want to retrieve collection of Contacts in Custom Event and you need objectId of current user. In order to do that you can pass the objectId of current user to the handler when invoking the event. User token is used to determine if user session has not expired and to let server determine which user makes the call.

Please, inform me if everything is clear and if you have more questions.
best regards,
Alex Navara