Is it possible to convert to ecto DSL

Questions / Help
1

I have the query below to get the previous and next row of a struct with ID id

Ecto.Adapters.SQL.query(
  Repo,
  """
    SELECT
      *
    FROM (
      SELECT
        lead(id) over (order by position ) as next,
        lag(id) over (order by position ) as prev,
        id,
        parent_id
      FROM
        my_table
      WHERE
        parent_id=$1
    ) x
    WHERE
      $2 IN (id, next, prev)
  """,
  [parent_id, id]
)

The query works as in pure SQL, but I’m particularly interested in retrieving
schemas with id, next and prev. Can someone help to convert to Ecto query DSL?

1 Like
2

There’s for sure a way to convert this to ecto query DSL, but you can also use Repo.load to turn the results of the raw query to schema structs.

Btw: you can use Repo.query instead of Ecto.Adapters.SQL.query as well.

1 Like
3

Thanks. But I have no idea how.