My Paging Lost Offset...

Yii 1.1 General Discussion
1

Hi all yii members…

i got lost paging when in pages number 3.

page 1 = show 10 data page 1

page 2 = show 10 dara page 2

page 3 = show 30 data page 3

my question : why this paging number 3 out off bound ?

how to solve it ?

my code :


$countResult = $connection->createCommand('show meta;')->queryAll();

        foreach ($countResult as $data) {

            if ($data['Variable_name'] == "total_found") {

                $count = $data['Value'];

            }

        }


        $dataProvidersphinx = new CSqlDataProvider($sqlStatement, 

                array(

                    'keyField' => $key, 

                    'totalItemCount' => $count, 

                    'sort' => 

                        array('attributes' => array('entry', 'value',)

                            ,), 'pagination' => 

                        array('pageSize' => 10,),));

        $dataProvidersphinx->db = $connection;

        $dataProvidersphinx->getData();




        $this->render('index', array(

            'total' => $count,

            'dataProvidersphinx' => $dataProvidersphinx,

        ));

many thanks. :)

2

Hi yiiqs2,

I’m not familiar with sphinx, so this might be a wrong suggestion, but …

How do you construct $sqlStatement? Doesn’t it contain ‘offset’ and ‘limit’?

Because CSqlDataProvider tries to modify the sql according to the current pagination, you should not include them in the sql.

And, I think ‘$dataProvidersphinx->getData();’ is not necessary. CGridView or CListView will do it for itself.

3

thanks for reply softark…

sphinx is search server full text, http://sphinxsearch.com/ indexing database from mysql.

sphinx so powerful can find query in 0,00003 mil second.

sphinx have difference query with mysql :

http://sphinxsearch.com/

[color="#000080"]to get offset in mysql : " limit 5 offset 10 "

to get offset in sphinx using this : "limit 5,10 "

to get count in sphinx using this : " show meta "[/color]

i have make changes yii CDbCommandBuilder class in applylimit function :

in line "offset" i change "offset" to " , "

i hope sql limit offset can return from "limit 5 offset 10" to "limit 5,10"




public function applyLimit($sql,$limit,$offset)

	{

		if($limit>=0)

			$sql.=' LIMIT '.(int)$limit;

		if($offset>0)

			$sql.=' , '.(int)$offset;

                        // $sql.=' , '.(int)$offset; i have changed from $sql.=' offset'.(int)$offset; to "'"

		return $sql;

	}like this :

wow finally grid showing data from query sphinx. but paging not work in page 3.

in header tbgridview page 1 : showing 10-10 from 40 result

in header tbgridview page 2 : showing 11 -20 from 40 result

but in header page 3: [color="#FF0000"]showing 11-30 from 40 result

[/color]

i confused why tbgridview page 3 header why[color="#FF0000"]showing 11-30 from 40 result

[/color] not from 21-30

my question :

do you know why header page showing 11-30 from 40 result not 21-30 from 40 ?

this is because i have make changes CDbCommandBuilder class in applylimit function.

where function class that make result header in grid "showing 11-30 from 40 result"

this is my sphinx command.


     

$dsn = 'mysql:dbname=catalog;host=127.0.0.1;port=9306;';

        $port = '9306';

        $connection = new CDbConnection($dsn);

        $connection->active = true;


        $sqlStatement = "select catalogid,title from catalog where match('@title ORACLE') group by catalogid order by value asc";

        $key = 'catalogid';

        $result = $connection->createCommand($sqlStatement)->queryAll();


        $countResult = $connection->createCommand('show meta;')->queryAll();

        foreach ($countResult as $data) {

            if ($data['Variable_name'] == "total_found") {

                $count = $data['Value'];

            }

        }


        $dataProvidersphinx = new CSqlDataProvider($sqlStatement, 

                array(

                    'keyField' => $key, 

                    'totalItemCount' => $count, 

                    'sort' => 

                        array('attributes' => array('entry', 'value',)

                            ,), 'pagination' => 

                        array('pageSize' => 10,),));

        

        $dataProvidersphinx->db = $connection;
4

According to the sphinx documentation, the syntax for LIMIT should be:




LIMIT [offset,] row_count

http://sphinxsearch.com/docs/2.1.1/sphinxql-select.html

