Suranyami

Had an unusual sort of query where we had a stream of sales data that was cumulative for the day but sent through in updates, so had to total them for a given store, for all users, but for the last entry on the day. And it was stored in Postgres in a JSON blob.

Here's how I ultimately solved it in postgres:

SELECT
  CAST(data ->> 'boxes' AS INTEGER) as boxes,
  data ->> 'storeid' AS storeid,
  data ->> 'username' AS username,
  CAST(inserted_at AS date) AS inserted_date
FROM
  external_records
WHERE
  api_id = 6
  AND data ->> 'transactiondate' IS NOT NULL
GROUP BY
  data ->> 'storeid',
  data ->> 'username',
  CAST(inserted_at AS date),
  inserted_at,
  data
ORDER BY
  inserted_at DESC;

I've been using Beekeeper Studio for running SQL queries. It's so much nicer than PGAdmin.

And here it is in ecto:

  def last_for_store_on_day(api_id, storeid, date_string) do
    date = Date.from_iso8601!(date_string)
    IO.puts(date)

    query =
      from(ex in ExternalRecord,
        where: ex.api_id == ^api_id,
        where: fragment("CAST(inserted_at AS DATE)") == ^date,
        where: fragment("data ->> 'storeid'") == ^storeid,
        group_by:
          fragment("""
          data ->> 'storeid',
          data ->> 'username',
          CAST(inserted_at AS date),
          inserted_at,
          data,
          id
          """),
        order_by: [desc: ex.inserted_at]
      )
    Repo.all(query)
  end

The fragment expressions are a little odd at first, but I like this a lot more than just doing the entire block as SQL, if only for security purposes.

Discuss...