Convert SQL query to Ecto / PostgreSQL function in from

Questions / Help
1

Is there nowadays an easy option to convert the following query to an Ecto query?

SELECT word, ndoc
    FROM ts_stat('
      SELECT tsv FROM products WHERE ...
    ')

Using fragment in from → from x in fragment(... is still not an option, am I right?

2

You could probably use the CTE functionality. Afaik this creates temporary tables just like putting the function directly as FROM data.

3

Not the thing I had hoped for.

I wanted to convert at least the nested part SELECT tsv FROM products WHERE ... to an Ecto subquery.
So I don’t have to write the WHERE clause as raw SQL.
But also with_cte I do not get it working.

I think with with_cte I also need to use fragment for ts_stat(?).
And it seems that fragment of course does not expect a subquery.

4

Ah, didn’t notice that. Yeah, a function receiving a query as text is not really something ecto can handle. You can use Repo.to_sql to generate the sql from an ecto query, but that won’t interpolate params, so it’s rather half of a solution.

5

Maybe this can help:

6

Thanks Benjamin.
Currently I’m blind in seeing a solution in it, but I’m not in top form today.
Maybe I revisit later or maybe it needs to stay raw SQL.