RQL - How to use list filter and match two condition in the same item inside the list

MongoDB Atlas App Services & Realm Realm
1

Sorry if the title sounds confusing, it’s hard to describe my question in one phrase.

I’m using RQL to filter my query, and with that I’m using dot notation to filter the query based on an list item that match certain condition. But I need to make sure that the item that passed the condition also passes another condition, and that’s where I’m not sure what to do.

Example:

Let’s suppose I have two schemas:

UserSchema = {
   id: string;
   name: string;
}

TeamSchema = {
   ...
   members: UserSchema[]
}

Now I want to query Teams that has a member name that contains certain string.

For that I can simply use dot notation and filter with: teams.filtered("members.name CONTAINS $0", "somename").

Until here it’s fine, but now I want to add one more condition, the member which his name contains the string, can’t have the id ‘1’.

If I add members.id != '1' I don’t think it would work, since it would check in all members, not the one that contains the string in their name.

Anyone knows if there’s anything to be done in this case?

2

The question is a tad vague and the code provided doesn’t quite match the question.

Is Teams a Realm List?

I think that references members so it’s a UserSchema name?

And I think you’re asking about a substring query? Well, actually two substring queries? Wouldn’t it just be a matter of checking if the string contains somename and does not contain 1? If so, I think it’s just a syntax thing in your question.

If so here’s a Swift solution which works for a quick test project I made - it returns only the member where the name contains somename and also does not have a 1. I know you’re working with RQL but the concept should apply to any SDK

let results = realm.objects(PersonClass.self).where { $0.members.name.contains("somename") && !$0.members.name.contains("1") }

So use !$0.members... instead of !=

3

Thanks for your reply. I agree that the question and code looks confusing, sorry about that, I found it confusing to explain this specific question. Let me try to explain again with examples with data.

Using the same schemas I provided, let’s say in my database I have the following data:

Teams: [
  {
    id: 1,
    members: [
      { id: 1, name: "John Doe" },
      { id: 2, name: "Todd" }
    ]
  },
  {
    id: 2,
    members: [
      { id: 1, name: "John Doe" },
      { id: 3, name: "John Senna" }
    ]
  }
]

Now let’s say I run the query teams.filtered("members.name CONTAINS $0 AND members.id != 1", "John").

What I expect as a result is to only get the team with id 2, because although the team with id 1 has a user with that name, the user that contains the name has the id that I want to exclude from my condition.

And as far as I know, the actual result of this query would be an empty array. Since members.id != 1 would remove any team that has a member with id 1.

4

I see, so you want to query against two different properties.

I think there may be a bit of a logic issue - remember that a query runs across all objects in the list.

So taking the first part

members.name CONTAINS John

That resolves to both team id 1 and team id 2 because they both have members in their members lists that contain a John.

Then add in the second requirement:

members.id != 1

that returns the teams that do not have any members in their members list that contain 1 as their ID.

Well, both teams have members that have an id of 1, so NO teams would be returned.

5

Yep, that’s exactly why that query won’t work for the behaviour I want. Like you said, each condition from my query will apply to all members, but I want to run “two conditions” for the same member, not any.

Is there any solution for this? Or it’s not something that Realm supports?

I guess if there was a way to run a subquery it would solve the problem, but I don’t think that’s possible.

6

Yeah - totally get it.

As I mentioned though - it’s a logic issue and not a matter of it’s supported by Realm. Based on the conditions, data and query, no database would have a match.

Here’s what I mean

although the team with id 1 has a user with that name, the user that contains the name has the id that I want to exclude from my condition.

this part

has the id that I want to exclude

If you want to exclude any team that contains the user with that ID, you’re query does that because BOTH teams include a user with that ID, therefore, they are both excluded!

The issue right now is we don’t know what the parameters are; what’s the criteria of returning team 2? Why are substring queries being used? e.g. This is an object

{ id: 1, name: "John Doe" }

Do you want a query that returns all teams that do not include that object?

Let me ask this a different way; what are the requirements where team 2 would be returned and NOT team 1?

The only different between them is that team 1 has a Todd

team 1: { id: 2, name: "Todd" }

and team 2 has a John Senna

team2: { id: 3, name: "John Senna" }

Is it because team 2 has an object count of 2 that contains the name John?

7

Do you want a query that returns all teams that do not include that object?

No, that’s the behaviour from the query I sent, and that’s NOT what I want.

Let me ask this a different way; what are the requirements where team 2 would be returned and NOT team 1?

That’s because team 2 has another member that matches both conditions, name contains “John” and id != 1.

Is it because team 2 has an object count of 2 that contains the name John?

No, it’s not a matter of having a count of 2, it’s a matter of the member that got matched needs to satisfy both conditions.

By saying that, the query (which I don’t know how to write to achieve this) would need match only member { id: 3, name: "John Senna" }, since this members matches both conditions (name contains “John” and id != 1).

I hope that’s clearer now. So my question is which query would satisfy these conditions?

8

I gotcha. My brain works in Swift so here’s the query (subquery actually) that will return team with id 2.

We’re leveraging a subquery to iterate through the members collection property for company name and id on each object. Subqueries are defined by parens around the query and count (query).count > 0

let results = realm.objects(Team.self).where { ($0.memberList.name.contains("John") && !($0.memberList.id == 1) ).count > 0 }

for team in results {
    print(team.id)
}

output is

2

9

Oh ok, I see, that helps a lot. Now I’ll check the equivalent of that in RQL, but that’s a huge help, thanks very much for your time.

EDIT:
In case anyone is interested in the solution for RQL, check this link about subqueries.
The query would look like:
teams.filtered("SUBQUERY(members, $member, $member.name CONTAINS[c] $0 AND $member.id != 1).@count > 0", "John")

1 Like
10

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.