CODEWITHSUNDEEP
elixirecto
guy1997
guy1997

Reputation: 286

How to make subqueries in Ecto?

I have two tables; listings and bids. I want to list all listings on which the highest bid is above 1 and below 10 using Ecto. See the code below for more information about the schema and query.

database schema

listings
  id
  name


bids
  listing_id
  amount

program.ex

Repo.all(
  from l in Listing,
  where: (SELECT MAX(amount) FROM bids WHERE listing_id = l.id) > 1 and
         (SELECT MAX(amount) FROM bids WHERE listing_id = l.id) < 10)

How would one go about this?

Upvotes: 2

Views: 574

Answers (2)

Mike Buhot
Mike Buhot

Reputation: 4885

As a group_by / having query:

Repo.all(
  from l in Listing,
  join: b in assoc(l, :bids),
  group_by: l.id,
  having: (max(b.amount) > 1) and (max(b.amount) < 10)
  select: l)

Upvotes: 6

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 120990

Ecto.Query.API.fragment/1 comes to the rescue:

join: l in Listing
where: fragment("
  ((SELECT MAX(amount) FROM bids WHERE listing_id = ?) > 1 and
   (SELECT MAX(amount) FROM bids WHERE listing_id = ?) < 10)
", l.id, l.id)

Upvotes: 2

Related Questions