Filter source table based on the embedded table(inner join)

[steve-chavez]

This is a common use case. Examples:

• Filter embedded resources filter only the nexted. I expected to filter the first level supabase#2207
• How to get records based on foreign (embedded) table's column value? supabase#1827 (comment)
• Query across foreign tables supabase#1682 (reply in thread)
• Query using OR from different tables throws an error supabase#2234 (reply in thread)
• directive order does not work. supabase#1169 (reply in thread) (order by child table)
• Query only specific record by join supabase#2399
• inner join supabase#2438
• Filtering through a join value supabase#3155

For solving that on the backend, PostgREST/postgrest#1075 needs to be cleared.

Currently the users need to work around this on the client side.

Alternative

However, an alternative could be doing the filtering on postgrest-js itself. When a user does this:

await supabase
    .from('messages')
    .select('id, user_id, users(id, username)')
    .filter('users.username', 'eq', 'Calebe')

postgrest-js can detect that the embedded table(users) filter should also filter the source table(messages). So, if a row has the users attribute as null or [], then the whole row can be filtered. Check the example on supabase/supabase#2207 (comment) for sample outputs.

To enable this mode, an option to filters could be added. Like:

eq('users.username', 'Calebe', {filterSource: true})
// or perhaps as "inner", that would reflect what postgrest would do eventually
eq('users.username', 'Calebe', {inner: true})

This could also be enabled by default, because is what most users would expect. As mentioned on supabase/supabase#2207:

"I expected more atomic results considering the filter will exclude the result at the primary level."

Drawback

"I can't use this approach on the table that could lead to big dataset."

[calebeaires]

Hi @steve-chavez, is this schedule to supabase 2.0? It is a real need 👍🏼 🥇

[steve-chavez]

Hey @calebeaires. Yes, I'll work on this for the new postgrest version.

[KULTI1995]

When can you expect it? @steve-chavez

[zbaloch]

+1 - I need this too.

[edwinquaihoi]

+1

[iampopal]

+1 I am looking forward for this so it will get support for https://github.com/supabase/supabase-flutter/ too

[chrisLoPresti]

+1 deff need

[alexverdaguer]

+1 - Nextjs + getServerSideProps()

[steve-chavez]

An update here. This one is already solved at the PostgREST side 🎉

A new release for PostgREST will be made soon, then all Supabase instances will be upgraded to this release.

[mstade]

@steve-chavez you are my hero! 🥰

[rahuldubey093]

Can we have an estimated release date?

[meghaboggaram]

waiting for this fix.. by when can we expect this feature?

[calebeaires]

Hey everyone

Postgrest has launched the new stable 9.0.0 version. Has anyone capable to make a request to update the homebrew formula?

[steve-chavez]

All Supabase instances have this feature now!

https://supabase.com/blog/2021/11/28/postgrest-9#resource-embedding-with-inner-joins

[rahuldubey093]

Thank you for the release!

However, I couldn't figure out how to use the !inner keyword in my query:

final response = await supabase
      .from('users')
      .select('*, connection:users_connections!users_connections_otherUserUUID_fkey(*)')
      .eq('connection.userID', currentUserUID)
      .not('connection.beingBlocked', 'eq', true)
      .not('connection.blocking', 'eq', true)
      .execute(count: CountOption.exact);

I have tried something like:
.select('*, connection:users_connections!inner!users_connections_otherUserUUID_fkey(*)')
but it didn't work.

[steve-chavez]

but it didn't work.

@rahuldubey093 What's the error message?

[rahuldubey093]

What's the error message?

This is the response
response: {data: [], status: 200, error: null, count: 0}

@steve-chavez It isn't returning any data, even if the rows meet the condition

[steve-chavez]

@rahuldubey093 What's the result without using the !inner keyword?

[rahuldubey093]

What's the result without using the !inner keyword?

