1

I am using Supabase and can use the client to retrieve nested data. However, I cannot work out how to select WHERE an Id matches a foreign key on the top level select.

Here is the query:

const {data} = await client
  .from("teacherclasses") 
  .select("classrooms(rooms(roomId:id, roomName:displayname)), schoolclasses(id,reference, 
       totalPupils:totalpupils, subjects(subject:name, 
       subjectAbbreviation:abbreviation))",
  .eq("teacherid", teacherId)
  .eq("classrooms.isdefault", true)
  .limit(1, {foreignTable: 'classrooms'})
  .limit(1, {foreignTable: 'schoolclasses.subjects'})

This query works perfectly (if I use the actual teacherid in teacherclasses.

However the id I get from the frontend is the userid of the teacher, not its primary key.

I tried to change it as follows:

const {data} = await client
  .from("teacherclasses") 
  .select("teachers(userid), classrooms(rooms(roomId:id, roomName:displayname)), schoolclasses(id,reference, totalPupils:totalpupils, subjects(subject:name, 
       subjectAbbreviation:abbreviation))",
  .eq("teachers.userid", teacherId)
  .eq("classrooms.isdefault", true)
  .limit(1, {foreignTable: 'classrooms'})
  .limit(1, {foreignTable: 'schoolclasses.subjects'})

change: teachers(userid) and .eq("teacher.teacherid", teacherId) however, this just returns every row in teacherclasses.

I don't need to select any data from the teacher table, but I get an error, if I do not add the teachers to the .select. I don't want to expose the teacherId, but cannot see how to do this

I am converting this from a dotnet LINQ query and here is a snippet that so you understand what I am doing

var schoolClasses = await _context.TeacherClasses
   .Include(t => t.Teacher)
   .Include(t=> t.ClassRooms)                    
   .Include(s => s.SchoolClass)
   .ThenInclude(s => s.Subject)
              
   .Where(t => t.Teacher.UserId == teacherId)

If anyone can see where I am going wring, I would be grateful

grayson
  • 945
  • 1
  • 11
  • 28

1 Answers1

1

The issue seems to be, that supabase is doing an outer join by default in this case.

Try replacing your join for teachers teachers(userid) in the select with teachers!inner(userid), which will do an inner join and should only fetch the desired rows.

Not sure why this is not documented more prominently in the supabase documentation, as I couldn't find a link for this to the official documentation.

Closest thing I could find is this github issue: https://github.com/supabase/postgrest-js/issues/197

astoiccoder
  • 143
  • 5