Complex multiple query breaks Pagination
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:
- 2011-02-24
- Last query:
- 2011-02-24
- Last reply:
- 2011-02-24
TitanKing (titan-phpdevshell) said : | #1 |
Try passing through $pagination-
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.
Brian Rippon (brian-rippon) said : | #3 |
Thanks, I'll try the suggestion of $pagination-
SELECT SQL_CALC_FOUND_ROWS
PreQuery.
c.Id,
c.Price
from
( SELECT SQL_CALC_FOUND_ROWS
from
group by
bfg_
bfg_prices c
where
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_
Should all the SELECTs be modified with SQL_CALC_
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/
On line 71 find, $this->sql = preg_replace(
and Replace with
$this->sql = preg_replace(
See if this works, if it does, please report back and this will become a patch.
Brian Rippon (brian-rippon) said : | #5 |
Great, that patch fixed it.
Cheers
Brian
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.
$database_
?
Cheers
Brian