Supabase nested join table query issue

Question

I have 2 tables

users: {
   id, 
   name
}

friends: {
   id,
   requestSentBy: relation(users_id), 
   requestSentTo: relation(users_id)
}

and what i want to achieve is get the user table with all friends table joined wherever his id is in either requestSentBy or requestSentTo table using supabase

My current query looks like this

const { data, error } = await supabase
          .from('users')
          .select(
            '*, friends!friends_requestSentBy_fkey(*)'
          )
          .eq('id', user?.id);

But this would exclude rows where requestSentTo has user id

Edited

I want to query the friends table as a join to my users profile data So the output would look something like this

{
  "data": [
    {
      "user_id": 1,
      "name": "John Doe",
      "friends": [
            {
                  "friend_id": 1,
                  "requestSentBy": 1,
                  "requestSentTo": 2,
            },
            {
                  "friend_id": 2,
                  "requestSentBy": 4,
                  "requestSentTo": 1,
            }
       ],
    }
  ],
  "status": 200,
  "statusText": "OK"
}

In hint i got this as a response:

"Try changing 'friends' to one of the following: 'friends!friends_requestSentBy_fkey', 'friends!friends_requestSentTo_fkey'. Find the desired relationship in the 'details' key."

The following query works

const { data, error } = await supabase
          .from('users')
          .select('*, posts(*), friends!friends_requestSentBy_fkey(*)')
          .eq('id', pathname.split('/')[2])
          .single();

But only returns those friends whom the user had sent request

Answer 1

Here's the solution.

const { data, error } = await supabase
   .from('users')
   .select('*, to:friends!friends_requestSentTo_fkey(*), sent:friends!friends_ requestSentBy_fkey(*)')

Found the solution here Github Question

Answer 2

That query doesn't look correct. Let me give an example for you:

This is your tables:

CREATE TABLE users (
   user_id INT PRIMARY KEY,
   name VARCHAR(255)
);

CREATE TABLE friends (
   friend_id INT PRIMARY KEY,
   requestSentBy INT,
   requestSentTo INT,
   FOREIGN KEY (requestSentBy) REFERENCES users(user_id),
   FOREIGN KEY (requestSentTo) REFERENCES users(user_id)
);

Note: requestSentBy and requestSentTo are linked to user_id in the users table.

Your query should be like this:

const { data, error } = await supabase
  .from('friends')
  .select(`
    friend_id,
    from:requestSentBy(name),
    to:requestSentTo(name)
  `)

Output:

{
  "data": [
    {
      "friend_id": 1,
      "from": {
        "name": "Deep Shetye"
      },
      "to": {
        "name": "Emre"
      }
    }
  ],
  "status": 200,
  "statusText": "OK"
}

More detail in Supabase Docs