Can't seem to cross intermediary table

I apologize for my ignorance, but SQL is not my strong point.

My tables:

event {
    id: UUID
}

category: {
    id: int,
    name: text
}

criterion: {
    question: text
    category_id: FK -> category
}

event_category {
    event_id: FK -> event
    category_id: FK -> category
}

event {
    id: UUID
}

category: {
    id: int,
    name: text
}

criterion: {
    question: text
    category_id: FK -> category
}

event_category {
    event_id: FK -> event
    category_id: FK -> category
}

So, each category has various criteria. And an event can have various categories. What I can't seem to do is answer the question: "For this event, what are all the criteria that apply?"

This is what I have so far. I can get to the category, but I can't get across the intermediary table:

let { data, error, status } = await supabase
  .from('criterion')
  .select('category_id, question, category_id!inner(name)')
  .eq('category_id.name', 'Event');

let { data, error, status } = await supabase
  .from('criterion')
  .select('category_id, question, category_id!inner(name)')
  .eq('category_id.name', 'Event');

I know my from table needs to be criterion, and I know I need go to up from criterion to category (with an inner join, and I've made this happen). I know I also need to go across event_category, but I can't seem to work out the supabase query for that. If anyone could provide advice, it would be greatly appreciated.

Can't seem to cross intermediary table

I apologize for my ignorance, but SQL is not my strong point.

My tables:

event {
    id: UUID
}

category: {
    id: int,
    name: text
}

criterion: {
    question: text
    category_id: FK -> category
}

event_category {
    event_id: FK -> event
    category_id: FK -> category
}

event {
    id: UUID
}

category: {
    id: int,
    name: text
}

criterion: {
    question: text
    category_id: FK -> category
}

event_category {
    event_id: FK -> event
    category_id: FK -> category
}

let { data, error, status } = await supabase
  .from('criterion')
  .select('category_id, question, category_id!inner(name)')
  .eq('category_id.name', 'Event');

let { data, error, status } = await supabase
  .from('criterion')
  .select('category_id, question, category_id!inner(name)')
  .eq('category_id.name', 'Event');

Can't seem to cross intermediary table

Similar Threads

Can't seem to cross intermediary table

Similar Threads

Similar Threads

Similar Threads