Ecto advanced selects

Questions / Help
,
1

Hello, please, can i get help or assistance in solving this problem in ecto.

I often use SQL with this type of selects:

SELECT a.data FROM table AS a
WHERE a.id = (SELECT b.id FROM another_table AS b WHERE b.data=something ORDER_BY b.order DESC LIMIT 1)

etc.

I know now this can by done using fragments. But in fragment I need to use another_table name as it is in SQL, but not as it is in Ecto.Schema defmodule.

Is there a way to do that type of advanced selects in Ecto or to use Ecto.Schema defmodule name in fragments?

Thanks in advance,
Sincerely and with respect to you
Kurmet

2

Hello @kurmetaubanov and welcome to the forum.

Regarding your question, I would do something like this:

defmodule A do
  use Ecto.Schema

  schema "table" do
    field(:data, :string)
  end
end

defmodule B do
  use Ecto.Schema

  schema "another_table" do
    field(:data, :string)
    field(:order, :integer)
  end
end

defmodule App do
  import Ecto.Query

  def get_results(data) do
    subset_query = from(B, where: data: ^data, order_by: desc: :order, limit: 1)
    query = from(a in A, join: b in subquery(subset_query), on: a.id == b.id)
  
    Repo.all(query)
  end
end

My answer is based on https://hexdocs.pm/ecto/Ecto.Query.html#subquery/2
I did not test the solution personally, but I hope it will help you.

1 Like
3

for now I found the solution by using fragments:

defmodule Table do

 schema "table" do
  field :data :string
 end

end

defmodule AnotherTable do

 schema "another_table" do
  field :data, :string
  field :order, :integer
 end

end

def fetch do

 qry = from a in Table, where: a.data == fragment("SELECT b.data FROM another_table AS b WHERE b.data = ? ORDER BY b.order DESC LIMIT 1", a.data)

end

but I am not satisfied that I should write name of the table “another_table” instead of writing struct name.
Ideally it should be working like this:

 qry = from a in Table, where: a.data == fragment("SELECT b.data FROM ? AS b WHERE b.data = ? ORDER BY b.order DESC LIMIT 1", AnotherTable, a.data) - this doesn't work,

and by this:

 qry = from a in Table, where: a.data == fragment("SELECT b.data FROM ? AS b WHERE b.data = ? ORDER BY b.order DESC LIMIT 1", AnotherTable.__schema__(:source), a.data)

is returning error:

 ** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near "$1", where "$1" is '"another_table"'
4

Hi EskiMag,

This doesn’t work, because I need to fetch many rows with data from “table” and find corresponding latest entered rows in “another table” by those data rows.

Like this, but Ecto doesn’t allow to use subqueries in WHERE clause

defmodule A do
  use Ecto.Schema

  schema "table" do
    field(:data, :string)
  end
end

defmodule B do
  use Ecto.Schema

  schema "another_table" do
    field(:data, :string)
    field(:order, :integer)
  end
end

defmodule App do
  import Ecto.Query

  def get_results() do
    subset_query(data) = from(B, where: data: ^data, order_by: desc: :order, limit: 1)
    query = from a in A, where: b.data == subquery(subset_query(a.data))
  
    Repo.all(query)
  end
end
5

Ecto doesn’t allow subqueries, but you can still emulate your subquery using a join. Unless I’m missing something, @stefanluptak’s solution is what you’re looking for. It should be a total equivalent to your example query.

I had the same issue on my application, where I needed a subquery, and I used a join instead.

6

There’s even an example in the docs on how to convert such a query with subquery in WHERE to a subquery using join here: https://hexdocs.pm/ecto/3.0.8/Ecto.Query.html#subquery/2

2 Likes
7

No it doesn’t work for me.

This is because I need to change subquery for every row, using WHERE clause.

It is written above

qry = from a in Table, where: a.data == fragment(“SELECT b.data FROM another_table AS b WHERE b.data = ? ORDER BY b.order DESC LIMIT 1”, a.data)

The question is, why can’t i use defmodule name instead of table name in fragment

8

I’m not sure you really do:

def fetch do
  subquery = 
    from b in AnotherTable,
      order_by: [desc: b.order],
      group_by: b.data, # this should make it behave like the LIMIT 1 
      select: b.data
  
  from a in Table,
    join: b in subquery(subquery),
    on: a.data == b.data
end
1 Like
9

Thank you for your reply. I will try that soon and report the results.

1 Like