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.
