Supabase query go get parent record with at least one child record?

Question

I got two tables, customer (parent) and order (child) with a foreign key relation. Is there a query using the javascript SDK to get all parent records that have at least one child record? Using prisma, the query would look something like this:

const result = await prisma.customer.findMany({
  where: {
    order: {
      some: {},
    },
  },
})

What would be the equivalent query in supabase?

Are you using `supabase-js` or `prisma`? For supabase, you can use this: https://stackoverflow.com/a/75389023/2188186 . If using prisma, then the supabase tags are irrelevant. — Mansueli, Apr 26 '23 at 13:58
No, I'm not using prisma. I'm using supabase. I just wanted to show what the query would look like in prisma as an example in case if was unclear. — erikvm, Apr 26 '23 at 18:54

score 0 · Answer 1 · answered Apr 26 '23 at 21:49

You won't be able to do this without fetching the data from the child table. But if that's okay for your case. Then you can do:

const {data:data , error} = await supabase
.from('customer')
.select('*, order!inner(*)')

Example result:

[
  {
    "customer_name": "Contoso Café",
    "id": 1,
    "orders": [
      {
        "id": 1,
        "created_at": "2023-03-17T07:43:05.116581+00:00",
        "user_id": "9fcae5ae-3f19-43d9-b40b-219c3d524064",
        "customer_id": 1
      },
      {
        "id": 2,
        "created_at": "2023-03-17T07:43:43.466414+00:00",
        "user_id": "39f7d9f2-84b6-4e54-8095-e7278cfee573",
        "customer_id": 1
      },
      {
        "id": 3,
        "created_at": "2023-03-17T07:44:10.524191+00:00",
        "user_id": "8f1eb286-01dd-4412-ab90-5a4d20be04f1",
        "customer_id": 1
      }
    ]
  },
  {
    "customer_name": "Contoso Vodka",
    "id": 3,
    "orders": [
      {
        "id": 4,
        "created_at": "2023-03-17T07:45:49.59218+00:00",
        "user_id": "2ef9d6a2-8fc8-4adb-bad3-926ba30e8abc",
        "customer_id": 3
      },
      {
        "id": 5,
        "created_at": "2023-03-17T07:45:58.706093+00:00",
        "user_id": "897064a9-8a57-4b3d-8468-ca0d35c72d44",
        "customer_id": 3
      }
    ]
  }
]

If you want just the numbers, then you could do:

const {data:data , error} = await supabase
.from('customer')
.select('*, order!inner(count)')

Alternatively, you can just count the numbers then filter them out on the response:

const {data:data , error} = await supabase
.from('customer')
.select('*, orders!inner(count)')

const filteredData = data.filter(team => {
  return team.team_members[0].count > 0;
});
console.log(JSON.stringify(filteredData,null,2));

Supabase query go get parent record with at least one child record?

1 Answers1