Sorting from numbers not correct

[imonteiro]

Original author: m.ha...@gmail.com (October 27, 2008 14:00:35)

What steps will reproduce the problem?

1. Create grid
2. Add column with formatting 'number'
3. Sort on that column

What is the expected output? What do you see instead? Expected is that the column is number formatted iso string formated (1,2,3 iso 1,10,11,2,20)

What version of the product are you using? On what operating system? Latest

Please provide any additional information below. I solved it for the initial sort by defining ->order( 'CAST(wbs_id AS SIGNED)')

Original issue: http://code.google.com/p/zfdatagrid/issues/detail?id=8

[imonteiro]

From pao.fre...@gmail.com on October 27, 2008 19:57:20 The format options will only format the output from the database.

If you want to alter directly from the DB, you will need to use the option

sqlexp

$grid->addColumn('age',array('title'=>'Age','sqlexp'=>'FORMAT(age, 4)'));

[imonteiro]

From m.ha...@gmail.com on October 27, 2008 21:29:08 Using:

$grid->addColumn( 'wbs_id', array( 'title' => 'WBS-ID', 'sqlexp' => 'FORMAT(wbs_id, 4)') );

Result:

Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(wbs_id) AS wbs_id FROM cm.cm_wbs WHERE code = 1508

[imonteiro]

From pao.fre...@gmail.com on October 27, 2008 21:35:26 Are you using the latest SVN revision?

[imonteiro]

From m.ha...@gmail.com on October 27, 2008 22:10:28 Updated this weekend

[imonteiro]

From pao.fre...@gmail.com on October 27, 2008 22:34:36 Update again.

The datagrid is updated more than once per day.

check the changes here

http://code.google.com/p/zfdatagrid/updates/list

[imonteiro]

From m.ha...@gmail.com on October 28, 2008 18:22:54 Updated to version 57.

If I replace ->addColumn( 'wbs_id', array( 'title' => 'WBS-ID' ) ) by ->addColumn( 'wbs_id', array( 'title' => 'WBS-ID','sqlexp' => 'FORMAT(wbs_id, 4)') ) I get

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 1431655765 bytes) in C:\Documents and Settings\mhaket\My Documents\workspace\cm\library\Zend\Db\Statement\Mysqli.php on line 256

'wbs_id' is a VARCHAR 250 in the DB.

[imonteiro]

From pao.fre...@gmail.com on October 28, 2008 20:55:03 How many records does your table has?

[imonteiro]

From pao.fre...@gmail.com on October 28, 2008 21:39:52 Please update to the latest revision (58), and check if the bug remains...

[imonteiro]

From m.ha...@gmail.com on October 28, 2008 21:47:22 4500

[imonteiro]

From m.ha...@gmail.com on October 29, 2008 21:10:34 Datagrid.php version 61

Notice: Undefined variable: queryGroup in C:\Documents and Settings\mhaket\My Documents\workspace\cm\library\Bvb\Grid\DataGrid.php on line 2263

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 1431655765 bytes) in C:\Documents and Settings\mhaket\My Documents\workspace\cm\library\Zend\Db\Statement\Mysqli.php on line 256

[imonteiro]

From pao.fre...@gmail.com on October 30, 2008 03:26:20 The undifined varialbe is simple, is solved and the correction will be on the next commit.

About the memory size, can you please do the following

Before deploy the grid execute this code ($this->view->grid = $grid->deploy())

echo $grid->getQueryCount(); echo "

---

"; echo $grid->getQuery(); die();

And put the result here

Thanks in advance.

[imonteiro]

From m.ha...@gmail.com on November 01, 2008 12:11:19 SELECT COUNT(*) AS TOTAL FROM cm.cm_wbs WHERE code = 1206 AND NOT status IN ('rejected','done','final','finished','solved','delete','closed ok')

SELECT id, code, FORMAT(wbs_id, 4) AS wbs_id, deliverable, hw_sw, definition_status, status, functional_cluster, pgp_ip FROM cm.cm_wbs WHERE code = 1206 AND NOT status IN ('rejected','done','final','finished','solved','delete','closed ok') ORDER BY CAST(wbs_id AS SIGNED) LIMIT 0 , 50

[imonteiro]

From pao.fre...@gmail.com on November 01, 2008 15:46:02 I don't see nothing wrong with this query.

You may need to increase your memory size limit using

ini_set('memory_limit', '50M');

Which version of MySQL are you using?

Regards,

[imonteiro]

From m.ha...@gmail.com on November 01, 2008 18:15:28 Mysql version 5.0.18

First query returned: 669 Second query 50 records (as expected :-)

Rasing to 50 Mb won't do the trick. The class tries to claim 144 Mb (see error message)

Something else has to be wrong...

[imonteiro]

From pao.fre...@gmail.com on November 01, 2008 20:24:09 hmm...

Turn on the db profiler (application/config.ini => db.config.profiler = true;) and check the table db_profiler. See if is there something that can help.

I only execute 3 queries. Describe table, and the two above.

I'm not seeing why PHP tries to use that amount of memory.

Do me a favor. Try a clean query. $grid->from('cm_wbs');

Just to see if the problem remains.

Meanwhile, I will do some profiling...

Thanks,

[imonteiro]

From m.ha...@gmail.com on November 01, 2008 20:37:41 I integrated the Bvb library into my own environment. I already use a config.ini but I don't think the calss can get to the 'profiler-setting'. Otherway to convince the class to use the profiler?

Will try a clean query in the next couples of days...

[imonteiro]

From pao.fre...@gmail.com on November 03, 2008 04:43:29 Are you sure this error comes from the datagrid?

I've created a similar table (with the same fields from your query), inject 1.000.000 random records and then executed the above query.

Almost everything went fine. (had a problem but related to the template)