#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    31
    Rep Power
    6

    How do you assign a single table to different columns after joining?


    This has currently got me a little perplexed. What I'm trying to achieve is joining a table of reviews with a table of images. The image table has the URL data so that they can be displayed. To link the two tables together I have a reviews_images table with the option of the image being a front image.

    Assuming a review can have multiple images, I had the tables looking as follows:
    Code:
    -------------------          -----------------          ---------------------------
    |     reviews     |          |    images     |          |     reviews_images      |
    -------------------          -----------------          ---------------------------
    | id_rev |  name  |          | id_img | uuid |          | rev_id | img_id | front |
    -------------------          -----------------          ---------------------------
    |   1    | Doom   |          |   1    | 1234 |          |   2    |   1    |   0   |
    |   2    | Doom 2 |          |   2    | 5678 |          |   2    |   2    |   1   |
    |   3    | Doom 3 |          |   3    | 9876 |          |   2    |   3    |   0   |
    -------------------          -----------------          ---------------------------
    What I want to try and achieve is the following:
    Code:
    ------------------------------------------------------------
    | rev_id |  name  | front_uuid | front | uuid      | image |
    ------------------------------------------------------------
    |   1    | Doom   | NULL       | NULL  | NULL      | NULL  |
    |   2    | Doom 2 | 5678       | 2     | 1234,9876 | 1,3   |
    |   3    | Doom 3 | NULL       | NULL  | NULL      | NULL  |
    ------------------------------------------------------------
    The issue I'm getting is that I don't know how to split the column for the front/uuid - is this even possible? What I can achieve is the following:
    Code:
    --------------------------------------------
    |   id   |  name  |      uuid      | imgID |
    --------------------------------------------
    |   1    | Doom   | NULL           | NULL  |
    |   2    | Doom 2 | 1234,5678,9876 | 1,2,3 |
    |   3    | Doom 3 | NULL           | NULL  |
    --------------------------------------------
    
    SELECT
        reviews.id_rev as id,
        reviews.name,
        GROUP_CONCAT(reviews_images.front) AS front,
        GROUP_CONCAT(images.id_img) as imgID,
        GROUP_CONCAT(images.uuid) as uuid
    FROM reviews
    LEFT JOIN reviews_images
    ON reviews_images.rev_id = reviews.id_rev
    LEFT JOIN images
    ON reviews_images.img_id = images.id_img
    GROUP BY reviews.name
    If it's not possible I assume it would just be much simpler to achieve this via PHP after exploding?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Code:
    SELECT reviews.id_rev AS id
         , reviews.name
         , CASE WHEN reviews_images.front = 1
                THEN images.uuid 
                ELSE NULL END    AS front_uuid
         , CASE WHEN reviews_images.front = 1
                THEN images.id_img  
                ELSE NULL END    AS front
         , GROUP_CONCAT(
           CASE WHEN reviews_images.front = 0
                THEN images.uuid  
                ELSE NULL END
                       )         AS uuid
         , GROUP_CONCAT(
           CASE WHEN reviews_images.front = 0
                THEN images.id_img  
                ELSE NULL END
                       )         AS image
      FROM reviews
    LEFT OUTER
      JOIN reviews_images
        ON reviews_images.rev_id = reviews.id_rev
    LEFT OUTER
      JOIN images
        ON images.id_img = reviews_images.img_id
    GROUP 
        BY reviews.id_rev
         , reviews.name

    Comments on this post

    • Varsh agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    31
    Rep Power
    6
    That has worked perfectly. I never even knew that there was a CASE option in MySQL. I've just been checking it out and even better is being able to order the CASE fields too. Massive thank you.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2016
    Posts
    53
    Rep Power
    2
    Please note. Not that I think you will hit this issue immediately, but group concat's return type, default, does not allow more than 1024 characters.
    so, if in theory, your group concat yields a result larger than 1024 characters, you will hit an issue.
    This can take a long time to identify, if you aren't aware of it.

    Sometime its can be overcome by simply allowing a larger character count in the return type, but the reason why its limited at 1024, is that larger sets tend to impact performance quite a bit.

    sometimes a restructure of the tables is required.

    But for your specific task, it does not seem like it will become an issue, at least not immediately.

    Comments on this post

    • Varsh agrees : Very handy to know of this limitation. I have had to restructure my queries because of this.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    31
    Rep Power
    6
    This is mainly for my portfolio website so there shouldn't be any issues. However I do want to eventually use the script for a much larger website after minor modifications. With the group concat - would this mainly be down to the amount of results in a single field or the actual character limit of 1024 (no matter the amount of results)?

    If it's the latter then I can see this being an issue very quickly as the UUID's are typically 36 to 38 characters in length (as a string - e.g.:46332cd6-121b-634e-ade8-2e1ce3c17e14~3). This would then give me only a possible 26-28 UUIDs. There will be instances in other scripts where this similar query will be used and I'll need to go, potentially, into the hundreds. How would I go about this restriction or what's your recommendations?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by Varsh
    How would I go about this restriction or what's your recommendations?
    don't use GROUP_CONCAT, i.e. don't use GROUP BY

    instead, just return the uuids and images each in a separate result row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2016
    Posts
    53
    Rep Power
    2
    Originally Posted by Varsh
    This is mainly for my portfolio website so there shouldn't be any issues. However I do want to eventually use the script for a much larger website after minor modifications. With the group concat - would this mainly be down to the amount of results in a single field or the actual character limit of 1024 (no matter the amount of results)?

    If it's the latter then I can see this being an issue very quickly as the UUID's are typically 36 to 38 characters in length (as a string - e.g.:46332cd6-121b-634e-ade8-2e1ce3c17e14~3). This would then give me only a possible 26-28 UUIDs. There will be instances in other scripts where this similar query will be used and I'll need to go, potentially, into the hundreds. How would I go about this restriction or what's your recommendations?
    This is really hard to answer, without a full understanding of how and why, you need your data formatted this way.
    You might want to attempt to save a serialized object, that hold the relevant data, instead of having the structure set dynamically. (This is if performance is the major factor).
    You could simply allow the group_concat return value to be set to a different value. I can't remeber the precise syntax, but you set the parameter value via a session update. Thinlk its something like "SET SESSION group_concat_return_value = 10000;"
    or similar to this, and it will allow your group_concat to return a longer string of characters.

    This is if performance is not an issue.

    There are many options in between, some idea may or may not work depending on how you actually want to use the data, so its a really hard question to answer without more information.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    31
    Rep Power
    6
    What it is is that the UUID is part of a URL. As the remaining part of the URL is static, I have removed that and used it as a separate static string in PHP, then stored the UUID in the database. The reason I have the database set up the way it is is so that I can delete an image and have it removed from the review on update automatically (I'm using InnoDB).

    If a game's page has, let's just say 100 images, then in order to show all the records (with images) then I would need to have it in a single query. The main idea was to have a few images shown, then clicking on the "show all" button would show the rest.

    The issue was also wanting to use a single query for the single page for each record. I could use 2 queries, one for the main article and icon image, and the other for the images thereafter, but I'm wanting to include custom BB code to allocate the ID of the images within the article (which I can already do). But if it's going to cause performance issues then I might have to reevaluate the table structure. I'm not entirely sure how though.

    For the time being the performance shouldn't be an issue as it is just a startup site, so the traffic won't be that high, but in the future I'll have to think about this further. My portfolio site won't have any issues whatsoever though so I guess it'll be a good test bed.

    I hope that has answered about how I want to use the data for one of the scenarios.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by Varsh
    ...I would need to have it in a single query.
    run the single query i suggested in post #6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2016
    Posts
    53
    Rep Power
    2
    Originally Posted by r937
    run the single query i suggested in post #6
    I don't think he means single query, I think he means single result row.
    I am not sure, why a single result row is required.

    But if it is a single query, that is indeed what is requested, then you are correct. Simply remove the groups, and accept you get a row, pr relation, and problem solved.

    But I do think he wants a single result row (not sure why) But I think that what he means.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    31
    Rep Power
    6
    Sorry, yes I meant a single row. There are instances where the end user will have a list of single items, but having multiple of a single item would look awful and not make any sense in regards to the information provided.

    However in the administration panel having multiple rows per result is fine as it can then be edited per record.

    Edit: I've decided to go the single query route however if the user wants any additional info within that field, then instead of displaying it all, I've gone with limiting the results to just a few, then having an AJAX request if the user wants to display the remaining records. I've already done a sample test and it has cut the load down a little per row, though I imagine with a potential 30k+ rows it'll make a massive difference. The administration panel I've gone with a simple query like r937 mentioned.

    Thanks for the help guys. After learning about that limit and performance issue, I'm now having to think doubly hard about what information to present to the end-user. It's been a good exercise and very thought provoking.
    Last edited by Varsh; November 6th, 2016 at 07:17 AM.

IMN logo majestic logo threadwatch logo seochat tools logo