Foreign Key Array

[kierangilliam]

Is it possible to create a foreign key array?

Using the following setup, I get an error:

(In console)

code: 400
message: "foreign key constraint "block_foreign_array_fkey" cannot be implemented"

Is this possible to do with Supabase?

[steve-chavez]

Hi Kieran,

Is this possible to do with Supabase?

Not possible, but might be in the future. Foreign Key Arrays is a work in progress in PostgreSQL.

Alternatively, you can do the usual approach of creating a junction(or join) table for this. Check this SO question.

Doing a many-to-many embedding then will work transparently with supabase-js.

[carllippert]

How can I filter by a nested relationship then?

I moved to text array because I was not able to filter my table by its related table as mentioned in docs.
Is two .eq not allowed?

ie.

table1 locations { location_id, public, etc }
table2 tags { tag_id, name, etc }
table 3 location_tags { location_id, tag_id, etc }

query locations ->

.select("*, location_tags ( *, tag_id ( tag_id, * ) ) )"
.eq("public", true)
.eq('location_tags.tag_id.tag_id', <filter_key>)

I would get all the locations no matter what relational results where found inside the location_tags table.

Any help appreciated!

[soedirgo]

@carllippert can you try the new !inner filter? Something like:

const { data, error } = await supabase
    .from('locations')
    .select('*, tags!inner(*)')
    .eq('public', true)
    .eq('tags.tag_id', ...)

[carllippert]

at first I thought it wasn't working. But then I figured it out! thnx @soedirgo!

   let query = supabase
      .from("locations")
      .select("*, location_tags!inner (*, tag_id!inner ( * ))")
      .eq("public", true)
      .limit(5);

    if (filters.length > 0) {
      filters.forEach((filter) => {
        query.eq('location_tags.tag_id.tag_id', filter.tag_id);
      }); 
    }

    const { data, error } = await query;

One thing/bug? I did notice is that you can't use typescript the same way I have previously when doing nested filters.
It gets angry with the query.eq(arg1, arg2) arg1 not being recognized column name.

i.e.

 let query = supabase
     .from<definitions['locations']>("locations")

[soedirgo]

Ah, yeah... that wasn't accounted for when the types were first designed. Filing this on supabase/supabase-js#170.