In real world, most of our "pattern" become technical debt as codebase grows very fast. My solution is just simply using raw sql query. Hide complexity of query behind another complex "ActiveRecord" is not the solution.
The benefit is separation of concern when your team has a SQL specialist. And no more code technical debt as your project scales.
I think Ecto has the best of both worlds (a useful abstraction over SQL and is low level enough) and the right approach:
"Ecto is a toolkit for data mapping and language integrated query for Elixir."