Smart field filter with relation

Help me!
1

Hey,

I currently have a table A with one-to-one relation with B.
To obfuscate my DB relations, I created Smart fields in A to get fields from B.
How can I use the filter option on my table A smart fields to filter with the table B fields ?

Cheers :slight_smile:

2

Hi @Simon_BRAMI1 :wave: If I understand what you want to achieve
you can have a look on this documentation.
Let me know If that help :pray:

1 Like
3

Hey Arnaud! You put the wrong link mamen :joy:

4 Likes
4

Oops :sweat_smile: I edited the link sorry about that. :pray:

5

I know about this doc, but it does show how I could pass a condition that accesses a relation.

I saw another way of doing this by fetching the data I want and return in the condition all the ids but this solution does not scale well when you have thousands of records, the SQL query endup being way too long and so my instance can crash because they run out of memory just to allocate that much space in a single string.

Any idea of a clean way to return a condition that filter in a subrelation ?

something like:

return { 'B.column': { filterValue } }
6

In order to try to help you, can you share with us how do you declare your models and your smart field please :pray:

7

Hello! I have the the very same case with the filtering by related data. I am not sure if it’s possible but still :slight_smile:

I am filtering the payments table and I see that the query also joins the users table, so in theory I should be able to filter by users fields. So I’m trying to return the filter of the smart field as

  • { 'user.name': 'some name' } - this translates to payment.user.name and I get Unknown column 'payment.user.name’ error
  • { ‘user.name’: ‘some name’ } - same as above
  • { user: { name: null } } - I get Invalid value { name: { [Symbol(eq)]: null } }
8

Hey!

One solution to your problem is to fetch all users matching the name in a db query and then use [Op.in] operator to filter payments by all of the userIds you got from the query. Here is a ressource to help you achieve that. :wave:

You could have someting like this:

filter: async ({ condition: { value, operator } }) => {

      switch (operator) {
        case 'equal':
          //We are looking for all the users ids that matches the name
          const queryToFindUsers = await users
            .findAll({
                 where: { name: { [Op.eq]: value } },
	      });

          //We map this array of objects to retrieve the user ids
          const userIds = queryToFindUsers.map(user => user.id);

          // Then filter the payments by userId
          return { userId: { [Op.in]: userIds } };
        default:
          return null;
      }
    },

Let me know if that helps

1 Like
9

Thank you! I am aware of this approach but I don’t think it suits me. I simplified my example a bit and the real user condition would probably return too much records. So I was hoping to provide a condition for the users directly