SQL WITH AS statements in Ecto Subquery

Question

I have an SQL query that using the PostgreSQL WITH AS to act as an XOR or "Not" Left Join. The goal is to return what is in unique between the two queries.

In this instance, I want to know what users have transactions within a certain time period AND do not have transactions in another time period. The SQL Query does this by using WITH to select all the transactions for a certain date range in new_transactions, then select all transactions for another date range in older_transactions. From those, we will select from new_transactions what is NOT in older_transactions.

My Query in SQL is :

/* New Customers */
WITH new_transactions AS (
       select * from transactions
       where merchant_id = 1 and inserted_at > date '2017-11-01'
     ), older_transactions AS (
        select * from transactions
        where merchant_id = 1 and inserted_at < date '2017-11-01'
     )
SELECT * from new_transactions
WHERE user_id NOT IN (select user_id from older_transactions);

I'm trying to replicate this in Ecto via Subquery. I know I can't do a subquery in the where: statement, which leaves me with a left_join. How do I replicate that in Elixir/Ecto?

What I've replicated in Elixir/Ecto throws an (Protocol.UndefinedError) protocol Ecto.Queryable not implemented for [%Transaction....

Elixir/Ecto Code:

 def new_merchant_transactions_query(merchant_id, date) do
    from t in MyRewards.Transaction,
    where: t.merchant_id == ^merchant_id and fragment("?::date", t.inserted_at) >= ^date
  end

  def older_merchant_transactions_query(merchant_id, date) do
    from t in MyRewards.Transaction,
    where: t.merchant_id == ^merchant_id and fragment("?::date", t.inserted_at) <= ^date

  end

  def new_customers(merchant_id, date) do
    from t in subquery(new_merchant_transactions_query(merchant_id, date)),
    left_join: ot in subquery(older_merchant_transactions_query(merchant_id, date)),
    on: t.user_id == ot.user_id,
    where: t.user_id != ot.user_id,
    select: t.id
  end

Update:

I tried changing it to where: is_nil(ot.user_id), but get the same error.

LEFT JOIN <some_table> ON <related_keys> WHERE <some_table.column> IS NULL. I don't know how to write that in ecto, though. Note that your current final WHERE seems to be a condition that would turn your LEFT OUTER JOIN into a (regular) INNER JOIN. — Clockwork-Muse
I tried changing it to where: is_nil(ot.user_id), and get the same error. It may be something else — DogEatDog
Please include the complete error, it looks like it looks like a list of transactions has already been read from the Repo?? — Mike Buhot
Full Error: gist.github.com/cdesch/8defe0d38b69c7ce905cf534cd6ece0e — DogEatDog
Please include the source for new_merchant_transactions code used in the gist. — Mike Buhot

Joel Coehoorn Joel Coehoorn · Accepted Answer · 2018-01-09T23:07:39

This maybe should be a comment instead of an answer, but it's too long and needs too much formatting so I went ahead and posted this as an answer. With that out of the way, here we go.

What I would do is re-write the query to avoid the Common Table Expression (or CTE; this is what a WITH AS is really called) and the IN() expression, and instead I'd do an actual JOIN, like this:

SELECT n.* 
FROM transactions n
LEFT JOIN transactions o ON o.user_id = n.user_id and o.merchant_id = 1 and o.inserted_at < date '2017-11-01'
WHERE n.merchant_id = 1 and n.inserted_at > date '2017-11-01'
    AND o.inserted_at IS NULL

You might also choose to do a NOT EXISTS(), which on Sql Server at least will often produce a better execution plan.

This is probably a better way to handle the query anyway, but once you do that you may also find this solves your problem by making it much easier to translate to ecto.

SQL WITH AS statements in Ecto Subquery

1 Answers