The following query works fine until I add the join on the image_galleries table. What it then does, is return some of the ‘images’ and ‘accompanying text’ - (for 4 images instead of all seven). It outputs that data repeatedly for each image/text; the same number of times as there are facilities.
I notice also that the the array of data returned for the images and text, it chopped off such that the fifth array element stops midway through. ie image 2 outputs 5 times as does image 3 and image 4. BUt on the fourth output of image 4, the output is cut off midway through the text.
q1. what I have overlooked in my query?
q2. is the ‘cutting off’ caused by a memeory issue of some sort or is it all down to my bad query?
Any guidance greatfully received.
SELECT
ps.room_type
, ps.description
, GROUP_CONCAT(
CONCAT_WS( ','
, fp.facility_category
, fp.facility_name
) ORDER BY fp.facility_category asc
SEPARATOR ';' ) AS facilities
, GROUP_CONCAT(
CONCAT_WS( ','
, im.image
, im.accompanying_text
) ORDER BY im.image asc
SEPARATOR ';' ) AS images
from products_stock ps
inner
join central_DB.image_galleries as im
on im.business_id = ps.business_id
and im.gallery_name = ?
and im.active = 1
and im.gallery_name = ps.room_type
left outer
JOIN facilities_products as fp
ON fp.live_product_id = ps.id
AND fp.business_id = ps.business_id
inner
join product_live pl
on pl.product_id = ps.id
and pl.start_date <= curdate()
and pl.end_date > curdate()
and priority = 'b'
where ps.business_id = ?
and ps.room_type = ?
while not a true cross join, it exhibits the same behaviour within each grouping of the main entity (in your case, for each property)
it is the result of combining two one-to-many relationships off the same entity
the way to fix this is to choose one of the relationships, and, instead of joining directly to it (resulting in multiple rows), join instead to a subquery which contains a GROUP BY – in mysql, you can easily bring forth the multiples of the “many” side of the relationship by using GROUP_CONCAT in that subquery – so that you are joining to only one row
to relate this to your query, instead of doing the GROUP_CONCATs in the outer query, do one of them inside a subquery, on, say, the images
OK, I am getting a better response from the query but it still cuts off midway through one of the results. Could this be for some reason other than a bad query?
here is what is being returned. As I hope is clear, the room2_6.jpg text is not complete and the image and data for room2_7.jpg is completely missing.
The data for image2_7.jpg and its text, is in keeping with what the query is looking for.
bazz
$VAR1 = [
'room2_2.jpg,We have completely re-furnished this room, including with a new bed and mattress, for April 2010. ',
'room2_3.jpg,The period feature fireplace in The Large Double. ',
'room2_4.jpg,Space aplenty in our large double. ',
'room2_5.jpg,Fresh, luxury mono-grammed towelling, including toothpastes, shampoos and shower gels. ',
'room2_6.jpg,More th'
];
here is the query:
SELECT ps.business_id
, ps.room_type
, ps.description
, GROUP_CONCAT(
CONCAT_WS( ','
, fp.facility_category
, fp.facility_name
) ORDER BY fp.facility_category asc
SEPARATOR ';' ) AS facilities
, im.image_data
from products_stock ps
left outer
JOIN facilities_products as fp
ON fp.live_product_id = ps.id
AND fp.business_id = ps.business_id
inner
JOIN ( SELECT business_id
, gallery_name
, GROUP_CONCAT(
CONCAT_WS(','
, image
, accompanying_text
)
SEPARATOR ';' ) AS image_data
from central_DB.image_galleries
where business_id = '153'
and gallery_name = 'Double (Standard)'
and active = 1
) as im
on im.business_id = fp.business_id
inner
join product_live pl
on pl.product_id = ps.id
and pl.start_date <= curdate()
and pl.end_date > curdate()
and priority = 'b'
where ps.business_id = '153'
and ps.room_type = 'Double (Standard)
OK, it ‘seems’ to be a memory/storage issue. If i remove the text from one or two images, the whole string is returned. It seems that what I am trying to return, is more than is being held in the array.
I’ll look up ‘memory’ and ‘storage’ but, if there is another issue I may have stumbled across, please let me know
SELECT ps.business_id
, ps.room_type
, ps.description
, im.image_data
, GROUP_CONCAT(
CONCAT_WS( ','
, fp.facility_category
, fp.facility_name
) ORDER BY fp.facility_category asc
SEPARATOR ';' ) AS facilities
from bookings.products_stock ps
left outer
JOIN bookings.facilities_products as fp
ON fp.live_product_id = ps.id
AND fp.business_id = ps.business_id
INNER
JOIN ( SELECT business_id
, GROUP_CONCAT(
CONCAT_WS(','
, image
, accompanying_text
)
SEPARATOR ';' ) AS image_data
FROM central_DB.image_galleries
where gallery_name = ?
and active = 1
group by gallery_name
) as im
on im.business_id = ps.business_id
inner
join bookings.product_live pl
on pl.product_id = ps.id
and pl.start_date <= curdate()
and pl.end_date > curdate()
and priority = 'b'
where ps.business_id = ?
and ps.room_type = ?
It seems that the result is limited in some way, to be 300 characters in length.
If I remove text from some images and run the query, it will show me more of what the query asks for again, limited to 300 chars. Never experienced this before with some much larger result sets.
I am now bald No matter what I seem to try, I always gravitate back to a derived sub select. To say I am now flailing about, is an understatement.
If you can give me a pointer or search term, I’ll try to make another inch of progress. I think I am that close. All the data I want is being returned, provided, it seems, the total size of it is less than 301 characters.
well, I’ll read up on that but, I am already confused.
I have three group_concats in different queries and all of them return more data than this particular one. So I am confised as to why I may need to change a server setting for this one. (Not doubting you oddz! but just confused). I always find the flow of questions much quicker than my ability to answer/learn the answer.
Its a partial observation based primarily on putting the words truncate and group concatenate together. I haven’t reviewed your query in-depth to any manor but it may be more practical to handle the organization of data in application language rather than using group concatenate since its returning truncated results it seems. Which is an indicator that your data is exceeding the max length. At least to me anyway. I know that QueryBrowser will tell you if the result set has been truncated. Perhaps there is a warning you an check for that will tell you if this is truly your problem if not using QueryBrowser.
Hmm, I have run that query on every table and all return zero results.
I had to modify the query several times to run it on all the tables. I don’t have a table called GLOBAL_VARIABLES but, instead have a table COLUMNS with a column called GLOBAL_VARIABLES.
I’ll try again, later this morning, after some sleep.
I have moved away from group_concat in an effort to try to get the same data via an outer join. Only getting one image so far and I am not seeing how a outer join doesn’t get me back all images and their captions.
SELECT ps.business_id
, ps.room_type
, ps.description
, img_data.image
, img_data.accompanying_text
, GROUP_CONCAT(
CONCAT_WS( ','
, fp.facility_category
, fp.facility_name
) ORDER BY fp.facility_category asc
SEPARATOR ';' ) AS facilities
from bookings.products_stock ps
left outer
JOIN bookings.facilities_products as fp
ON fp.live_product_id = ps.id
AND fp.business_id = ps.business_id
left outer
join (select business_id
, image
, accompanying_text
FROM central_DB.image_galleries
where gallery_name = ?
and active = 1
) as img_data
on img_data.business_id = ps.business_id
inner
join bookings.product_live pl
on pl.product_id = ps.id
and pl.start_date <= curdate()
and pl.end_date > curdate()
and priority = 'b'
where ps.business_id = ?
and ps.room_type = ?
Having group_concat w/o a group clause is going to return a single row. I don’t believe you need that subquery either. Also, that inner join following those left outer joins is turning your left outer joins into inner joins I do so believe. Placing an inner join after a left outer join doesn’t mean require this if and only if the previous is available. Also, the <= and >= comparison in the on clause is going to reek hell upon your optimization and scale horribly.
Thank you both for your help. At long last, (I seem to need everything done NOW, so it always takes me too long) lol, I have the server configured and the page working. Amended the /etc/my.cnf file.
Now, if / when, the server is shut down and restarted, the change will be read/loaded automatically.