Deep filtering with relational table

[Pankaj_P]

Feature(s) impacted

Filtering

Observed behavior

Not able to use fields from the foreign table to filter

Expected behavior

Should be able to use fields from the foreign table to filter

Context

User Emails table stores foreign key of Users table. How can I filter using the fields from Users table?
I tried the following. But it shows “No records match your filter” which is wrong because the same filter works in the Users table.

• Organization name: Project-V-Org
• Project name: Project-V-Admin-Panel
• Environment name: Production
• Database type: MongoDB
• Recent changes made on your end if any: NA…

[Enki]

Hi @Pankaj_P ,

Can you share the code where you define the relationships between usersEmails and users, if any ?
Also can you share the schema of you DB structure in mongoDB for those tables ?
Does the filtering work in the other direction ? like filtrering on “userEmail.email” from the table “users” ?
I think both your issues are related.

[Pankaj_P]

@Enki

Can you share the code where you define the relationships between usersEmails and users, if any ?

I am not defining relationship from code. It is auto detected when forestadmin schema is formed.

Does the filtering work in the other direction ? like filtrering on “userEmail.email” from the table “users” ?

Email field is not shown in Users table.

Also can you share the schema of you DB structure in mongoDB for those tables ?

From Forest admin schema json
Users table -

Emails table -

[Enki]

Filtering is only possible over fields, or OneToOne/ManyToOne relationships, because in this case, the relationship is considered like a field in many cases.
The emails fields is not shown in the table “Users” because several userEmails are linked to one user, so it is normal not to be able to filter in this direction.

The fact that the field “user” is available to filter the “userEmails” table make me think that the relationship seems to be well defined: the filter should work.
Can you access the “user” from a “userEmails” record ?

You said that “the same filter works on the table users”, is that possible that all users that matches the filter do not have an associated userEmail ?

[Pankaj_P]

Can you access the “user” from a “userEmails” record ?

I am redirected to the correct User record when that “null null” link is clicked. (I had mentioned this in the other issue)

You said that “the same filter works on the table users”, is that possible that all users that matches the filter do not have an associated userEmail ?

I checked few random users. They have email IDs

[Pankaj_P]

@Enki, one important update that might help solve the issue.
In MongoDB, there is no UserEmail table. In the Users table, emails is a JSON array, which is why the Forest Admin is treating it as a separate table with a many-to-one relationship. (Correct me if I am wrong.)
Could you please tell me how I can remove the incorrect table and restore the emails array to its original form?

[Enki]

That’s an important thing to know indeed.
Unfortunately, there is no way to customise this in the cloud version of forest admin. But for your usecase, it shouldn’t be necessary, the relationship should allow filtering on user attributes from the userEmails table.

Is the issue still unresolved ? we recently released an update on cloud version dependencies, it may have solved the issue.
If not, I will need your exact BD schema in mongoDB to be able to reproduce. I still cannot with the informations you gave.

[Pankaj_P]

I am not able to filter, so the issue is unresolved even after dependencies update.
Here is the MongoDB schema

{
    "$jsonSchema": {
      "bsonType": "object",
      "required": [
        "_id",
        "createdAt",
        "emails"
      ],
      "properties": {
        "_id": {
          "bsonType": "string"
        },
        "createdAt": {
          "bsonType": "date"
        },
        "emails": {
          "bsonType": "array",
          "items": {
            "bsonType": "object",
            "properties": {
              "address": {
                "bsonType": "string"
              },
              "verified": {
                "bsonType": "bool"
              }
            },
            "required": [
              "address",
              "verified"
            ]
          }
        },
        "updatedAt": {
          "bsonType": "date"
        }
      }
    }
  }

[Enki]

I created a project where I setup your exact situation and don’t reproduce the bug.

Can you spot any difference in your settings ?
I used cloud mongoDB with version 8.0.9, what version of mongo do you use ?

The DB:

The result in Forest Admin:

Filtering on parent.name works:

[Pankaj_P]

DB users structure is correct, but I am using MongoDB version 6.0

Admin panel snapshot

Does this match with you forest admin schema?

[Enki]

I have exactly the same schema:
My one:

Yours:

Does any filter work on user from the user_email collection ? Does a simpler filter work, like just user.id is … ?

[Pankaj_P]

@Enki Simple filter on user ID works

[Enki]

Ok, so that’s not an issue with the relationship itself.
You do confirm that the complex filter is working correctly on the user collection, and that filtered users do have emails ?

If that’s the case, can you share the network call to the agent showing the complete query with the filter, and the response to this call ?