How to pass an array parameter into a live query?

[thruflo]

I have a useLiveQuery where I'm trying to use a WHERE IN clause. I can make it work by hacking in a list of dollar signs:

const placeholders = parentIds.map((_, i) => `$${i + 1}`).join(', ')
const results = useLiveQuery(`
    SELECT value FROM items
      WHERE parent_id IN (`${placeholders}`)
      ORDER BY id
  `, parentIds)

Although beware that you need more boilerplate if the parentIds can ever be empty, cough #544.

I can also do it by padding:

const paddedIds = [...parentIds, ...Array(10 - parentIds.length).fill(-1)]
const results = useLiveQuery(`
  SELECT value FROM items
    WHERE parent_id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
    ORDER BY id
`, paddedIds)

But is there / could there be a more ergonomic way that passes in the array as a single parameter?

[tdrz]

@thruflo

Is this what you had in mind?

      const result = useLiveQuery(`SELECT * FROM test WHERE id = ANY($1)`, [
        [1, 2],
      ])

Added a test: https://github.com/electric-sql/pglite/pull/548/files

[thruflo]

Awesome, so are you telling me it just worked if I just passed an array as a single parameter?! That's obviously what I tried first. Sorry -- I must have had some other issue that confused me into thinking it didn't work 🤦

[thruflo]

Reopening because I'm discovering another quirk here, which is that the liveQuery causes my component to continuously re-render when I use an array param:

export default function Component() {
  
  // This causes an endless re-render loop.
  const itemResults = useLiveQuery(`
    SELECT parent_id, value FROM items
      WHERE parent_id = ANY($1)
      ORDER BY id
  `, [[1, 2]])

  // // For comparison, this works fine -- no endless re-rendering
  // const itemResults = useLiveQuery(`
  //   SELECT parent_id, value FROM items
  //     WHERE parent_id = $1
  //     ORDER BY id
  // `, [1])

  useEffect(() => {
    console.log('itemResults changed')
  }, [itemResults])

  return null
}

I would guess that it's because the array param is being compared by identity rather than value. If I put the param in a state variable and only update it after a compare by value the re-rending goes away:

  const [ queryParam, setQueryParam ] = useState([-1])

  useEffect(() => {
    if (JSON.stringify(itemIds) !== JSON.stringify(queryParam)) {
      setQueryParam(itemIds)
    }
  }, [itemIds])

  const results = useLiveQuery(`
    SELECT parent_id, value FROM items
      WHERE parent_id = ANY($1)
      ORDER BY id
  `, [queryParam])

[samwillis]

I had a chat with @msfstef on this, It's one of those areas that React can have many foot guns...

In general when an array or an object is passed as a dependancy to a React hook it is compared with the previous value by reference, rather than value. Thats what we are doing at the moment, and the reason for the render loop.

But this is a common pattern with our hook, as @thruflo found, and we want to make the default experience better. The alternatives are:

1. A shallow comparison on array and object params. First check is same reference, then do a shallow comparison is its changed. Relatively cheap to run on each render, removes the issue for the majority of users.
2. A deep comparison on array and object params. This is mush more expensive as you can have deeply reseted objects, that could even self reference...

We think the best option is 1, so do a shallow comparison on array and object params. Then document this better with an example of memoization for multi-dimensional arrays.

[thruflo]

is there an alternative mechanism to indicate when to re-run the query? Like the ability to provide an arbitrary dependency? I ask because then you could use the common pattern of turning the array/object into a value (e.g.: JSON.stringify) and passing the value as a discriminator. That would allow the user to control re-querying whilst still providing an array as the actual parameter value for the query.