@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…
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
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.
Backendless DOES support “and”, you can test the query using Backendless REST Console (see the REST Console tab on the Data screen).
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
@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:
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?
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:
@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.
- 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”
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
@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?
Have you tried this?
users.objectId IN ('objectId1', 'objectId2' )
Hi @mark-piller and @Vladimir_Yalovy
It seem that the IN operation = OR.
I need AND instead of OR
Any suggestion?
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.
Hi @mark-piller
Sorry for make you misunderstanding.
I repeate my story:
My “Conversation” table has 1:N relations with “Users” table. Hence, in “Conversation” table, I have column “users” which were automatically created while adding the relation with “Users” table. Is this clear until now?
Now, I’m in the “Conversation” table. I want to find all the conversation records which contain my 2 users “Mark” and “Minh”. So I wrote the query like that:
DataQueryBuilder queryBuilder = DataQueryBuilder()
…relationsDepth = 2
…whereClause =
“type = ‘solo’ AND users LIKE ‘%Mark%’ AND users LIKE ‘%Minh%’ ”;
But I always get an empty array.
What’s worng in my query?
I don’t know if it clearer for you now, if not, please let me know.
And to anwser you question: In “users” column of “Conversation” table, we can find the “Mark” and “Minh” in the same record. Because, this column link to many users (1:N) with “Users” table.
Hello @Minh_Tuan_Vu
Please, try:
DataQueryBuilder queryBuilder = DataQueryBuilder()
…whereClause = “user.name = ‘Mark’ or user.name = ‘Minh’”
… property = “name”
… property = “Count(user) as count”
…groupBy = “user”
…having = “count=2”
1 Like
@Vladimir_Yalovy I tried it but not working because the operation
“whereClause = “user.name = ‘Mark’ or user.name = ‘Minh’”” should be “whereClause = “user.name = ‘Mark’ AND user.name = ‘Minh’””
I need to find a record of conversation which contain Mark and Minh. If I use “or” operation, I will get more conversations than I expected, for example: Mark-Vladimir, Minh - Vladimir, Minh - Mark. Only Minh-Mark is my expected result.
Do you get my point?
Many thanks
Hey @Minh_Tuan_Vu ,
try this out:
Basically this is the same as @Vladimir_Yalovy suggested you, but with additional condition - type = 'Solo'
DataQueryBuilder queryBuilder = DataQueryBuilder()
…whereClause = “type = ‘Solo’ AND (users.name = ‘Mark’ OR users.name = ‘Minh’)”
… property = “name”
… property = “Count(users) as usersCount”
…groupBy = “users”
…having = “count = 2”
Cheers,
Stanislaw
@stanislaw.grin Thanks so much, It finally work for me.
I just used your query with Count(users) = 1.
Many thanks to @Vladimir_Yalovy @mark-piller and @stanislaw.grin for your supports
1 Like