Question about find with relationship?

Flutter
1

I have a question:
In database table A relation 1:N to users table and in A have multi record had relation to user with objectId = ABC and XYZ
When my “whereClause” = "users LIKE ‘%ABC…%’ " or “whereClause” = "users LIKE ‘%XYZ…%’ "
This find data and not empty
But when “whereClause” = "users LIKE ‘%ABC…%’ AND users LIKE ‘%XYZ…%’ "
This return [];

2

Hello @Minh_Tuan_Vu

Try the following
“whereClause” = "users LIKE ‘%ABC…%’ OR users LIKE ‘%XYZ…%’ "

Regards,
Inna

3

Tks @Inna_Shkolnaya
But i want find “AND” not “OR”

4

Hello @Minh_Tuan_Vu

Could you please share with us sample list of data you are trying to get?

Regards, Vlad

5

Hi @vladimir-upirov
it simply retrieves data if in that table the relation with users (1:N) and users contains my 2 objectId at the same time

6

are you sure you need to use LIKE in this case?

maybe IN or = would be much suitable?

7

@vladimir-upirov It just like below. I would use “AND” relationship but not “OR”.
In fact, “OR” relationship work like charm but not in case of “AND”. It’s very strange…

8

This is because that condition cannot be true ever. Suppose you look for:

FruitName LIKE ‘%APPLE’ AND FruitName LIKE ‘%PEAR’

a fruit cannot be both an apple and a pear at the same time.

Regards,
Mark

9

Hi @mark-piller.
I’m sorry for the misunderstood you have on my sample. I just want to say that I want to use “AND” operation in a query. That’s it! No less or more.

And don’t worry about the business logic in my code, it’s my job, man!

So, to correct my sample if you want, it should be as below:

DataQueryBuilder queryBuilder = DataQueryBuilder()
…relationsDepth = 2
…whereClause =
“type = ‘solo’ AND users LIKE ‘%${AppPref.user.objectId}%’”;

The query return an empty array even I have corresponding results in DB.

However, I just think that the “AND” is a very basic operation that every data programming languages MUST supported. And I think that Backendless is not an exception.
However, it were may be my fault that I don’t find it in your documents, that’s why I need your support!

Many thank for your comprehension.

10

Backendless DOES support “and”, you can test the query using Backendless REST Console (see the REST Console tab on the Data screen).

11

@mark-piller Thank you. I’m going to test the query in this console first.

12

Hi @Minh_Tuan_Vu ,

users is a relation column, so when you want to filter by this column, you should specify by which column of table that users pointing to you’d like to filter.
E.g. type = 'Solo' AND users.active = true
or
type = 'Solo' AND users.objectId LIKE '%ABCD-4242%'

Regards,
Stanislaw

13

@mark-piller and @stanislaw.grin
My real problem is that:
I’m developping a chat tool where many users meet around in a conversation.
To do that, I created a “Conversation” table with 1:N relationship to “Users” table.
Below is the structure of “Conversation” table:

image
image1361×544 56.9 KB

Now, I want to make a query on “Conversation” table to test if a conversation exist for 2 any users, I did like that:
DataQueryBuilder queryBuilder = DataQueryBuilder()
…relationsDepth = 2
…whereClause =
“type = ‘solo’ AND users LIKE ‘%${AppPref.user.objectId1}%’ AND users LIKE ‘%${AppPref.user.objectId2}%’ ”;

I meant I try to find if it exist a conversation of type “solo” and between 2 users (user.objectId1 and user.objectId2).

In SQL, it should be an inner-join between 2 tables.

However, I don’t know ho to realize that query correctly with Backendless API for Flutter. COuld you please help?

14

I believe using LIKE for objectId doesn’t make sense. objectId is a unique value and like-searches do not apply. Also, please see the comment above about “users” being a relation column:

15

@mark-piller and @stanislaw.grin,

First, I confirmed that the like-searches on objectId work perfectly. I tried:
DataQueryBuilder queryBuilder = DataQueryBuilder()
…relationsDepth = 2
…whereClause =
type = ‘solo’ AND users.objectId LIKE ‘%37215765-05DB-4870-8D35-3A6F94E1588E%’

and find my result.

  1. Howerver, when adding an “AND” operation for second user, I wrote:

DataQueryBuilder queryBuilder = DataQueryBuilder()
…relationsDepth = 2
…whereClause =
type = ‘solo’ AND users.objectId LIKE ‘%37215765-05DB-4870-8D35-3A6F94E1588E%’ AND users.objectId LIKE ‘%5371E09C-D5EA-4E19-A790-A1289171DF5F%’

And I received an empty array.

I supposed that I should not use “AND” here, but a “Inner Join” instead. But I don’t know how to write down this.

Remind of relations: “Conversation” 1: N “Users”

16

This part of the condition does not make sense:

It does not make sense because the same objectId CANNOT be equal the same value at the same time.
Another thing that does not make sense is using LIKE. Instead, you should use =:

users.objectId = '37215765-05DB-4870-8D35-3A6F94E1588E'

If you use LIKE, you will be slowing down your query execution.

Regards,
Mark

17

@mark-piller : It seems like you said…
But do you know how can I find a “conversation” record that contain 2 users when I know their 2 objectIDs?

18

Have you tried this?

users.objectId IN ('objectId1', 'objectId2' )
19

Hi @mark-piller and @Volodymyr_Ialovyi
It seem that the IN operation = OR.
I need AND instead of OR

Any suggestion?

20

Your AND condition is illogical.

Can the same person be named Minh and Mark? Suppose you search for

Name = ‘Minh’ and Name = ‘Mark’

Will you get any results? No, you wont, because the condition is evaluated for every record in the database. And name cannot be both Minh and Mark at the same time.