Ecto: sql subqueries

[yltsrc]

is there any way to create query with ecto like:

select from posts where author_id in (select id from authors where name = 'Jose')

something which will look like:

query =
  Author
  |> where([a], a.name = "Jose")
  |> select([a], a.id)
Post
  |> where([p], p.id in ^query)

I wish to receive ecto 2.0 release as early as possible, so please do not bring it into current release if you will plan to do something like this

[josevalim]

Have you tried the Ecto v2.0.0-rc.5 release? It may be supported there and,
if not, you can always use fragments.

On Wednesday, June 15, 2016, Yura Tolstik [email protected] wrote:

is there any way to create query with ecto like:

select from posts where author_id in (select id from authors where name = 'Jose')

something which will look like:

query =
Author
|> where([a], a.name = "Jose")
|> select([a], a.id)Post
|> where([p], p.id in ^query)

I wish to receive ecto 2.0 release as early as possible, so please do not
bring it into current release if you will plan to do something like this

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/elixir-lang/ecto/issues/1489, or mute the thread
https://github.com/notifications/unsubscribe/AAAlbu6zuN9oUYQkR8kwsLaHlW6UweMOks5qL8YTgaJpZM4I2Kia
.

_José Valim_
www.plataformatec.com.br
Skype: jv.ptec
Founder and Director of R&D

[michalmuskala]

I think the example query can be converted to an equivalent one using joins:

from p in Post,
  join: a in Author, on: a.id == p.author_id,
  where: a.name == "Jose"

Alternatively with a proper association in the schema, it gets even simpler:

from p in Post,
  join: a in assoc(p, :author),
  where: a.name == "Jose"

[yltsrc]

@michalmuskala if I need to join 2 tables to posts and 3 tables to authors, then it may not be a good idea to do 5 joins, just to get results I need
I can't proof, but I think subquery will work faster, then one select with 5 joins
and now I had to do 2 queries to repo and cast types to insert them to the result query

[yltsrc]

@josevalim ecto 2.0.0-rc5 results on another example:

** (FunctionClauseError) no function clause matching in Ecto.Type.of_base_type?/2
      (ecto) lib/ecto/type.ex:627: Ecto.Type.of_base_type?({:in, :binary_id}, #Ecto.Query<from p in EctoTest.Price, join: a0 in EctoTest.Apartment, on: a0.id == p.apartment_id, join: a1 in EctoTest.Address, on: a1.id == a0.address_id, where: a1.city == "Warsaw", where: p.date == ^%{__struct__: Ecto.Date, day: 15, month: 6, year: 2016}, select: p.id>)
      (ecto) lib/ecto/type.ex:565: Ecto.Type.try_cast/2
      (ecto) lib/ecto/type.ex:555: Ecto.Type.cast/2
    (elixir) lib/enum.ex:1473: Enum."-reduce/3-lists^foldl/2-0-"/3

[josevalim]

Oh, so it does not work. :D You need to fragments then:

where: fragment("author_id in (select id from authors where name = 'Jose')")

[yltsrc]

@josevalim thanks, I am really appreciate how you deal with issues
it will be the way to deal with it, but I am wondering if there any plans for the feature like this one in the next releases of ecto?

[josevalim]

Thank you! I have added this to #1479 since they require basically the same amount of work in the planner.