Hello,

We recently did some optimizations on a slow query in our system, and found the following article from dba exchange that has a nice syntax for selecting an array of items per row

This ended up being a more performant solution from what we were using previously. Below is a simple example of what we’re trying to do in raw postgresql

select *
from calls c
  left join lateral (
    select array (
        select cr.id
        from call_recordings cr
        where cr.call_id = c.id
          and cr.s3_key is not null) as call_recording_ids) as call_recordings on true
limit 10;

As far as I can tell there isn’t an easy way to do this in Ecto. The closest translation to Ecto I’ve got so far is this

  def test_select_array do
    call_recordings_query =
      from cr in Recording,
        select: %{
          call_recording_ids:
            fragment(
              "(SELECT ARRAY(SELECT cr.id FROM call_recordings cr WHERE cr.call_id = ? AND cr.s3_key IS NOT NULL))",
              parent_as(:call).id
            )
        }

    from(c in Call,
      as: :call,
      left_lateral_join: cr in subquery(call_recordings_query),
      limit: 10,
      select: %{
        call_id: c.id,
        call_recording_ids: cr
      }
    )
  end

But even that isn’t quite right, and it’s janky. Looking for any ideas/guidance on this. Maybe this is something Ecto supports without a fragment and I’m not aware of it, or could support?

– Will