SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() with Ecto3

Questions / Help
1

I’m implementing some pagination logic in Absinthe with Ecto 3(DB is MySQL), but Ecto 3 seems not to have a way to query SELECT FOUND_ROWS() except to call Ecto.Adapters.SQL.query!(Repo, "SELECT FOUND_ROWS()")

As a workaround, I’ve written a logic which query SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() like this.

def get_result_count() do
  query =
    from(
      q in Question,
      select: {q.id, q.title, q.body, q.user_id, q.inserted_at, q.updated_at},
      order_by: [asc: q.id]
    )

  {:ok, [result, count]} = Repo.transaction fn ->
    {sql, values} = Repo.to_sql(:all, query)

    calc_sql =
      sql
      |> String.replace_prefix("SELECT ", "SELECT SQL_CALC_FOUND_ROWS ")

    raw_result = Ecto.Adapters.SQL.query!(Repo, calc_sql, values).rows

    result =
      raw_result
      |> Enum.map(fn(x) ->
        [id, title, body, user_id, inserted_at, updated_at] = x
        key = %{id: id, body: body, title: title, user_id: user_id, inserted_at: inserted_at, updated_at: updated_at}
        true = Question.changeset(%Question{}, key).valid?
        Map.merge(%Question{}, key)
      end)

    [[count]] = Ecto.Adapters.SQL.query!(Repo, "SELECT FOUND_ROWS()").rows

    [result, count]
  end
end

It’s worked correctly, but I have a feeling that the logic is not elegant and it might be vulnerable to Ecto’s specification change.

Are there any better ways to implement SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() or any improvement for the logic?

2 Likes
2

Not an actual answer to the question asked!
Is there a particular reason you don’t want to just run another query to get the count?
Depending on how your table is constructed, and caching etc, it might actually be more efficient to tweak the query for a separate count query.

3

(I might miss to understand your sentenses due to my English skill.) I think you are saying that I only call one query but I’m calling two queries, SELECT SQL_CALC_FOUND_ROWS * and SELECT FOUND ROWS().

4

At last, I’ve wrap up the function as a module:

The module not only replaces the SQL and query, but also deals with some confirmation to know some internal specification changes in Ecto.Query.Builder.Select in the future).

5

No - outside of MySQL a typical approach might be:

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Track}

  def play do
    IO.puts(AppInfo.string())

    results_per_page = 3
    on_page = 10
    skip_rows = (on_page - 1) * results_per_page

    base_query = from(t in Track)
    count_query = from(t in base_query,
      select: count(t.id)
    )
    results_query = from(t in base_query,
      limit: ^results_per_page,
      offset: ^skip_rows)

    found_rows = Repo.one(count_query)                   # query No. 1
    # found_rows = Repo.aggregate(base_query, :count, :id)
    results = Repo.all(results_query)                    # query No. 2

    [results, found_rows]
  end

end

SQL_CALC_FOUND_ROWS doesn’t always outperform the above approach (especially when the count query can omit joins that do not affect the count).

2 Likes