So “limit 5 offset 10” should be translated into “limit 10, 5” in your applyLimit method. The modification of the method will make things alright. :)

5

yes because that i changed cdbcommandbuilder applyLimit function.

i have make changes cdbcommandbuilder on applyLimit function i make changes offset to ","


public function applyLimit($sql,$limit,$offset)

        {

                if($limit>=0)

                        $sql.=' LIMIT '.(int)$limit;

                if($offset>0)

                        $sql.=' , '.(int)$offset;

                        // $sql.=' , '.(int)$offset; i have changed from $sql.=' offset'.(int)$offset; to "'"

                return $sql;

        }like this :

but i got error in pagination using mysql.

result using mysql :

page 1 : result 1-5

page 2 : result 6-10

page 3 : result 6-15 ( this page 3 should show : result 11-15 )

do you know why page 3 showing result 6-15 not 11-15 ?

actually in mysql : " limit 5,10" same with "limit 5 offset 10"

why page number 3 showing result 6-15 ( this page 3 should show : result 11-15 ).

should i change another function related cdbcommandbuilder ?

thanks softark. :)

6

Ah, no.

You are specifying the values in the order of ‘limit’, ‘offset’. But it should be ‘offset’,‘limit’.

7 



public function applyLimit($sql,$limit,$offset)

{

    if ($limit > 0) {

        if ($offset > 0) {

            $sql .= ' LIMIT ' . (int)$offset . ',' . (int)$limit;

        } else {

            $sql .= ' LIMIT ' . (int)$limit;

        }

    }

    return $sql;

}
8

wow,…

this works.

you rock…

thanks softark… :) :) :)

9

hi softark.

this is solved. i use sphinx (full text server search) in yii.

i use cdbconnection to execute yii.

in my controller




        $dsn = 'mysql:dbname=catalog;host=127.0.0.1;port=9306;';

        $port = '9306';

        $connection = new CDbConnection($dsn);

        $connection->active = true;


        $sqlStatement = "select * from catalog where match('@title oracle') group by catalogid order by title asc";

        $key = 'catalogid';

        $result = $connection->createCommand($sqlStatement)->queryAll();

        

        //to get sphinx count using this query : 

        $countResult = $connection->createCommand('show meta;')->queryAll();

        foreach ($countResult as $data) {

            if ($data['Variable_name'] == "total_found") {

                $count = $data['Value'];

            }

        }

        

        //this is new sphinx csqldataprovider 

        $dataProvidersphinx = new CSqlDataProvider($sqlStatement, 

                array(

                    'keyField' => $key, 

                    'totalItemCount' => $count, // $count i get from sphinx using above query.

                    'sort' => 

                        array('attributes' => array('entry', 'value',)

                            ,), 'pagination' => 

                        array('pageSize' => 10,),));

        

        $dataProvidersphinx->db = $connection;


        $this->render('index', array(

            'total' => $count,

            'dataProvidersphinx' => $dataProvidersphinx,

        ));

in view




 $this->widget('bootstrap.widgets.TbGridView', array(

        'dataProvider' => $dataProvidersphinx,

        'id' => 'order-grid',

        'type' => 'striped',

        'columns' => array(

            array(

            'header' => 'No',

            'value' => '$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize + $row+1',

            'headerHtmlOptions' => array('class' => 'headgrid'),

        ),

            array(

                'header' => Yii::t('order', 'id'),

                'value' => '$data[\'catalogid\']',

            ),

            

            array(

                'header' => Yii::t('order', 'Title'),

                'value' => '$data[\'title\']',

            ),

            

        ),

    ));

we need to modify yii cdbcommandbuilder.php class at [color="#FF0000"]function applyLimit[/color] in yii folder : vendor/yiisoft/yii/framework/db/schema/cdbcommandbuilder.php

change to this code.




   public function applyLimit($sql, $limit, $offset) {

        if ($limit > 0) {

            if ($offset > 0) {

                $sql .= ' LIMIT ' . (int) $offset . ',' . (int) $limit;

            } else {

                $sql .= ' LIMIT ' . (int) $limit;

            }

        }

        return $sql;

    }

wow…

softark rocks… !

thanks so much :) :) :) :)