Feat: support relation filter

[magrinj]

Scope & Context

Relation between customs and standards objects has been added recently #2398

Current behavior

We can't make a query filtering on nested relations

Expected behavior

We should be able to make query filtering on nested relations, by example query like that:

query Documents {
  documents(filter: { suppliers: { name: { like: '%title%' } } }) {
    edges {
      node {
        id
        title
        suppliers {
          id
          name
        }
      }
    }
  }
}

Technical inputs

• This ability should be added in the filter factories. • query-builder should handle them properly based on how pg_graphql is supposed to receive them.

Designs

https://github.com/user-attachments/assets/e8c37725-ffd7-4853-bd37-dddc19a01db8

[magrinj]

Not supported for now, but should be added: https://github.com/supabase/pg_graphql/issues/88

[magrinj]

Just exposing foreign key in #2505 for now, and make multiple requests

[FelixMalfait]

Should be possible now that we've moved the findMany endpoint outside of pg_graphql

Related: https://github.com/twentyhq/twenty/issues/6891

[Bonapara]

@FelixMalfait added the designs to the description

[Bonapara]

Behavior updated with multi-select instead of select

https://github.com/user-attachments/assets/ea9519ef-c6ec-48d6-9a92-e60fa6c7b8e9

[FelixMalfait]

@Bonapara actually The feature was about even more advanced filter (person.company.name does not contain X or company.person.address is Y). It's true that for one-to-many relationships we only display it one side of the relation, but I would say this is a specific sub-case for the ID field (from an eng perspective) - and because ID are not user-friendly there's a special UI layer for IDs that retrieve the name/pictures. Let's discuss orally if it's not clear!

[Bonapara]

But is filtering on person.company.id a V1 of person.company.any_field?

V2 I had in mind (Doable as V1?)

https://github.com/user-attachments/assets/f210cc47-62d9-4673-9fe1-c9857e0f0b1e