Querying nth table by joining n-1 tables.

[KamaniBhavin]

Improve documentation

Link

https://supabase.com/docs/reference/javascript/select

Describe the problem

I recently encountered a scenario where I needed to join three tables - product, supplier, and manufacturer - and filter the results based on a column in the manufacturer table.

schema

Product Table:
product_id (Primary Key)
supplier_id (Foreign Key to Supplier Table)

Supplier Table:
supplier_id (Primary Key)
manufacturer_id (Foreign Key to Manufacturer Table)

Manufacturer Table:
manufacturer_id (Primary Key)
max_capacity

However, I found the documentation to be inadequate for my needs. While it provided guidance for querying a single foreign key-constrained table, it didn't offer any information on how to accomplish this with multiple nested tables in the same format.

Describe the improvement

To assist users facing similar issues, I suggest adding an example to the documentation. This would enable them to efficiently join multiple nested tables in the desired format and filter the results according to their requirements.

      db
       .from("products")
        .select(`
            product_id,
            suppliers (
                supplier_id,
                manufacturers (
                    max_capacity
                )
            )
        `)
        .eq("suppliers.manufacturers.max_capacity", maxCapacity)

Additional context

It is also applicable for n nested tables.

[KajSzy]

I think there is a bug, I tried filter results based on nested foreign table key, but without passing !inner results were not filtered out properly

db.from("products").select(`
   product_id,
      suppliers (
         supplier_id,
            manufacturers!inner(
               max_capacity
            )
         )
    `)
    .eq("suppliers.manufacturers.max_capacity", maxCapacity)

[steve-chavez]

@KajSzy By default all nested objects are LEFT JOINed, so that is actually expected behavior. As you mention !inner is needed for a deep filter.

Internally we've been discussing a supabase-js abstraction for nested objects (a join method). This could default to !inner for preventing confusion.

[KajSzy]

I think it can be left as is (no abstraction needed), but I covering this topic (querying foreign tables and filtering through foreign columns) would be extremly helpful.
I've spent like hour or so just browsing GitHub issues and discussions.
Also selecting many-to-many table omitting joining table is covered only in Querying joins and nested tables

Overall I think that @supabase/supabase-js is working great but have many magic moments where things are so easy to implement but it's hard to find documentation on it