Rudy, I hope you’re out there cause I have a feeling you’re the only one who could help me on this one!
This query always worked fine UNTIL the pickup_locations table had a couple hundred rows added to it and the link table product_pickup therefore grew as each product now had more pickup_locations.
Basically some products have over 300 pickup locations and when I run this query I get about 100 returned max. Any help would be appreciated. I have a feeling it might be a memory setting or not?
Consider the following query:
SELECT products.id
, products.title
, product_locations.title as product_location
, products.cost
, products.extra_cost
, products.extra_cost_note
, products.saving_note
, products.picture_url
, COALESCE(l.locations,"") AS locations
, COALESCE(e.extras,"") AS extras
, products.status
FROM products
LEFT OUTER JOIN
( SELECT product_pickup.product_id
, GROUP_CONCAT(pickup_locations.location SEPARATOR "\
") AS locations
FROM product_pickup
INNER JOIN pickup_locations
ON pickup_locations.id = product_pickup.pickup_id
GROUP BY product_pickup.product_id ) AS l
ON l.product_id = products.id
LEFT OUTER JOIN
( SELECT product_extras.product_id
, GROUP_CONCAT(extras.title SEPARATOR "\
") AS extras
FROM product_extras
INNER JOIN extras
ON extras.id = product_extras.extras_id
GROUP BY product_extras.product_id ) AS e
ON e.product_id = products.id
LEFT OUTER JOIN
product_locations on products.product_location = product_locations.id
WHERE products.status IN ( 1 , 2 )';
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.
You could also choose to extract all locations without GROUP_CONCAT, but then you’ll have to manage the “duplicate” product rows (1 for each location) in your script.
I wrote ‘with GROUP_CONCAT’ when I wanted to write ‘without GROUP_CONCAT’. :injured:
SELECT products.id
, products.title
, product_locations.title as product_location
, products.cost
, products.extra_cost
, products.extra_cost_note
, products.saving_note
, products.picture_url
, COALESCE(l.location,"") AS locations
, COALESCE(e.extras,"") AS extras
, products.status
FROM products
LEFT OUTER JOIN
( SELECT product_pickup.product_id
, pickup_locations.location
FROM product_pickup
INNER JOIN pickup_locations
ON pickup_locations.id = product_pickup.pickup_id
) AS l
ON l.product_id = products.id
LEFT OUTER JOIN
( SELECT product_extras.product_id
, GROUP_CONCAT(extras.title SEPARATOR "\
") AS extras
FROM product_extras
INNER JOIN extras
ON extras.id = product_extras.extras_id
GROUP BY product_extras.product_id ) AS e
ON e.product_id = products.id
LEFT OUTER JOIN
product_locations on products.product_location = product_locations.id
WHERE products.status IN ( 1 , 2 )';
As you can see I eliminated the GROUP_CONCAT from the first subquery. It means that if a product has two pickup locations, now it will have two rows in the query result. All columns of those two rows will have the same values (product id, title, ecc) except for the locations column. So to display the product info like you did before, you’ll have to handle this new situation (more than 1 row for 1 product).
Ah gotcha… Hmm, now I’ve got dilemma. Either I get the data without group_concat and somehow filter is with PHP or I do two different queries and build an array…
Yah, that’s the way I went. I was a bit hesitant modifying default values since they’re usually defaulted to a certain value for a reason.
The particular table the group_concat is working on is 65kB so I did 100kB * 1024 and that’s the max I’m going with.
I chucked this snippet in my DB connection class.
//Need to set the buffer higher for group_concat or else the results are truncated
if (!mysqli_query($link, 'SET SESSION group_concat_max_len = 102400;')) {
$error = 'Error setting group_concat_max_len: ' . mysqli_error($link);
include $_SERVER['DOCUMENT_ROOT'].'/error.html.php';
exit();
}
