Hi ! 👋 I have two collections, candidates and mandated_candidates, that are joined : - candidates - id - name - mandated_candidates - id - candidate_id (reference candidates.id) - mandate_id - status I'm trying to query the candidates collection to get only the candidates that are assigned to a specific mandate X (where an entry in the mandated_candidates collection exists where mandate_id=X) but NOT assigned to mandate Y. I've tried this filter but it doesn't work :

filter_by: $mandated_candidates(mandate_id:=X) && !$mandated_candidates(mandate_id:=Y)

I get this error : More than one joins found for collection

mandated_candidates

in the

filter_by

. Instead of providing separate join conditions [...] I found this issue on GitHub that might be related : https://github.com/typesense/typesense/issues/2145 Is what I'm trying to do possible? Thanks !

If you're sending this request to the

candidates

collection, then a reference field should exist in that collection that references a field in

mandated_candidates

for you to be able to mention that $collectionName in the filter_by

Hey Jason, thanks for the fast reply! The relation is one candidate to many mandated_candidates. I based my setup on the example in the docs with the customers and the orders, where the reference field is on the "orders" table and you're querying the "customers" collection I don't think I can add a "mandated_candidate_id" field to the "candidates" collection since there would could be multiple, it's not a one to one relationship

Oh my bad, you're right. I was going off of some early discussions around the syntax. I'll defer to @Harpreet Sangar on this.

@Charles-Antoine Demers Wouldn't

filter_by: $mandated_candidates(mandate_id:=X) && !$mandated_candidates(mandate_id:=Y)

be logically equivalent to

filter_by: $mandated_candidates(mandate_id:=X)

Unless

mandate_id

is an array field 🤔 The use case of the github issue is different from your scenario. This should work for you:

filter_by: $mandated_candidates(mandate_id:=X && mandate_id:!=Y)

Hey @Harpreet Sangar I think my example is a bit hard to follow. Let me try with another example using the same concepts as the docs : you have customers (id, name) that can have many orders (id, customer_id, item_name) Some example data : • Customer A ◦ Order 1 - iPhone 16 ◦ Order 2 - Samsung S25 • Customer B ◦ Order 1 - iPhone 16 ◦ Order 2 - Pixel 9 • Customer C ◦ Order 1 - Pixel 9 I can get everyone that ordered an iPhone 16 with the following query on the customers :

filter_by: $orders(item_name:=iPhone 16) // returns customer A and customer B

I can also get everyone that has never ordered an iPhone 16 :

filter_by: !$orders(item_name:=iPhone 16) // returns customer C

I want to get is the customers that have ordered an iPhone 16, but have not ordered a Samsung S25. This query doesn't work :

filter_by: $orders(item_name:=iPhone 16 && item_name:!=Samsung S25) // returns customer A and B, should not return customer A since they also have an order for the S25

Does this example make a bit more sense?

Let's remove joins from the scenario. If we had a field

foo

and the following docs:

foo: [X, Y]
foo: [X, Z]
foo: [Z]

You want a way to get all the docs where

foo

is

X

but not

Y

. So only second doc should be a match?

Yep that's what it would look like without the joins

Okay, so

foo:=X && foo:!=Y

would return only the second document.

Copy code

filter_by: $orders(item_name:=iPhone 16 && item_name:!=Samsung S25) // returns customer A and B, should not return customer A since they also have an order for the S25

Similarly, this would only return B. If

filter_by: $mandated_candidates(mandate_id:=X && mandate_id:!=Y)

isn't returning the expected results, can you share a reproducible example?

Sure 🙂 The original app is made with Laravel using the official PHP client but I'll try to make a simpler version that reproduces the issue. Is PHP okay, I could also make it in JS if it's easier

it's not a one to one relationship

No need for reproduction steps. I can now imagine a scenario like

candidate_a
  {id:0, mandate_id: X}
  {id:1, mandate_id: Y}

candidate_b
  {id:2, mandate_id: X}
  {id:3, mandate_id: Z}

candidate_c
  {id:4, mandate_id: Z}

Only

filter_by: $mandated_candidates(mandate_id:=X) && !$mandated_candidates(mandate_id:=Y)

would return

candidate_b

.

Yes, exactly 👍

I've added your use case to the same github issue.

Thanks a lot for your help, I really appreciate it