Search code examples
databasepostgresqlfluttersupabasesupabase-database

How to query joins from multiple table using Flutter SupaseClient

This is my schema I am trying to fetch a list of events from the table along with the host info and the list of attendees by matching events.host_id with user.user_id and events.event_id with 'attendees.user_id`

Schema
user(user_id, name, email)
events(event_id,event_name, host_id) // host_id is the user_id of host
attendees(event_id,user_id)

Also, note that both columns of attendees are foreign keys associated with events.event_id and user.user_id respectively.

Here is what I have tried which works partially for me

final response = await _supabase
    .from('events')
    .select('*, $user!inner(*)')
    .order(CREATED_AT_COLUMN, ascending: ascending)
    .execute();

This returns the following desired output (missing List of attendees)

enter image description here

This returns data from attendees with results from users and events nested in the attendees table.

final response = await _supabase
    .from(attendees)
    .select('*, $user!inner(*), $events!inner(*)')
    .execute();

enter image description here

Expected output

{
event_id,
..
..

host: {
   id: host_id,
   name: host_name
   ...
   },
attendees: [
  {id: user_idevent_id: event_id},
  {id: user_idevent_id: event_id}
  ...
  ]
}

So I have also tried

 final response = await _supabase
     .from(events)
     .select('*, $user!inner(*), $attendees!inner(*)')
     .execute();

My guess is perhaps the way my tables are structured is incorrect.

Solution

  • Your table definition is perfect!

    Your final query should look something like this:

    await supabase
  .from('events')
  .select('*, host:users!event_host_fkey(*), attendees(*, users(*))');

    The way I got the weird users!event_host_fkey thing in the middle was by looking at the error message of the following query:

    try {
  await supabase
    .from('events')
    .select('*, host:users(*), attendees(*, users(*))');
} catch(error) {
  // read the error message here.
  print(error);
}

    The events table and users table have two relationships right now, one through the host_id foreign key, and another though the attendees join table. Supabase knows this, and will ask you to provide additional information on which association to use for the query. Folllowing what the error message will give you the original query that I provided at the top! I hope this helps!