@steve-chavez The query returns results but the below conditions don't make any difference:
.not('connection.beingBlocked', 'eq', true) .not('connection.blocking', 'eq', true)

I still get rows where 'connection.beingBlocked' == true

[steve-chavez]

Ok, so doing !inner doesn't return any rows on:

final response = await supabase
      .from('users')
      .select('*, connection:users_connections!users_connections_otherUserUUID_fkey!inner(*)')
      .eq('connection.userID', currentUserUID)
      .not('connection.beingBlocked', 'eq', true)
      .not('connection.blocking', 'eq', true)
      .execute(count: CountOption.exact);

Are you sure there are rows that fulfill all the conditions? Try by removing some filters and checking the data returned.

[rahuldubey093]

Are you sure there are rows that fulfill all the conditions? Try by removing some filters and checking the data returned.

@steve-chavez You are right! Some rows had null values that's why it wasn't working. Now I have made sure none of the rows have null value on the queried columns and the !inner keyword is working as expected! Without the !inner keyword the query was still returning rows which met the conditions.

Thank you so much 👍🏻

[johnhforrest]

Is the contains modifier supported with this change? Code snippet below showing the issue.

const query = supabase
    .from<RawSearchResult>('games')
    .select(
      `
    id,
    name,
    cover_url,
    release_date,
    genre_entries!inner(genre_id),
    platform_entries!inner(platform_id)
  `,
    )
    .limit(limit);

// Works
if (platformFilters.length) {
  query.in('platform_entries.platform_id', platformFilters);
}

// Doesn't work
if (platformFilters.length) {
  query.contains('platform_entries.platform_id', platformFilters);
}

If I use the in operator, the query works but it filters out all of the other platforms. contains throws an error:
{"message":"operator does not exist: smallint @> unknown","code":"42883","hint":"No operator matches the given name and argument types. You might need to add explicit type casts.","details":null}

For context, if I don't apply any filters my response looks like this (the JSON in this case doesn't include all of the platform_entries due to the issue I described above):
[{"id":81085,"name":"Dark Souls: Remastered","cover_url":"co2uro","release_date":1527033600,"platform_entries":[{"platform_id":130}],"genre_entries":[{"genre_id":12}, {"genre_id":25}, {"genre_id":31}]}]

[steve-chavez]

@johnhforrest The same contains filter fails if you remove the !inner keyword right? This problem should be orthogonal to !inner.

For contains(which is translated to @> in PostgreSQL) to work both operands should be arrays as defined in
https://www.postgresql.org/docs/current/functions-array.html#ARRAY-OPERATORS-TABLE

And platform_id is a smallint as mentioned in the error message.

[johnhforrest]

Makes sense, I thought it might have something to do with me misunderstanding the contains query but I figured I would ask. Appreciate the help!

[kheber92]

Does this work with multiple .or() conditions?

I have a quite simple (working) SQL query which needs to be "translated" to Supabase SDK:
SELECT * FROM items JOIN manufacturers ON items.manufacturer_id = manufacturers.id WHERE items.name = 'Apple' OR manufacturers.name = 'Apple'

So by searching for a keyword (e.g. 'Apple') I'd like to query both tables items and manufacturers for their name column.

My approach would be something like this, although it doesn't return any value:

supabase
  .from('items')
  .select('*, manufacturers!inner(*)')
  .or('name.eq.Apple') // Removing this returns a value
  .or('name.eq.Apple', { foreignTable: 'manufacturers' })

However the SQL query works fine so I'm not sure if I have used the !inner correctly?

[steve-chavez]

Does this work with multiple .or() conditions?

No, you cannot apply or across embedded tables(with !inner or without). See PostgREST/postgrest#2014.

For that case you'll need a function and call it through rpc.

[kheber92]

Does this work with multiple .or() conditions?

No, you cannot apply or across embedded tables(with !inner or without). See PostgREST/postgrest#2014.

For that case you'll need a function and call it through rpc.

