Ecto where on multiple columns

Questions / Help
1

Hello everyone,

I am having trouble translating a query in Ecto.

SELECT  "pages".* FROM "pages" WHERE ("pages"."rgt" - "pages"."lft" = 1) ORDER BY "pages"."lft" ASC LIMIT $1  [["LIMIT", 11]]

I am trying to retrieve records where the sum of 2 fields equal 1. I tried this…

from(p in Page, where: p.rgt - p.lft == 1)

but I have this error.

** (Ecto.Query.CompileError) `i.rgt() - i.lft()` is not a valid query expression.

I tried some alternative, like i.rgt == i.lft + 1, but no success.

How can I query based on the sum of 2 fields?

Thanks for taking time.

2

You’ll probably have to use fragments for this:

from p in Page,
  where: fragment("? + 1", p.lft) == p.rgt

I haven’t tested this code, so it may not work, but you can build the SQL necessary for your use case.

3 Likes
3

Thank You @danschultzer, your solution is working fine :slight_smile: