Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get total amount of rows, unique id's and result set with a LIMIT

I am building a data table with pagination on the frontend and the table contains advanced filtering and sorting with optional column visibility. There’s also the possibility to do actions on the selected rows in the table. The data in the table is lazy loaded, so my backend is only returning 10 rows with the LIMIT 10 clause in the SQL.

There’s 3 points of data I require in my frontend:

  1. the result set with the 10 rows
  2. The total amount of records without the LIMIT (for the pagination)
  3. The unique id’s of the entire result set (for the actions if the "select all checkbox" is selected.

The first point is retrieved logically by executing the query and doing a LIMIT 10 OFFSET 0.

The second point can be retrieved by adding SQL_CALC_FOUND_ROWS at the top of the query , and immediately executing SELECT FOUND_ROWS(); afterwards.

The third point I cannot seem to get this data anymore using the query which gives me the result for step 1 and step 2.

It is a very large query with quite some logic in it, so it’s not ideal for my scenario to execute this query two times to get both 3 result sets.

This is the code I have for the first 2 points (very simplified version, but the joins and other logic in the query don’t matter here):

SELECT SQL_CALC_FOUND_ROWS *
FROM (SELECT *
        FROM user
        GROUP BY id) AS subq
LIMIT 2 OFFSET 0;
SELECT FOUND_ROWS();

And for the third point i’d do this query:

SELECT DISTINCT(id)
FROM user
GROUP BY id

Is it possible to do the third query somewhere inside the first one? It could be for example just a GROUP_CONCAT that returns all the id’s as a string comma separated or something that I can work with and parse in my nodejs backend?

Tags:

2

Answers


  1. 0

    I’d wait to get all the ids until they do select all (which may find a different set/count of ids if the data has changed).

    But if you really want to do this, you don’t need a separate count since you can just check how many rows are returned. I’d have your complex query as a subquery, just producing all the ids you want in the order you want them, like this (assuming a table t to be ordered by t.x):

    select all_ids.id, t.*
from (
    select id, row_number() over (order by x) rn from t
) all_ids
left join t on rn <= 10 and t.id=all_ids.id
order by rn
    Login or Signup to reply.
  2. 0
    • Return 11 rows — the 11th tells you whether there are more
    • Use id as a "where you left off" instead of OFFSET.
    • Alas, SQL_CALC_FOUND_ROWS is deprecated, thereby necessitating the use of querying twice. Alternatively, stop giving the user the total count.
    • Number the rows in the app; doing it in SQL gets messy.

    More: Pagination

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search