Supabase how to query the same table twice-CodePudding

I have been experimenting with Supabase recently and trying to make a twitter like 'replying to @user' comment feature.

So I have a database ERD attached below for reference, As you can see each comment has a userid and also a 'replyingTo' which also stores the userid of the comment which is being replied to.

Now I'm able to query individually between these two tables, So I can get the user of comment along with the comment very easily but however, When I'm trying to fetch the user profile of the comment creator and the profile of replyingTo, I get the following error -

Could not embed because more than one relationship was found for 'Comments' and 'profiles'

I'm not too experienced at PostgreSQL so I'm not sure how to do something of this scope, This is the following code I'm using currently which is giving me the error I have described above.

                 const { data, error } = await supabase
                .from('Comments')
                .select('ReplyingTo, profiles:profiles(id), profiles:profiles(id)')
                .eq('commentid', cmtid)

My expected outcome is to get the comment, the user profile who created the comment and also the profile of the user who is receiving a reply.

Thank you for your time and patience.

CodePudding user response：

As the error says, the problem is that you have two relationships between profiles and Comments. For this case, you'd need to disambiguate(which foreign key column you want to use for the join) as specified in the PostgREST docs. For the supabase js client, it should be like:

 const { data, error } = await supabase
.from('Comments')
.select('ReplyingTo, profiles1:profiles!userid(*), profiles2:profiles!ReplyingTo(*)')
.eq('commentid', cmtid)