Zend\Paginator\Adapter\DbSelect alternative solution to count, with subselect

[ralphschindler]

This is a generic solution that should work across all database platforms (I've tested against the Zend_Db-Examples repository schema, and it works on Sqlite, MySQL, and Postgres.)

Ideally, in the future, we will have a module per vendor of database so that we can have platform specific solutions that take advantage of platform specific features. For example, MySQL could use SQL_CALC_FOUND_ROWS.

[fatmuemoo]

This doesn't work on non join queries.... example:
SELECT COUNT(1) AS c FROM (SELECT FROM transaction_line WHERE transaction_id = '1') AS original_select

Commenting out the line that resets the the columns fixes this.

[ralphschindler]

Hmm, resetting should reset columns not to empty but to a wildcard I think. I'll look into this.

[ralphschindler]

In addition, I am not sure columns() needs to be reset at all, what do you think about this patch now?

[fatmuemoo]

IMO, the columns don't need to be reset... looks good.

[ralphschindler]

Yeah, I've asked in #zftalk.dev, and they agree. If we need to do any kind of performance enhancements (which is basically what resetting columns is), we can do them elsewhere at a different time (perhaps in those vendor specific modules I mentioned).

Thanks, I'll work up unit tests and get this going.

[isimonov]

In issue did not reset columns in select query to determine the count of rows in the grouped queries. Here there was a problem in my opinion. The count() method throws an error when original_select will be such:

SELECT *, TRIM(str_column) as str_column FROM table

PDOException: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'str_column'

Query is good work: SELECT *, TRIM(str_column) as str_column FROM table

But query determine the count of rows throws an error:
SELECT COUNT(1) AS "c" FROM ( SELECT *, TRIM(str_column) as str_column FROM table ) AS "original_select"

The error resulted in higher.

I think need to reset columns original_select on '*'.

$select->reset(Select::COLUMNS);
$select->columns(array(Select::SQL_STAR));

So everything will work correctly.

And why delete code which get join information, clear, and repopulate without columns?

[isimonov]

If in join uses the same technique ( e.g. TRIM(str_column) as str_column ) appears the same error. For this you need to reset the column in join. Since it was originally.