Use of `dynamic` in Ecto for grouping and/or

Questions / Help
,
1

How would I write the following with Ecto?

SELECT id, from_date, until_date
FROM `events` e
WHERE user_id = 1
AND (from_date > "2019-10-01" OR from_date IS NULL)
AND (until_date < "2021-01-01" OR until_date IS NULL)

First naive attempt:

query = from e in Event,
  where: e.user_id == ^user_id,
  where: e.from_date > ^from_date,
  or_where: is_nil(e.from_date),
  where: e.until_date > ^until_date,
  or_where: is_nil(e.until_date)

Wrong query generated to what was intended:

# ...
WHERE
(
	(
		(
			(e0."user_id" = $1) AND (e0."from_date" > $2)
		) OR (e0."from_date" IS NULL)
	) AND (e0."until_date" > $3)
) OR
(e0."until_date" IS NULL)

Second successful attempt:

date_conditions =
  dynamic([e],
    (e.from_date > ^from_date or is_nil(e.from_date))
    and (e.until_date < ^until_date or is_nil(e.until_date))
  )

query = from e in Event,
  where: e.user_id == ^user_id,
  where: ^date_conditions

WHERE (e0."user_id" = $1)
AND (
  (
	(e0."from_date" > $2) OR (e0."from_date" IS NULL)
  ) AND
  (
	(e0."until_date" < $3) OR (e0."until_date" IS NULL)
  )
)

Looks good now.

Is a dynamic the way to go? How were people achieving such a simple query before dynamic was a thing?

1 Like
2

Yes this is the way to go. where or or_where clauses will result in implicit parenthesis like so: (prev. conditions on the query) or/and (new conditions). dynamic is the only way to compose conditions without that implicit grouping.

1 Like
3

Thank you!

4

dynamic is important when the conditions vary at runtime. When they don’t, you can write it more simply as:

query = from e in Event,
  where: e.user_id == ^user_id,
  where: e.from_date > ^from_date or is_nil(e.from_date),
  where: e.until_date > ^until_date or is_nil(e.until_date),
  select: {e.id, e.from_date, e.until_date}
1 Like
5

Thank you!