How do i search with this conditions?

General
1

As possibly reduced example of my case:

Table “Contacts” contains 100 rows,

User #1 has 5 contacts with phone numbers 11, 22, 33, 44, 55
User #2 has 5 contacts with phone numbers 44, 55, 66, 77, 88
each contact has own name, so each contact is separated row, so both users have 10 rows total (5 and 5)

I need to get contacts with phone numbers 66, 77, 88

In real case each user might have more than 1000 “contacts”

2

Hi, Anton,

Could you specify somehow the schema of your data?
If this is a test app you can simply export your data and share in the thread or send me by email artur.dzidzoiev@themidnightcoders.com

Best,
Artur.

3

Thanks for reply, Artur!
The schema is more complicated, than this example, but the issue is completely the same.

Are you sure you need this?

4

You can export only one table “Contacts” because at the moment I don`t fully understand the question.

Best, Artur.

5

there are related tables, which is involved in this. The app is under development atm, so i can just give you my credentials to access my account - it will be easier. Where should i send my creds?

6

If the question is how to structure the “where clause” query, you could do this:

PhoneNumbers in (66,77,88)
7

You can add me as developer to your app, my email is artur.dzidzoiev@themidnightcoders.com

8

Sorry, i’ve just realised why you don’t understand what i mean)

forget about this:

I need to get contacts with phone numbers 66, 77, 88

I want to retrieve the unique phone numbers (not contacts) from user#2
Both User#1 and User#2 have contacts with phone numbers 33, 44, 55, so the unique contacts from user#2 will be 66, 77, 88.

9

So, I need something like this:
search contacts, ownerId=[User#2Id], phoneNumber != [all of the user#1 phone numbers]

10

Artur, could you, please, check the messages below? may be it will make my question clearer

11

There is no single query that would be able to get that in a single shot. As a workaround, you could add a server-side event handler for the “Create” and “Update” operations. In the event handler, you’d check the uniqueness of the phone number and if it is not present for other users, mark that contact as such (by setting some value in a special column). Once you have that special value, you can easily load the contacts with unique phone numbers.

12

how many relations could be established in Data Relation (1:N) column? What if i will create separated table “PhoneNumbers”, which will contain two columns: “phone” and “relatedUsers”?
So, i will be able to use ‘where’ query in Contacts "phoneNumber.relatedUsers.objectId!=[User#2Id]

13

We do not restrict the number of related entities. The approach you propose should work.

14

if phoneNumber has more than one user, then this approach doesn’t work(

15

and i can’t put special markers in the ‘text’ type column, because i will have to retrieve this column each time to add data in it. If this column will contain 1000+ markers, it will be kind of slow

16

So, i will be able to use ‘where’ query in Contacts "phoneNumber.relatedUsers.objectId!=[User#2Id]

may be i should change where query somehow to make this works?

17

As a workaround, you could add a server-side event handler for the “Create” and “Update” operations. In the event handler, you’d check the uniqueness of the phone number and if it is not present for other users, mark that contact as such (by setting some value in a special column). Once you have that special value, you can easily load the contacts with unique phone numbers

This also is not helpful, because almost all of the ‘phoneNumbers’ will be shared between thousands of users. And i need to retrieve not the unique phoneNumbers, but only those numbers, which current user doesn’t have yet