Complex multiple query breaks Pagination

Asked by Brian Rippon

I'd like to use a complex multiple query with Pagination, but it generates an SQL error. The SQL I'd like to use takes the form

SELECT PreQuery.Id, c.Price
      ( SELECT
              c.Id, MAX(c.aDate) Latest
        FROM prices c
        GROUP BY
              c.IId ) PreQuery, products p, prices c
      PreQuery.Id = p.Id
      AND PreQuery.Id = c.Id
      AND PreQuery.Latest = c.aDate

However both SELECT phrases are factored breaking the SQL.

[don't worry about what the SQL is trying to achieve, the latest price for a product, although other suggestions for SQL that does work gratefully received!]

Question information

English Edit question
PHPDevShell Edit question
No assignee Edit question
Solved by:
Brian Rippon
Last query:
Last reply:
Revision history for this message
TitanKing (titan-phpdevshell) said :

Try passing through $pagination->condition = 'AND';

Revision history for this message
TitanKing (titan-phpdevshell) said :

Just review your complete query and see what it returns, the pagination class wont cause errors on itself, the formatting just might need to be changed, also test your query independently.

Revision history for this message
Brian Rippon (brian-rippon) said :

Thanks, I'll try the suggestion of $pagination->condition. The SQL works fine standalone. The problem I am seeing is that the SQL is transformed into

              max(c.Effective) Latest
              bfg_prices c
           group by
              c.PropId ) PreQuery,
      bfg_properties p,
      bfg_prices c
          PreQuery.PropId = p.PropId
      and PreQuery.PropId = c.PropId
      and PreQuery.Latest = c.Effective

 PropId LIKE '%%x%%' OR
 LIMIT 0, 15

and this generates

The MySQL database engine returned with an error (code 1234 - Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'

Should all the SELECTs be modified with SQL_CALC_FOUND_ROWS?

Revision history for this message
TitanKing (titan-phpdevshell) said :

Yes, they need to be modified for pagination to work, but the replacement should only happen, once, lets create a small patch and see if it fixes it, this patch will then be redistributed in release.

Open plugins/Pagination/models/pagination.query.php

On line 71 find, $this->sql = preg_replace("/select/i", 'SELECT SQL_CALC_FOUND_ROWS', $parameters[0]);
and Replace with
$this->sql = preg_replace("/select/i", 'SELECT SQL_CALC_FOUND_ROWS', $parameters[0], 1);

See if this works, if it does, please report back and this will become a patch.

Revision history for this message
Brian Rippon (brian-rippon) said :

Great, that patch fixed it.


Revision history for this message
Brian Rippon (brian-rippon) said :

Actually, not quite...

If my SQL query already ends with 'WHERE condition1 AND condition2" etc, it's transformed into 'WHERE condition1 AND condition2 AND filter1 OR filter2"... Because of precedence this is wrong. I think line 247 of pagination.class.php should be

    $database_string = ' ' . $this->condition . ' ( ' . $search_columns . ' )';