How to do SQL variable binding in `from`

Questions / Help
,
1

I am working on the search feature for my project and trying to leverage Postgres text search. The query I want to run looks like this:

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10

query is bound in the FROM statement, and is utilized in both the WHERE and SELECT statements. This way it is only computed once.

The closest I can get with my knowledge of Ecto is this:

from a in Apod,
  where: fragment("to_tsquery('neutrino|(dark & matter)') @@ textsearch"),
  select: [a.title, fragment("ts_rank_cd(textsearch, to_tsquery('neutrino|(dark & matter)')"),
  order_by: fragment("ts_rank_cd(textsearch, to_tsquery('neutrino|(dark & matter)')"),
  limit: 10

I’m having to compute both the query and the rank twice. Is there a better way?

2

@dustinfarris I think you can get a bit closer with a subquery. The annoying bit is that ecto requires something in the from clause. I tried out a more limited example of this and it seems to work. Here is what the above might look like.

subq =  from a in Apod,
  select: %{query: fragment("to_tsquery('neutrino|(dark & matter)')")},
  limit: 1

from a in subquery(subq),
  where: fragment("query @@ textsearch"),
  select: [a.title, fragment("ts_rank_cd(textsearch, query)"),
  order_by: fragment("ts_rank_cd(textsearch, query)")