Multi join query troubles

Hi,

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 = ?



bazz

i call this “cross join effects”

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

Thanks Rudy,

I’ll try that shortly.

bazz

Uh-oh! This doesn’t bring me any results and it is because of something in the derived table part of the query.

What am I not getting? this is becoming frustrating.

bazz



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
       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
             , gallery_name
             , active
             , GROUP_CONCAT(
                       CONCAT_WS(','
                           , image
                           , accompanying_text
                       )
                 SEPARATOR ';' ) AS image_data
       FROM central_DB.image_galleries 
               ) AS im
             ON im.business_id = '153'
            and im.gallery_name = 'Double (Standard)' 
     
       where ps.business_id = '153'  
          and ps.room_type = 'Double (Standard)'

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)

bazz

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

bazz

Still no success. :frowning:

Query ‘works’ but, it cuts off the results.

google ain’t helping. :frowning:


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. :confused:

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.

bazz

bazz, i’m waiting for you to run out of steam :wink:

you are actually doing really well, documenting your progress for our benefit, as you progress through the various stages of debugging

but until you stop posting, i’m just gonna sit back and watch

:slight_smile:

I am now bald :smiley: 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.

bazz

GROUP_CONCAT max length

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.

:unhappy:

bazz

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.

oddz, thank you.

I shall mull over a way to bring in the necessary data without a group_concat. Unless I really must, I would rather not change the server config.

bazz

Try this out:


USE INFORMATION_SCHEMA;
 select * from GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'GROUP_CONCAT_MAX_LEN';

thanks oddz. gonna take a break coz I can’t even get that to work in a query. Grrr!

bazz

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.

bazz

And, now, I am most definitely out of steam :slight_smile:

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.

bazz