\IPS\Helpers\Table\Db and grouping

[Ryan H.]

4.1.4 seems to include some changes to how grouping works with Db tables for strict mode, with the side effect that we can use multiple groupBy columns now. Awesome!

But the row count doesn't account for grouping at all. Line 270 in \IPS\Helpers\Table\Db:

<?php
		/* Count results (for pagination) */
		$count = \IPS\Db::i()->select( 'count(*)', $this->table, $where );

That means with my data set, $count returns the total number of records (235, 10 pages), but the grouped table only has 19 rows. 9 empty pages. (Strangely, with only one group column, I seem to have the opposite problem--only one page, regardless of the grouped number of results.)

Fixing it seems to be as easy as adding the group param to the select:

<?php
		/* Count results (for pagination) */
		$count = \IPS\Db::i()->select( 'count(*)', $this->table, $where, NULL, NULL, $this->group );

But that being said, I do not know what the performance implications of this would be. FWIW, the group option is not used on any table in core/forums/calendar.

Thanks.

 

For reference, my table, all core data:

<?php

$table = new \IPS\Helpers\Table\Db( 'core_tags', \IPS\Http\Url::internal( 'app=advancedtagsprefixes&module=manage&controller=tags' ), NULL, array( 'tag_text', 'tag_meta_app' ) );

$table->selects			= array( 'tag_text', 'count(tag_text) as uses', 'max(tag_added) as last_use' );
$table->include			= array( 'tag_text', 'tag_meta_app', 'tag_meta_area', 'uses', 'last_use' );
$table->onlySelected		= array( 'tag_text', 'tag_meta_app', 'tag_meta_area' );

$table->quickSearch 		= array( 'tag_text', 'tag_text' );
$table->mainColumn 		= 'tag_text';
$table->sortBy 			= $table->sortBy ?: 'tag_text';
$table->sortDirection 		= $table->sortDirection ?: 'asc';

 

[bfarber]

I already fixed that for 4.1.6.  I ran into the same issue with Dynamic Charts (which uses a Db table helper behind the scenes).

[Ryan H.]

I thought this was fixed, but it's partly resurfaced. 4.1.7 and 4.1.8 don't take grouping into account on the count query, meaning the page count is assuming there's no grouping at all.

With how explicit the change was, I'm sure there was some reason.

4.1.6:

		/* Count results (for pagination) */
		$count = \IPS\Db::i()->select( 'count(*)', $this->table, $where, NULL, NULL, $this->group );
		if ( count( $this->joins ) )
		{
			foreach( $this->joins as $join )
			{
				$count->join( $join['from'], ( isset( $join['where'] ) ? $join['where'] : null ), 'LEFT' );
			}
		}
		$count		= $count->first();

4.1.8:

		/* Count results (for pagination) */
		$count = \IPS\Db::i()->select( 'count(*)', $this->table, $where );
		if ( count( $this->joins ) )
		{
			foreach( $this->joins as $join )
			{
				$count->join( $join['from'], ( isset( $join['where'] ) ? $join['where'] : null ), 'LEFT' );
			}
		}
		$count		= $count->first();

See the difference?