Query only specific record by join

[stychu]

Given this structure

whitelist_chats -> chats ( whitelist_chat_id = foreign key))

I want to be able to query only one record that matches botId from the whitelist_chats and also contains in chats specific chat_id

[
  {
    "id": 1,
    "botId": 1442288367,
    "contact": "none",
    "chats": [
      {
        "id": 1,
        "whitelist_chat_id": 1,
        "default": true,
        "link": "some link",
        "chat_id": -1001493989904
      }
    ]
  },
  {
    "id": 2,
    "botId": 1442288367,
    "contact": "none",
    "chats": [
      {
        "id": 2,
        "whitelist_chat_id": 2,
        "default": true,
        "link": "..",
        "chat_id": 1737212793
      {
        "id": 3,
        "whitelist_chat_id": 2,
        "default": false,
        "link": "zz",
        "chat_id": 1811181668
      }
    ]
  }
]

so i would like to retrieve

{
    "id": 1,
    "botId": 1442288367,
    "contact": "none",
    "chats": [
      {
        "id": 1,
        "whitelist_chat_id": 1,
        "default": true,
        "link": "some link",
        "chat_id": -1001493989904
      }
    ]
  }

So given this query

let { data = [], error } = await supabase
  .from("whitelist_chats")
  .select('*, chats(*)')
  .eq('botId', 1442288367)
  .eq('chats.chat_id', [-1001493989904])

[
{
  "id": 1,
  "botId": 1442288367,
  "contact": "none",
  "chats": [
    {
      "id": 1,
      "whitelist_chat_id": 1,
      "default": true,
      "link": "some link",
      "chat_id": -1001493989904
    }
  ]
},
{
  "id": 2,
  "botId": 1442288367,
  "contact": "none",
  "chats": []
}
]

I am getting both whitelist_chats but second one is just with empty chats array.. I just want the 1 to be exactly matched

[stychu]

This works on SQL UI console. How do I query via JS?

[stychu]

let { data, error } = await supabase
  .from("chats")
  .select("*, chat:whitelist_chats(*)")
  .eq("whitelist_chats.botId", 1442288367)
  .eq("chat_id", -1001493989904)
  .single()

This seem to work but I would like to have it the other way damn... Why the other way around doesnt work as I would like it to....

[steve-chavez]

Hey @stychu,

This is a pending enhancement supabase/postgrest-js#197. Sorry for the confusion caused, for now you can filter the empty arrays client side.

[stychu]

@steve-chavez Thanks for reply. And for the pointer. Appreciate that and looking forward for the fixes 👍