Pagination with Stored Procedure and Cakephp 3

Question

Pagination with Stored Procedure and Cakephp 3

Navigation

#1 by (0 votes)

0

I have a function in which I set up my records with a Store Procedure and I want to do a paging, of course in this case I can not use the predefined function of CakePHP here my code:

public function index() {
$id_user = $this->Auth->user('id_usuario');
$connection = ConnectionManager::get('default');
$query_user = $connection->execute('CALL ser_get_list_persons(:_IdUser)',[':_IdUser'=>$id_user])->fetchAll('assoc');
$this->set('persons', $query_user);

}

To this I want to put a page and it would have to be manual or maybe another way, I searched the entire network but I have not found something similar for several days for help thanks.

mysql php cakephp

asked by Jonathan Cunza 01.12.2017 в 00:09

source

1 answer

How can I maintain a constant data send to a URL in Python? Problem in php + Mysqli

score 0 · Answer 1

I built a stored procedure that pages directly in SQL Server, I think it's efficient since I leave all the hard work to the engine, I hope it works for you too:


IF OBJECT_ID('SPS_Consultar','P') IS NOT NULL DROP PROCEDURE SPS_Consultar
GO
CREATE PROCEDURE SPS_Consultar
    @Tabla sysname,
    @StringABuscar VARCHAR(100) = '%%', 
    @Esquema sysname = 'dbo', 
    @NumPagina as int = 1,
    @TamPagina as int = 10,
    @OrdenarPor as varchar(100) = '',
    @Columnas  as varchar(max) ='',
    @CantPaginas INT OUT,
    @TotalRegistos INT OUT
AS
DECLARE @sqlCommand Nvarchar(max) = ''
DECLARE @ParmDefinition nvarchar(500) = ''
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

    --------------------- INICIACIÓN DE VARIABLES -----------------------
    IF @StringABuscar  = '' SET @StringABuscar = '%%'
    SET @CantPaginas = 0
    SET @TotalRegistos = 0
    ---------------------------------------------------------------------

    --------------------- SI @OrdenarPor ESTÁ VACÍO ---------------------
    IF @OrdenarPor = '' 
    BEGIN
        SELECT TOP 1 @OrdenarPor = COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = @Esquema
        AND TABLE_NAME = @Tabla 
        --AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
    END
    ----------------------------------------------------------------------------

    --------------------- CONSTRUCCIÓN DE LA CONSULTA -------------------------
    SET @sqlCommand = 'SELECT overall_count = COUNT(*) OVER(),* FROM [' + @Esquema + '].[' + @Tabla + '] WHERE ' 
    SET @sqlCommand = ' FROM [' + @Esquema + '].[' + @Tabla + '] WHERE ' 
    IF @Columnas = ''
    BEGIN
        SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @StringABuscar + ''' OR '
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = @Esquema
        AND TABLE_NAME = @Tabla 
        --AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
        SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
        SET @sqlCommand = @sqlCommand+' ORDER BY '+@OrdenarPor
    END
    ELSE
    BEGIN
        SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @StringABuscar + ''' OR '
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = @Esquema
        AND TABLE_NAME = @Tabla 
        AND COLUMN_NAME IN (SELECT WORD FROM dbo.FNS_EXPLODE (@Columnas,','))
        SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
        SET @sqlCommand = @sqlCommand+' ORDER BY '+@OrdenarPor
    END

    SET @sqlCommand=@sqlCommand+' OFFSET ('+CAST(@NumPagina AS VARCHAR)+'-1)*'+CAST(@TamPagina AS VARCHAR)+' ROWS '
    SET @sqlCommand=@sqlCommand+' FETCH NEXT '+CAST(@TamPagina AS VARCHAR)+'ROWS ONLY '
    ---------------------------------------------------------------------------

    --------------------- CONTAR LOS REGISTROS DE LA TABLA ---------------------
    DECLARE @sqlCommand2 AS NVARCHAR(MAX) = N'SELECT @TotalRegistos = COUNT(*) OVER() ' + @sqlCommand;  
    SET @ParmDefinition = N'@TotalRegistos int OUTPUT';
    EXEC sp_executesql @sqlCommand2, @ParmDefinition, @TotalRegistos=@TotalRegistos OUTPUT;
    ----------------------------------------------------------------------------

    --------------------- CANTIDAD DE PAGINAS TOTALES ---------------------
    SELECT @CantPaginas = (@TotalRegistos / @TamPagina) + CASE WHEN @TotalRegistos % @TamPagina > 0 THEN 1 ELSE 0 END 
    -----------------------------------------------------------------------

    --------------------- EJECUTAR CONSULTA FINAL CONSTRUIDA ---------------------
    SET @sqlCommand = 'SELECT * ' + @sqlCommand
    PRINT @sqlCommand
    EXEC sys.sp_executesql @sqlCommand
    ------------------------------------------------------------------------------

END
GO

And you can use it like this:

DECLARE @CantPaginas INT 
DECLARE @TotalRegistos INT 
DECLARE @NumPagina INT = 1
EXEC dbo.SPS_Consultar 
    @Tabla = 'AFI', 
    --@Columnas = 'PrimerNombre', 
    @StringABuscar = '', 
    @NumPagina = @NumPagina, 
    @TamPagina = 100, 
    @CantPaginas = @CantPaginas OUTPUT, 
    @TotalRegistos = @TotalRegistos OUTPUT

PRINT 'PÁGINA ' + CAST(@NumPagina AS VARCHAR) + ' DE '+CAST(@CantPaginas AS VARCHAR)
PRINT 'Total Registros '+CAST(@TotalRegistos AS VARCHAR)