SQL WITH AS statements in Ecto Subquery

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.

Answer

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.