PHPDevShell

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
   FROM
      ( SELECT
              c.Id, MAX(c.aDate) Latest
        FROM prices c
        GROUP BY
              c.IId ) PreQuery, products p, prices c
   WHERE
      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

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

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

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

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 : 		#3

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

SELECT SQL_CALC_FOUND_ROWS
      PreQuery.PropId,
      c.Id,
      c.Price
   from
      ( SELECT SQL_CALC_FOUND_ROWS
              c.PropId,
              max(c.Effective) Latest
           from
              bfg_prices c
           group by
              c.PropId ) PreQuery,
      bfg_properties p,
      bfg_prices c
   where
          PreQuery.PropId = p.PropId
      and PreQuery.PropId = c.PropId
      and PreQuery.Latest = c.Effective

 AND
 PropId LIKE '%%x%%' OR
 ORDER BY PropId
 asc
 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 : 		#4

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 : 		#5

Great, that patch fixed it.

Cheers
Brian

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

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 . ' )';
?
Cheers
Brian