php - Buscar un registro específico en los resultados paginados de MySQL

pagination seek

Question

Tengo un sistema de paginación clásico que usa LIMIT startrecord, endrecord y quiero averiguar en qué número de página se encuentra un registro X.

La única idea que tengo ahora es buscar recursivamente todos los registros para descubrirlo. ¡Pero estoy buscando un método mucho más "económico"!

Algunas ideas ?

Answer 1

Podría usar una sub consulta para crear una tabla con los resultados y su posición, y luego consultarla para la entrada específica que está viendo:

SET @rank=0; 
SELECT rank, record 
FROM (
    SELECT 
        @rank:=@rank+1 AS rank, 
        record 
    FROM table
) as subquery 
WHERE record = x;

La tabla devuelta mostraría el registro y el rango que apareció en la consulta original. Puede dividir el rango por el número de resultados por página ... O compilarlo en la consulta. Espero que esto ayude.

Answer 2

Indique la cantidad de registros anteriores al que está buscando. Esto requiere que usted asuma un pedido para su consulta que es natural.

SELECT COUNT(id) AS c
FROM tbl
WHERE sort_field < ((SELECT sort_field FROM tbl WHERE id = 18))
OR (sort_field = ((SELECT sort_field FROM tbl WHERE id = 18)) AND id < 18);

Luego solo recupera la c y calcula el ceilling(c/page_size) . Esto le dará el número de página en el que caerá su registro. Lo único importante que debe recordar es que debe ordenar los registros en el mismo orden que lo haría en su consulta con límite.

Para describir lo que hace la consulta, cuenta los registros numéricos que se encuentran delante del registro con id 18. La única parte difícil es con registros con el mismo valor que para su sort_field de sort_field en el cual MySQL hará uso de la clave principal y en nuestro caso id . Y es por eso que tenemos la parte OR en nuestra condición. En mi respuesta, supongo que está ordenando su consulta original (con la instrucción de límite) ascendente, pero si ordena de forma descendente, debe cambiar todo < to > .

Answer 3

Realmente no se puede crear una forma "económica". Debe obtener la lista completa de registros del DB ya que no hay forma de conocer la posición de un registro de MySQL.

Dependiendo de su clasificación, la frecuencia a la cual los datos cambian, puede asignarle al registro su posición en una columna: agregue la posición de la columna a la tabla que está consultando. Eso podría no ser factible en todos los casos.

Answer 4

Use algo como esto con su consulta como parte de la subselección s

SELECT s.row, s.RECORD, YOUR_OTHER_FIELDS...
FROM (SELECT @row := 0) cnt
  JOIN (SELECT @row := @row + 1 row, RECORD, ...YOUR QUERY WITH ORDER BY ...) s
WHERE s.RECORD = <desired record number>

y divide la fila por el tamaño de página de tu paginación. Ejemplo concreto pero sin sentido:

SELECT p.row, p.id
FROM (SELECT @row := 0) cnt
  JOIN (SELECT @row := @row + 1 row, id FROM products ORDER BY id desc) p
WHERE p.id = 485166

Según lo previsto, el valor de la row cambia con el orden que usa en la subselección.

Dobla la inicialización variable en la consulta, por lo que esta es solo una declaración. Tampoco depende de un orden natural o distribución de filas, siempre y cuando el orden en que se devuelvan permanezca igual para cualquier ORDEN que especifique (o omita).

Answer 5

si esto es algo que utilizará a menudo, creo que es una buena idea crear un procedimiento almacenado o una función . Podemos usar un cursor en el interior para recorrer los resultados y obtener la posición del elemento deseado. Creo que esto será más rápido, no tendrá que iterar a todos los registros, y no necesita una subconsulta (por todo esto diría que es más económico) y puede usar el orden, unirse y todo lo que necesite.

DELIMITER $$


CREATE FUNCTION position ( looking_for INT ) 
 RETURNS INT
READS SQL DATA
BEGIN
  -- First we declare all the variables we will need
  DECLARE id   INT;
  DECLARE pos   INT;
  SET pos=0;
  -- flag which will be set to true, when cursor reaches end of table
  DECLARE exit_loop BOOLEAN;         

  -- Declare the sql for the cursor
  DECLARE pos_cursor CURSOR FOR
    SELECT id  
    FROM your_table
    --you can use where, join, group by, order and whatever you need 
 --end of query

  -- Let mysql set exit_loop to true, if there are no more rows to iterate
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

  -- open the cursor
  OPEN example_cursor;

  -- marks the beginning of the loop
  example_loop: LOOP

    -- read the id from next row into the variable id
    FETCH  pos_cursor INTO  id;
    -- increment the pos var
    SET pos=pos+1;

    -- check if we found the desired item, 
    -- if it has been set we close the cursor and exit 
    -- the loop
    IF id=looking_for THEN
        CLOSE example_cursor;
        LEAVE example_loop;
    END IF;


    -- check if the exit_loop flag has been set by mysql, 
    -- if it has been set we close the cursor and exit 
    -- the loop
    IF exit_loop THEN
        CLOSE example_cursor;
        LEAVE example_loop;
    END IF;

  END LOOP example_loop;
 RETURN pos;
END $$

DELIMITER ;

Usted crea la función solo una vez, y para usarla, solo necesita usar este sql:

CALL position(ID_OF_THE_ITEM_YOU_ARE_LOOKING_FOR);

y devuelve la posición del artículo, en la posición [0] [0] del conjunto de filas devuelto.

Por supuesto, en lugar de la identificación, puede crear una función que compare el nombre, o cualquier otro campo, o incluso más de uno.

Si la consulta es siempre diferente , no puede usar una función, pero puede usar el cursor (la sintaxis será la misma). Puedes construir el cursor en tu PHP, dejar que sea una variable del Sistema (usando @pos), y en cualquier caso simplemente agregar el sql específico de la consulta (la parte entre DECLARE pos_cursor CURSOR FOR y --end of query )