Flutter Supabase Query matches one to many relationship the wrong way

Question

I am building a Flutter App with Supabase as a backend and a chat feature.

In Supabase I have the Table chat_messages:

id uuid
content varchar
sent_by uuid (fk -> users.id)
quoded_message_id uuid (fk -> chat_messages.id)

The quoted_message_id is s self-referencing foreign key, so, as the name says, I can quote messages like e.g. in WhatsApp.

I query the chat messages with

final result = await Supabase.instance.client.from('chat_messages')
.select('*,quoted_message:chat_messages(*)');

This should return a list of the table chat_messages, with a nested chat_message id quoted_message_id is not null. To give you an example: Let's say I have two columns in that table:

id | content | sent_by | quoted_message_id '123' | 'Test 1' | | null '456' | 'Test 2' | | '123'

The result I expect when I query that Table:

[
  {
    'id': '123',
    'content': 'Test 1',
    'sent_by': <userId>,
    'quoted_message_id': null,
    'quoted_message': [],
  },
  {
    'id': '456',
    'content': 'Test 2',
    'sent_by': <userId>,
    'quoted_message_id': '123',
    'quoted_message': [
      {
        'id': '123',
        'content': 'Test 1',
        'sent_by': <userId>,
        'quoted_message_id': null
      }
    ]
  }
]

But the result I get is like this:

[
  {
    'id': '123',
    'content': 'Test 1',
    'sent_by': <userId>,
    'quoted_message_id': null,
    'quoted_message': [
      {
        'id': '456',
        'content': 'Test 2',
        'sent_by': <userId>,
        'quoted_message_id': '123'
      }
    ],
  },
  {
    'id': '456',
    'content': 'Test 2',
    'sent_by': <userId>,
    'quoted_message_id': '123',
    'quoted_message': []
  }
]

So the quoted messages are exactly the other way around. I tried these methods, which both did not work. The first one had the same result, the second one threw an error.

final result = await Supabase.instance.client.from('chat_messages')
.select('*,quoted_message:chat_messages!left(*)');
final result = await Supabase.instance.client.from('chat_messages')
.select('*,quoted_message:chat_messages!<fk_name>(*)');

Answer 1

If you want to flip the way messages are queried, try the following:

final result = await Supabase.instance.client.from('chat_messages')
  .select('*,quoted_message:quoded_message_id(*)');