Help with this Ecto query

Questions / Help
1

I suppose this is partially an SQL question, but I’m wondering how to do this in Ecto.

I have two models that look like so:

defmodule App.Order do

  schema "orders" do
    field :title, :string
    field :description, :string
    field :canceled, :boolean, default: false
    belongs_to :owner, App.User
    has_many :attempts, App.Attempt

    timestamps()
  end
  ...
end

defmodule App.Attempt do

  schema "attempts" do
    field :text, :string
    field :accepted, :boolean
    belongs_to :owner, App.User
    belongs_to :order, App.Order

    timestamps()
  end
  ...
end

If possible, I want to craft a single query that gives me all open orders. An order is “open” if all of the following conditions are true:

  • canceled is false.
  • The count of attempts where accepted is true is 0.
  • The count of attempts where text is non-nil and accepted is nil is 0.

So basically, “select all non-canceled orders where none are explicitly accepted, or none have non-nill text and where accepted is nil, meaning the attempter has entered text but the client hasn’t explicitly accepted or rejected yet.” New attempts will have accepted set to nil and no text, meaning they need text from the attempter and are neither accepted nor rejected pending that text.

Is there any way to structure this in a single Ecto query? Or do I need to add an additional field to my model? While a bit obscure, it seems like the above plus a little documentation is enough for now to capture the states an order might be in, but I can’t wrap my mind around the joins and count checks necessary to list orders meeting this criteria. I can start with attempts, then work back to their orders, but I’m then not sure how I’d sort my orders by when they were last updated, which I also want to do. I suppose I could do that in code, but if the order list ever grew large, this would mean pulling them all from the DB and sorting them in code, plus applying pagination as well.

Thanks for any help. Happy to provide additional information if needed.

As an aside, thanks so much for Ecto, Phoenix, and Elixir. Part of why I’m struggling with Ecto and SQL is because I’ve been in the Mongo/NoSQL world for a while. The only frameworks that have captivated me over the last few years are Meteor and other real-time frameworks, and many of them tended toward Mongo/Redis. I didn’t much like Mongo, but I liked the power the frameworks gave me, so I sort of bit my tongue and slowly lost most of my SQL knowledge. Thanks for giving me an environment that pleases both my pragmatic and correctness-appreciating sides. :slight_smile:

1 Like
2

Have you tried already to create plain SQL query to fetch all that things? It’s easier to use Ecto DSL when you have plain SQL. Actually as a bad approach when you don’t have enough time etc I can suggest you to put that plain SQL into fragment. But that’s not the best idea))

query = from t1 in Table,  
join: t2 in fragment("SELECT q.request_id...... "), 
on: ..., 
order_by: etc

Another better solution is to build a View and map it into Ecto model schema. But that will work only if your query doesn’t contain parameters. Otherwise you can try to add a procedure into migration which will return View to you and you can map it. But I haven’t tried this yet.

1 Like
3 
from order in Order,
  join: accepted_attempt in assoc(order, :attempts),
  join: pending_attempt in assoc(order, :attempts),
  where: not order.cancelled,
  where: accepted_attempt.accepted,
  where: not is_nil(pending_attempt.text) and is_nil(pending_attempt.accepted),
  group_by: order.id,
  having: count(accepted_attempt.id) == 0,
  having: count(pending_attempt.id) == 0

The trick is to join twice on the attempts, for each condition we want to realise that relates to the attempts. Let’s break it down:

from order in Order,
  where: not order.cancelled,

This selects all orders that are not cancelled.

from order in Order,
  join: accepted_attempt in assoc(order, :attempts),
  where: accepted_attempt.accepted,
  group_by: order.id,
  having: count(accepted_attempt.id) == 0,

This selects all orders that have the count of accepted attempts equal to zero.

from order in Order,
  join: pending_attempt in assoc(order, :attempts),
  where: not is_nil(pending_attempt.text) and is_nil(pending_attempt.accepted),
  group_by: order.id,
  having: count(pending_attempt.id) == 0

