Support filtering based on child table relationship

[bossjapzz]

Describe the bug
Support filtering based on child table relationship

Expected behavior

[github-actions]

This issue is stale because it has been open for 30 days with no activity.

[apecollector]

Would like to see this as well, this is really what makes graphql appealing imo.

[gromchen]

How can I vote for this enhancement? 😅

[olirice]

you just did!

I agree this would be a great feature. We're currently focusing on some rough edges where we're not quite spec compliant + and improving type support (json/jsonb) arrays etc.

Once we have a good foundation for all the basics, this will be a feature we'll be interested in, but it isn't actively under development yet

[vwkd]

+1 (Adding context from #296)

Hasura can do this for both child and parent relationships. AFAIK the REST API can do this currently only for child relationships (see "Filter Foreign Tables").

For example with Hasura, this selects authors containing a name with all their books, or authors and the specific books whose description contains the name. This is often desired in a search. Note how this filters both on child and parent relationships.

query {
  authors(where: {_or: [{name: {_iregex: $name}}, {books: {description: {_iregex: $name}}}]}) {
    name
    books(where: {_or: [{author: {name: {_iregex: $name}}}, {description: {_iregex: $name}}]}) {
      title
      description
    }
  }
}

(Note, for the relationship names to be set like this in Hasura, the foreign key columns should not match the table name. For example, one can use a suffix like _id, e.g. books_id column in authors table and author_id column in books table.)

[robertn702]

Any update on this feature? It's currently my biggest blocker.

[olirice]

No movement yet but I'm aware that this is high interest feature

The only higher priority is user defined mutations via SQL functions

FYI:
Everyone is currently finalizing deliverables for launch week 8. You should see the pace of development pick back up after that

[cullophid]

I just wanted to add to the hype of this feature 🥳.

Keep up the good work pg_graphql is very promising!

[davidchalifoux]

I'm also looking for this feature!

[azlekov]

Interested in this one. Is there any workaround now like database views or whatever?

[olirice]

Interested in this one. Is there any workaround now like database views or whatever?

unfortunately, there isn't currently a workaround for filtering parents based on children

[magrinj]

Hey @olirice,
Any update on this ?
We're using pg_graphql at Twenty when querying the database for the dynamic part of the GraphQL API, and really need this feature
Thanks a lot to Supabase for this awesome project !

[olirice]

none yet, but we've identified nested inserts (which requires upsert support to work well) as the next big project we're tackling in pg_graphql so you should start to see movement on it

[RobSchilderr]

none yet, but we've identified nested inserts (which requires upsert support to work well) as the next big project we're tackling in pg_graphql so you should start to see movement on it

is there any workaround to achieve the same effect right now?

[olirice]

You could

• write a function that performs the filter ahead of time
• use two requests / filter on the client
• use a computed field if you're always filtering the child in the same way (no parameters required)

but all have their drawbacks

[Mihai-github]

I'm trying to filter data from table A based on a value in table B. Specifically, I want to filter records where B.something (some condition) specific value. I haven't been able to find an example of this query in the Supabase documentation. Could you please confirm if this type of filtering is supported in GraphQL on Supabase? If not, are there any workarounds or alternative approaches I could consider?

[imor]

@Mihai-github have you tried one of the options mentioned by @olirice above? If these don't work for you can you share more details about your use case?

[bryanmylee]

I'd love to tackle this problem, but I'm not sure how the GQL is translated into a PG query. Is the GQL just translated into a pgREST query?

[imor]

Is the GQL just translated into a pgREST query?

No, GQL is converted into a SQL query which is executed by the QueryEntrypoint::execute method for a query and MutationEntrypoint::execute method for a mutation.

I'm not sure how the GQL is translated into a PG query

The QueryEntrypoint::execute and MutationEntrypoint::execute methods convert GQL into SQL by calling the self.to_sql_entrypoint methods. The to_sql_entrypoint methods are implemented by various builder objects that implement the QueryEntrypoint or MutationEntrypoint traits. E.g. FunctionCallBuilder's impl just delegates to a call to its to_sql method. These builder objects are created earlier by parsing GQL and looking at the SQL context.

Let me know if this is sufficient detail for you to take a stab at implementing a solution or you need more.

[bh865]

We have any update on this?

You could

• write a function that performs the filter ahead of time
• use two requests / filter on the client
• use a computed field if you're always filtering the child in the same way (no parameters required)

but all have their drawbacks

Do you have a preferred method of these 3?

[olirice]

My preferred option would be #1
Extend with a function that performs the aggregation and then filter on that value
Docs: https://supabase.github.io/pg_graphql/computed_fields/#extending-types-with-functions

[bh865]

My preferred option would be #1 Extend with a function that performs the aggregation and then filter on that value Docs: https://supabase.github.io/pg_graphql/computed_fields/#extending-types-with-functions

So, I've managed to get a function that I can query successfully in the SQL Editor but it does not show up in my GraphiQL playground. Any suggestions? I left out 'immutable' and 'stable' as I've tried both to no avail.

CREATE OR REPLACE FUNCTION filter_events_search(gender VARCHAR) RETURNS TABLE( id UUID ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT e.id FROM events e JOIN teams t ON e.team_id = t.id WHERE t.gender = filter_events_search.gender; END; $$;

Looks like I finally got it working! Thanks for the direction!

[bobbybol]

Hi @olirice, any progress on this? Thanks ;)

[olirice]

not yet. we're currently working on upsert