Thanks a lot, I have checked rpc.
Unfortunately, the only suitable return type for Functions is record and this returns a single record only. In my case I need to fetch multiple. I know Functions are Alpha Preview so maybe multiple records are possible in the future.

However I'm wondering if this common SQL query (see my previous post) is not possible with Supabase at the moment?

Querying two tables (connected by a foreign key) by the conditions with the logical OR, like this:
SELECT * FROM table_a JOIN table_b ON table_a.foreign_key_id = table_b.id WHERE table_a.name = 'abc' OR table_b.name = 'abc'

[steve-chavez]

Unfortunately, the only suitable return type for Functions is record and this returns a single record only. In my case I need to fetch multiple.

You can do CREATE FUNCTION ... RETURNS SETOF <table> to return multiple rows of a table.

(see my previous post) is not possible with Supabase at the moment?

No, as I mentioned above you can't combine OR conditions of different tables. You'll need RPC for this case.

[johnhforrest]

@steve-chavez One more issue potentially related to this unless it's just a general limitation on supabase requests. I can also open a separate issue to track this if preferred.

In the snippet below, titleIds is an array of strings with 386 elements. Each string is a string ID of shape '2024145056'.

This query generates an HTTP 502 response 100% of the time. Here's the error:

{
  error: {
    message: 'An invalid response was received from the upstream server'
  },
  data: null,
  count: null,
  status: 502,
  statusText: 'Bad Gateway',
  body: null
}

If I slice the array to 150 elements, the query works just fine with no other changes. My hunch is that I'm hitting some sort of undocumented limit but 386 is not a huge array and it's certainly not a large request in terms of bytes.

Code snippet:

type JoinedResult = Pick<definitions['games'], 'id' | 'name'> & {
  collection_entries: Pick<definitions['collection_entries'], 'status_id'>[];
  xbox_games: Pick<definitions['xbox_games'], 'title_id'>[];
};

const response = await supabase
    .from<JoinedResult>('games')
    .select(
      'id, name, collection_entries (status_id), xbox_games!inner(title_id)',
    )
    // @ts-ignore: inner join types
    .in('xbox_games.title_id', titleIds);

[roker15]

All Supabase instances have this feature now!

https://supabase.com/blog/2021/11/28/postgrest-9#resource-embedding-with-inner-joins

does not work with typescript, because in filter it expect only parameter from current table and not from embedded table.

[steve-chavez]

@johnhforrest I think that might be the URL length limit. See PostgREST/postgrest-docs#417 for a workaround.

[johnhforrest]

@steve-chavez that was it. Moving to RPC fixed my issue. Thanks!

[EnzoBitMatrix]

How did you do this with RPC?

I need to return a JSON response using just the HTTP rest api (using AppGyver).
Any idea's how I get RPC to return the below SQL Script as JSON? I should mention that the script runs perfect and returns the correct results when I put it in the SQL Editor page.

In the docs I only find this: https://[MYSUB].supabase.co/rest/v1/table1?select=some_column,other_table(foreign_key)

SELECT
  table1.date,
  table1.time,
  table1.vehicle,
  table2.type_name,
  table3.status_name,
  table4.sim_name,
  table5.scenario_name,
  table6.staff_name
FROM table1
INNER JOIN table2 ON table1.type = table2.id
INNER JOIN table3 ON table1.status = table3.id
INNER JOIN table4 ON table1.sim = table4.id
INNER JOIN table5 ON table1.scenario = table5.id
INNER JOIN table6 ON table1.staff = table6.id

Thank you in advance!

[steve-chavez]

@adrianstoker You need to put the query inside a function:

create or replace function my_func() returns setof table("date" date, "time" timestamp/*complete your columns here*/) as $$
SELECT
  table1.date,
  table1.time,
  table1.vehicle
--...
$$ language sql stable;

Then call it like:

GET https://[MYSUB].supabase.co/rest/v1/rpc/my_func

The REST API will convert it to json automatically.