Supabase query with multiple foreign table queries (!inner) not returning any rows

Question

So I am querying a profile table that has fk relations to 'recommendations' and 'playlist'. I want to get the profile info, recommendations of a certain type, and playlist entries marked active.

However, unless there are items that that fulfill both queries, the query will return zero rows. If there are recommendations of type 'rock', but no active playlist entries then I get zero rows.

What I want returned:

[{
name: 'Joe',
email: '[email protected]',
recommendations: [{id: 1, title: 'some title', ...}, {id: 2, title: 'other title', ...}],
playlist:[]
}]

const { data, error } = await supabase
 .from('profile')
 .select('''
    name,
    email,
    recommendations!inner(
        id,
        title,
        url,
        date,
        type
    ),
    playlist!inner(
      id,
      active,
      played,
      played_date
    )
    '''
    )
    .eq('playlist.active', true)
    .eq('recommendations.type', 'rock')

I looked at 'or' queries, but these are for queries filtering the same table.

Steve Chavez · Accepted Answer · 2023-01-09 17:11:16Z

0

or across related tables work on postgREST latest prelease, which is available on the supabase CLI. Try it like this:

const { data, error } = await supabase
 .from('profile')
 .select('''
    name,
    email,
    recommendations(
        id,
        title,
        url,
        date,
        type
    ),
    playlist(
      id,
      active,
      played,
      played_date
    )
    '''
    )
    .eq('playlist.active', true)
    .eq('recommendations.type', 'rock')
    .or('(playlist.not.is.null, recommendations.not.is.null)')

answered Jan 9, 2023 at 17:11

Steve Chavez

1,25416 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

markalex · Accepted Answer · 2023-03-21 19:25:51Z

0

Search will be done on the relative table column by adding the inner joining !inner on relative tables

const { data, error } = await supabase
 .from('profile')
 .select('''
    name,
    email,
    recommendations!inner(
        id,
        title,
        url,
        date,
        type
    ),
    playlist!inner(
      id,
      active,
      played,
      played_date
    )
    '''
    )
    .eq('playlist.active', true)
    .eq('recommendations.type', 'rock')
    .or('(playlist.not.is.null, recommendations.not.is.null)')

edited Mar 21, 2023 at 19:25

markalex

13.9k3 gold badges15 silver badges48 bronze badges

answered Mar 13, 2023 at 22:29

Mubashar Hussain

131 silver badge5 bronze badges

Collectives™ on Stack Overflow

Supabase query with multiple foreign table queries (!inner) not returning any rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related