This selects the orders that have the count of pending attempts (not nil text and nil accepted field) is equal to 0.

The final query simply composes all those conditions to create a query where all three are satisfied.
Of course in order to make that query efficiently adding some indexes is required (finding where indexes are needed is beyond the scope of this answer, though).

PS. This is obviously not tested in any way.

6 Likes
4

What an amazingly prompt, detailed answer. Thanks so much! Agreed that indexes are out of scope, and will likely emerge once I’ve run this over actual data and optimized. But, at the very least, you’ve given me a great starting point to play with and test myself.

Thanks again.

2 Likes
5

I hit a minor wrinkle.

I tweaked the above query to select: count(o.id) so I can get a count of the number of open orders. Unfortunately, this returns nil. I didn’t yet test the full query because my initial functionality just returns this count, but I thought adding the select would be a quick way to see if I was on the right track.

So using query composition, I began building up a query, running it, then adding a new clause until it failed. I loaded an order in my database that is not canceled and has 0 attempts of any kind, so it should define as open.

q = from o in Order, select: count(o.id)
Repo.one(q) # 1
q = from o in q, where: o.canceled == false
Repo.one(q) # 1
q = from o in q, join: accepted_attempt in assoc(o, :attempts)
Repo.one(q) # 0
# [debug] QUERY OK source="orders" db=8.5ms queue=0.1ms
# SELECT count(o0."id") FROM "orders" AS o0 INNER JOIN "attempts" AS a1 ON a1."order_id" = o0."id" WHERE (o0."canceled" = FALSE) []

So, as soon as I add an unused join, the count drops to 0. I’d have expected it to remain 1, at which point I would have added additional query clauses using the join and see where it broke.

Thanks.

1 Like
6

That’s how SQL join works in case there are no columns to join with. I recommend this site, and especially the diagrams - http://www.sql-join.com/sql-join-types/

In order not to remove rows that can’t be matched through join is to use a left join instead of a regular join (in ecto - replace join: with left_join:).

3 Likes
7

Thanks. I’m looking over the article, and am attempting to construct a smaller query that doesn’t quite meet all of my conditions.

q = from order in Order,
    left_join: accepted_attempt in assoc(order, :attempts),
    group_by: order.id,
    where: not order.canceled,
    where: accepted_attempt.accepted

So in theory this should return all orders. There is one order in my database with no attempts, so I’d imagine this would return that order. It doesn’t.

Repo.all(q)
[debug] QUERY OK source="orders" db=2.7ms queue=0.1ms
SELECT o0."id", o0."title", o0."description", o0."canceled", o0."owner_id", o0."inserted_at", o0."updated_at" FROM "orders" AS o0 LEFT OUTER JOIN "attempts" AS a1 ON a1."order_id" = o0."id" WHERE (NOT (o0."canceled")) AND (a1."accepted") GROUP BY o0."id" []
[]

It looks like the conditions on the left are true, but the right conditions won’t evaluate since there are no attempts for this order.

What am I missing now? Thanks for your help so far. I’ll keep poking at this here to see if I catch anything.

1 Like
8

OK, I got it. I think the conditions were ANDing across these joins in places when what I wanted was an OR. I simplified things by going with a single join, and a single more complicated WHERE:

  def open_orders_query do
    from order in __MODULE__,
    left_join: active_attempt in assoc(order, :attempts),
    where: not order.canceled,
    where: active_attempt.accepted or is_nil(active_attempt.text) or (not is_nil(active_attempt.text) and is_nil(active_attempt.accepted)),
    group_by: order.id,
    having: count(active_attempt.id) == 0
  end

If there is a way to split that last where, please do let me know. I think the problem is that where conditions on a query get AND’d together when that isn’t quite what I want. But I could be mistaken.

Thanks for all the pointers.

1 Like