-2

I have 2 tables

  1. Department(id, department_name)
  2. Programs(id, program_name, department_id)

Data flow diagram

dept_id in Programs is the foreign key from Department table

I want to join department_id in Program table and id in Department table I'm unable to do it in Flutter supabase supabase_flutter: ^0.2.12. Please help me out Thanks

Edit: I want to perform this action from the front end. (Flutter)

CREATE TABLE Departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE Programs (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER,
  FOREIGN KEY (dept_id) references Departments(id)
);

INSERT INTO Departments VALUES (1, 'DEPT_A');
INSERT INTO Departments VALUES (2, 'DEPT_B');
INSERT INTO Programs VALUES (1, 'PROG_A', 1);
INSERT INTO Programs VALUES (2, 'PROG_B', 1);
INSERT INTO Programs VALUES (3, 'PROG_C', 2);
INSERT INTO Programs VALUES (4, 'PROG_D', 2);

SELECT Departments.name, Departments.id, Programs.name, Programs.id
FROM Departments 
INNER JOIN Programs
ON Departments.id = Programs.dept_id;
Akash Punagin
  • 17
  • 1
  • 6
  • 2
    "I'm unable ..." Then what are you able to do? (Perhaps you can query each table separately?) What have you tried for joining? (Did you get an error rejection of some kind?) – AntC Mar 29 '22 at 21:10
  • Hi @AntC, I couldn't find any documentation to join tables in **supabase_flutter: ^0.2.12** or in the supabase website. I could query each table separately but I want to join the tables and query the database as it is relational database – Akash Punagin Mar 30 '22 at 12:19

3 Answers3

1

If your database has relationships, you can query related tables too.

final res = await supabase
  .from('countries')
  .select('''
    name,
    cities (
      name
    )
  ''')
  .execute();

This is directly from the docs: Supabase Dart Docs

Tim Kariuki
  • 633
  • 8
  • 17
0

you can set up a foreign key relation in supabase from within your table editor by pressing edit on the column you want to add one to.

enter image description here

KeoooDev
  • 528
  • 4
  • 14
0

For example, you have two tables "users" and "clothes".

And you have in "users" column "clothe_id" as forieng key for column "id" in "clothes".

You can use this code to get tables.

final List data = await AppSupabase.client
        .from('users')
        .select('name, clothes!users_clothe_id_fkey!inner(*)')
        .eq('name', 'Admin');

// [{name: Admin, clothes: {id: 1, title: Costume, level: 4, ...}}]

Also you can filter, for example, by column "level" in "clothes":

final List data = await AppSupabase.client
        .from('users')
        .select('name, clothes!users_clothe_id_fkey!left(level)')
        .gte('clothes.level', 2);

//[{name: Jane, clothes: null}, {name: Admin, clothes: {level: 4}}] 


final newList = data
        .where((element) => element['clothes'] != null)
        .toList();

//[{name: Admin, clothes: {level: 4}}]
Шахзод
  • 121
  • 2
  • 3