Having trouble with query excluding related items

General
1

I’m having trouble with a backendless query. Using iOS.

My query is such:

BackendlessDataQuery *query = [BackendlessDataQuery query];

query.whereClause = [NSString stringWithFormat:@“isOn = TRUE AND uuid = ‘%@’ AND major = %d AND minor = %d AND messageUsersDelivered.objectId <> ‘%@’ AND (messageUsers.objectId = ‘%@’ OR messageIsPublic = TRUE )”, uuid, major, minor, currentUserObjectId, currentUserObjectId];

BackendlessCollection *bc = [beaconStore find:query];

This is resulting in unexpected results.
The table being queried is a custom Data table storing “messages”.
messageUsersDelivered a column on the table and is related one-to-many to the Users table.
messageUsers is a column on the table and is also related one-to-many to the Users table.

The <> operand does not appear to be working for excluding messages already delivered to this user.

2

A simpler way to ask this question is:

What is the correct WhereClass sql query syntax to do a “NOT IN” on a column that is related to Users?

Or if I have it correct in the example, is there a bug here?

3

Hi Daniel,

If I understand your request correctly, you have table BeaconStore with one-to-many relation to table Users, and you want to retrieve BeaconStore objects which don’t have a particular user in relation, right?

If so, then unfortunately our syntax does not support such functionality for one-to-many relations.
What you can do is retrieve the objects with smaller where clause

isOn = TRUE AND uuid = \'%@\' AND major = %d AND minor = %d AND messageUsersDelivered.objectId &lt;&gt; \'%@\' AND (messageUsers.objectId = \'%@\' OR messageIsPublic = TRUE )", uuid, major, minor, currentUserObjectId, currentUserObjectId]

and then manually iterate it and filter the ones you need (which are the ones without a particular related user).

4

Daniel, please try this:

query.whereClause = [NSString stringWithFormat:@“isOn = TRUE AND uuid = ‘%@’ AND major = %d AND minor = %d AND messageUsersDelivered.objectId != ‘%@’ AND (messageUsers.objectId = ‘%@’ OR messageIsPublic = TRUE”, uuid, major, minor, currentUserObjectId, currentUserObjectId];

5

Ok, I’ve tried using !=

The result is that it excludes one user, but not the other users that are also in messageUsersDelivered.
So it’s not quite working right yet.

6

Daniel,

Could you describe what you’re trying to accomplish without expressing it as a query?

Mark

7

Sure,

We are checking the Beacon messages table to see if the current logged in user has new Beacon messages.

In order to prevent the user from receiving the same message twice, once the user has received the Beacon message, we then add the user to “messageUsersDelivered” (which has a one-to-many relationship to the Users table).

So then, the next time we check for messages, we want to exclude that Beacon message from the results by checking to make sure they are not in the “messageUsersDelivered”.

8

Thanks, Daniel, for explaining for what you’re after. As Sergey pointed out, the exclusion syntax is not currently supported. As an alternative, how about recording the time when beacon messages were delivered to a user? Next time you grab messages, you can include a condition into the query to get messages which arrived after certain timestamp.