How to join multiple models in on ecto query

Questions / Help
1

I have 3 models as Vendor , VendorModel and Camera . I am doing this query to get VendorModels .

query = from vm in VendorModel,
          join: v in Vendor, on: vm.vendor_id == v.id,
          where: like(fragment("lower(?)", vm.name), ^("%#{search}%")),
          where: like(fragment("lower(?)", v.name), ^("%#{search}%"))
models = query |> add_sorting(column, order) |> preload(:vendor) |> Evercam.Repo.all()

I am appending order_by through a method add_sorting which only adds up the order_by such as

  defp add_sorting(query, "exid", order) do
    from [vm, _v] in query,
      order_by: [{^sort_order(order), vm.exid}]
  end

After getting all models I am doing Enum.reduce to format an object on of all models. On each VendorModel there is an association with Camera , And I am getting a count for each model referenced in cameras such as

  Enum.reduce(display_start..index_end, [], fn i, acc ->
    model = Enum.at(models, i)
    vm = %{
      vendor_exid: model.vendor.exid,
      exid: model.exid,
      camera_count: Camera |> where(model_id: ^model.id) |> Evercam.Repo.all() |> Enum.count(),
    }
    acc ++ [vm]
  end)

this always ran another query for each model to get the count of cameras, Is there any possibility to add this count query to above the first query. And it also supports order by?

I have read about subquery and group_by but I couldn’t make it work.

PS: Already tried stackoverflow for this question: https://stackoverflow.com/questions/54510058/how-to-join-multiple-models-in-on-ecto-query-ecto

2

Depending on the complexity and size of your dataset, I’m thinking of 2 possible solutions:

  1. select a count fragment in the VendorModel query.

  2. join on the Camera association in the query and reduce the association to a simple count in your Enum.reduce/3

1 Like
3

any example?

4

in PostgreSQL its like

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count 
  FROM vendor_models
  INNER JOIN vendors as v ON vendor_models.vendor_id = v.id 
  INNER JOIN cameras ON vendor_models.id = cameras.model_id
  GROUP BY vendor_models.id, vendor_models.name, v.name
  ORDER BY v.name asc

But how it will be in ecto I have no idea

5 
from(vm in VendorModel,
  join: v in assoc(vm, :vendor),
  join: c in assoc(vm, :cameras),
  preload: [
    vendor: v,
    cameras: c
  ]
)

Would preload your associations in a the Ecto.Schema. This might be overkill for a simple count, but could get you going without the 1+N queries…

I never had to combine sub-schema like your use case, but it looks like there are examples in the Ecto.Query.group_by/3 documentation.

1 Like
6

Final one get to this

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count 
  FROM vendor_models
  INNER JOIN vendors as v ON vendor_models.vendor_id = v.id 
  INNER JOIN cameras ON vendor_models.id = cameras.model_id
  WHERE lower(vendor_models.name) like lower('%ax%') OR lower(v.name) like lower('%ax%')
  GROUP BY vendor_models.id, vendor_models.name, v.name
  ORDER BY v.name asc

but in Ecto. no luck yet

7 
SELECT vm.*, v.name as vname, count(cameras.id) as count 
  FROM vendor_models as vm
  INNER JOIN vendors as v ON vm.vendor_id = v.id 
  INNER JOIN cameras ON vm.id = cameras.model_id
  WHERE lower(vm.name) like lower('%ax%') OR lower(v.name) like lower('%ax%')
  GROUP BY vm.id, v.name
  ORDER BY v.name asc

how can we convert this to Ecto?

8

It’s pretty direct, what specific part of the expressions are you having trouble with so we can clarify? :slight_smile:

2 Likes
9

as I already mentioned in the question: I have come this far

query = from vm in VendorModel,
          join: v in Vendor, on: vm.vendor_id == v.id,
          where: like(fragment("lower(?)", vm.name), ^("%#{search}%")),
          where: like(fragment("lower(?)", v.name), ^("%#{search}%"))
models = query |> add_sorting(column, order) |> preload(:vendor) |> Evercam.Repo.all()

what I am unable to figure out is: How I can get count in Ecto. and also how I can do the grouping in ecto.

10

For an example look here: Order By Association Count within a Dynamically Built Query in Phoenix / Ecto - #2 by peerreynders

Now I can’t be sure without a data source to play with - but I suspect that the preload may be getting in your way. If that is the case I’d fall back to an Ecto.query.select/3 with a map explicitly identifying all the necessary fields - that should more cleanly align with the SQL you have shown.

Once you have a working Ecto query you could try to use a preload query to get those preloads back in there.

4 Likes
11

It looks like you’ve just about got it. so you need to join your cameras table, add your group_by and then a projection (select) so you get your count of cameras along with your model data. You can get rid of your preload of vendor since all you want is your vendor name and you can get that in your select.

join: c in Camera, on: vm.id = c.model_id,
group_by: [vm.id, v.name],
select: {vm, v.name, count(c.id)}

Each row will have a 3 element tuple with your VendorModel, vendor name, and count of cameras.

Something like that. Its easier to work out with the actual db and an iex prompt in front of me rather than a text box on elixirforum but hopefully you get the idea.

3 Likes
12

I see in a slightly earlier reply you said you only wanted:

vendor_models.id, vendor_models.name, v.name, count(cameras.id)

so in that case I’d do:

select: %{id: vm.id, model_name: vm.name, vendor_name: v.name, count: count(cameras.id)}

its probably convenient to have each returned element in your returned data in a Map.

3 Likes