Feature request (?): Named parameters in `fragment`s

88 views

Skip to first unread message

Łukasz Niemier

unread,

Jul 19, 2018, 7:04:29 AM7/19/18

to elixir-ecto

Currently fragment("foo(?, ?)", …) is roughly translated to foo($1, $2). What I would like is to have named parameters (or numbered?) so I could reuse some parameters passed to fragment that occurs in given string multiple times. Currently the only option that I am aware of is to pass that argument multiple times, so I ended with something like:

fragment(
  """
  (
    SELECT
      -- Balance
      COALESCE(SUM(items_log.qty) FILTER (WHERE items_log.date < ?), 0) AS open_bal,
      SUM(items_log.qty) AS close_bal,
      -- Qty In/Out
      COALESCE(SUM(items_log.qty) FILTER (WHERE items_log.qty > 0 AND items_log.date BETWEEN ? AND ?), 0) AS qty_in,
      COALESCE(SUM(-items_log.qty) FILTER (WHERE items_log.qty < 0 AND items_log.date BETWEEN ? AND ?), 0) AS qty_out,
      -- Item ID
      item_id
    FROM (
      SELECT
        inventory_item_id AS item_id,
        qty AS qty,
        purchase_date AS date
      FROM institution_inventory_items_in
      UNION
      SELECT
        inventory_item_id AS item_id,
        -qty AS qty,
        inserted_at AS date FROM institution_inventory_items_out
    ) items_log
    GROUP BY items_log.item_id
  )
  """,
  unquote(open_date),
  unquote(open_date),
  unquote(close_date),
  unquote(open_date),
  unquote(close_date)
)

Where with named params it could be used like

fragment(
  """
  (
    SELECT
      -- Balance
      COALESCE(SUM(items_log.qty) FILTER (WHERE items_log.date < ?{open_date}), 0) AS open_bal,
      SUM(items_log.qty) AS close_bal,
      -- Qty In/Out
      COALESCE(SUM(items_log.qty) FILTER (WHERE items_log.qty > 0 AND items_log.date BETWEEN ?{open_date} AND ?{close_date}), 0) AS qty_in,
      COALESCE(SUM(-items_log.qty) FILTER (WHERE items_log.qty < 0 AND items_log.date BETWEEN ?{open_date} AND ?{close_date}), 0) AS qty_out,
      -- Item ID
      item_id
    FROM (
      SELECT
        inventory_item_id AS item_id,
        qty AS qty,
        purchase_date AS date
      FROM institution_inventory_items_in
      UNION
      SELECT
        inventory_item_id AS item_id,
        -qty AS qty,
        inserted_at AS date FROM institution_inventory_items_out
    ) items_log
    GROUP BY items_log.item_id
  )
  """,
  open_date: ^open_date,
  close_date: ^close_date
)

This would IMHO greatly improve readability of some fragments that I had to write.

OvermindDL1

unread,

Jul 19, 2018, 12:47:29 PM7/19/18

to elixir-ecto

I'd love this, I have some large fragments that re-use a single value 12 times, not great for efficiency either...

Reply all

Reply to author

Forward

0 new messages