Issue
I have a table of > 250k rows of ‘names’ (and ancillary info) which I am displaying using jQuery Datatables.
My Users can choose any ‘name’ (Row), which is then flagged as ‘taken’ (and timestamped).
A (very) cut down version of the table is:
Key, Name, Taken, Timestamp
I would like to be able to display the ‘taken’ rows (in timestamp order) first and then the untaken records in their key order [ASC] next.
The problem would be simple, but, because of size constraints (both visual UI & data set size) My display mechanism paginates – 10 / 20 / 50 / 100 rows (user choice)
Which means a) the total number of ‘taken’ will vary and b) the pagination length varies.
Thus I can see no obvious method of keeping track of the pagination.
(My Datatable tells me the count of the start record and the length of the displayed records)
My SQL (MySQL) at this level is weak, and I have no idea how to return a record set that accounts for the ‘taken’ offset without some kind of new (or internal MySQL) numeric indices to paginate to.
I thought of:
-
Creating a temporary table with the key and a new numeric indices on
each pagination. -
Creating a trigger that re-ordered the table when the row was
‘taken’. -
Having a "Running order" column that was updated on each new ‘taken’
-
Some sort of cursor based procedure (at this point my hair was
ruffled as the explanations shot straight over the top of my head!)
All seem excessive.
I also thought of doing a lot of manipulation in PHP (involving separate queries, dependant on the pagination size, amount of names already taken, and keeping a running record of the pagination position.)
To the Human Computer (Brain) the problem is untaxing – but translating it into SQL has foxed me, as has coming up with a fast alternative to 1-3 (the test case on updating the "Running order" solution took almost three minutes to complete!)
It ‘feels’ like there should be a smart SQL query answer to this, but all efforts with ORDER BY, LIMITS, and the like fall over unless I return the whole dataset and do a lot of nasty counting.
Is there something like a big elephant in the room I am missing – or am I stuck with the hard slog to get what I need.
Solution
A query that displays the ‘taken’ rows (in timestamp order) first and then the untaken records in their key order [ASC] next:
SELECT *
FROM `table_name`
ORDER BY `taken` DESC, IF(`taken` = 1, `Timestamp`, `Key`) ASC
LIMIT 50, 10
The LIMIT values: 10 is the page size, 50 is the index of the first element on page 6.
Change the condition on IF(taken = 1,Timestamp,Key) with the correct condition to match the values you store in column taken. I assumed you store 1 when the row is ‘taken’ and 0 otherwise.
Answered By – axiac
Answer Checked By – Katrina (BugsFixing Volunteer)