Ecto limit on nested associations

I have some troubles making a query with Ecto :

SavedDevice
    - belongs_to Devices
    - belongs_to User
Device
    - has_many SavedDevices
    - has_many DeviceLocations
DeviceLocation
    - belongs_to Devices

I want to load the SavedDevices belonging to a User, with the latest DeviceLocation saved.

This solution works fine :

  def list_pdevices_locations(user, limit \\ 0) do
    location_query = from(dl in DeviceLocation, order_by: [desc: :inserted_at])
    query =
      from(d in ProvisionedDevice,
        preload: [device_info: [locations: ^location_query]],
        limit: ^limit
      )

    if user.is_admin do
      Repo.all(query)
    else
      Repo.all(from(d in query, where: d.user_id == ^user.id))
    end
  end

But it loads every DeviceLocations. This is a problem because there can be thousands of them and I only need the last one.

When I add limit: 1 to location_query, it returns just 1 DeviceLocation for all the Devices and not 1 Devicelocation per Device.

Any idea?

Solution

I don't know if this can be done in a preload, but you can use lateral joins (section 7.2.1.5) to achieve what you want. You will want to create a subquery for the locations:

location_query =
  from(dl in DeviceLocation,
    where: parent_as(:d_info).id == dl.device_info_id,
    # Unique rows by device_info_id
    distinct: dl.device_info_id,
    # Order by inserted at to get the most recent entry
    order_by: [desc: dl.inserted_at]
  )

Then join to it from ProvisionedDevice:

query = 
  from(pd in ProvisionedDevice,
    inner_join: di as assoc(pd, :device_info),
    # Use the same alias as specified in the subquery
    as: :d_info,
    inner_lateral_join: dl in subquery(location_query),
    on: dl.device_info_id == di.id,
    # You can structure this select any way you like
    select: %{device: pd, info: di, location: dl}
  )

Of course this will no longer match the return spec from your example, but you can post process the results to conform to your previous contract (if that is required):

for %{device: device, info: info, location: location} <- Repo.all(query) do
  info_and_location = %{info | locations: [location]}
  %{device | device_info: info_and_location}
end

Either way, the query will give you one device with its info and most recent location all in one query.