Is a 3-level join possible?

[99power]

My setup is pretty simple. I have 4 tables and they're related only 2 layers deep, but I want a query that goes deeper. Here's what I mean:

category (id, name) -- stands alone
postmeta (id, categoryid, postid) -- joins posts and category
posts (id, title) -- stands alone
playlistitems (id, postid) -- is related to posts

By the names you can figure out the relations, primary keys are set on all overlapping ids. If I start the query from posts it all works perfectly.
.from('posts').select('title, category(name), playlistitems(id), postmeta('id)

Now things get deeper when I'm starting from categories (without playlist) as it's a 2-level deep join:

category -> postmeta -> posts
.from('category').select('name, posts(title) postmeta('id)

And here's where I'm stuck, when going to the third level adding playlistitems I get an error.

category -> postmeta -> posts -> playlistitems
.from('category').select('name, posts(title) postmeta('id), playlistitems(id)

My error points at missing primary keys, but I've double checked and it's not the case, so this is either simply not possible or a bug? Can someone clear that up for me please?

Searched for a foreign key relationship between 'categories' and 'playlistitems' in the schema 'public', but no matches were found.

[99power]

Solved. Going to leave this here in case someone else faces the same issue.
The "trick" is to embrace the deeper level dependency in brackets of its parent, like so:

.from('category').select('name, posts(title, playlistitems(id)) postmeta('id)

Thanks to Copilot for this one, couldn't answer it even after questioning it a hundred times, but suggested it on another query.

[moeelbadri]

HOLY SHIT MAN, i wasted 2 month lmfao while looking for this , thanks alot for the answer
and u can use now !inner inner join
const {data,error}= await supabase.from("booking").select("bookingCabins!inner(cabins!inner(type)),start_date,end_date").eq("bookingCabins.cabins.type",id??0)
that is so perfect

[aybeedee]

Thanks for sharing! Additionally, you can also extract type definitions and operate on nested data with ease when performing such multi-level joins in TypeScript with something like:

import { QueryData } from "@supabase/supabase-js";

let orders = null;
try {
  const query = supabase
    .from("order")
    .select("*, order_item!inner(*, product!inner(*))")
    .eq("user_id", user.id);
  
  const { data, error } = await query;
  
  orders = data as QueryData<typeof query>;
  
} catch (error) {
  // handle error
}

orders?.forEach((order) => {
  const thisOrder = order;
  order.order_item.forEach((orderItem) => {
    const thisOrderItem = orderItem;
    const thisOrderItemsProduct = orderItem.product;
  })
});