SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with GROUP_CONCAT

    I'm having a problem with the aggragate function GROUP_CONCAT when using it in a statement with multiple tables. Here is my sql:

    PHP Code:
    SELECT 
        ID 
    AS id
        
    MLS_LISTING_ID,
        
    CONCATminprice ) , " - $"maxprice ) , " / "Rentals_pricing.type ) AS price
        
    GROUP_CONCAT(DISTINCT Rentals_pricing.type SEPARATOR '/') as type
    FROM 
        MLS

        
    Units
        
    Rentals_to_Units
        
    Rentals_pricing
    WHERE 
        MLS_LISTING_ID 
    Units.units_id
        
    AND PROPERTY_TYPE_CODE IN ('VR')
        AND 
    MLS_LISTING_ID Rentals_to_Units.units_id
        
    AND Rentals_to_Units.rentals_id Rentals_pricing.rentals_id
    GROUP BY 
        Rentals_pricing
    .rentals_id
    ORDER BY 
        min_price 
    The trouble is that this statement returns `type` as "[BLOB - 13 Bytes]". When I do a similiar query on just the Rentals_pricing table, it works fine (as it should). BTW, I'm using all four tables because I'm actually retrieving a fair amount of data about each unit; I edited the sql statement down to the relevant info.

    Thanks,
    basbd

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    is Rentals_pricing.type a blob field or something?

    try
    Code:
    GROUP_CONCAT(DISTINCT 
            Left(Rentals_pricing.type,100)
              SEPARATOR '/') as type
    are you sure you need DISTINCT in there?

    by the way, and for your own sanity down the road when you come back to debug your own queries, consider qualifying each and every column name with the table it comes from

    so instead of
    Code:
    SELECT 
        ID AS id, 
        MLS_LISTING_ID,
    you would write
    Code:
    SELECT 
        xxxxx.ID AS id, 
        yyyyy.MLS_LISTING_ID,
    also, it is always a good idea to make sure that your GROUP BY lists each and every non-aggregate column used in the SELECT list -- this avoids unpredictable results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, that worked! Thank you.

    To answer your question, I do need the DISTINCT in there. The pricing table is a list of multiple pricing levels for one rental (such as $100/weekly, $200/weekly, $2000/monthly, etc.). I'm trying to just get the lowest and heighest pricing level as a summary of the pricing for a particular unit.

    Come to think of it, maybe I should ask this question too. I'm using perl's XML::Generator::DBI to generate a XML file based on a query, so I need to format all of my data in my SQL statement. I've been trying to figure out the best way to format prices for rentals. Regular units just have a price field that I can draw off of, but rentals obviously has more complicated pricing scheme.

    What do you think the best way to format multiple pricing levels into one field? Where I was going with my original question was something like this:

    $750 - $3000 (weekly/monthly)

    But I think ideally I would like to have something like this:

    $750/weekly - $3000/monthly

    though I have no idea how to do this in a GROUPed statement.

    Thanks,
    Jon

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by basbd
    The trouble is that this statement returns `type` as "[BLOB - 13 Bytes]".
    this isn't necessarily "trouble". phpmyadmin usually hides the contents of a BLOB field from you because they usually contain large amounts of data. in your case, seeing that data is appropriate. there's a setting in phpmyadmin's config.inc.php file that controls this.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What do you think the best way to format multiple pricing levels into one field?
    replace this:
    Code:
    CONCAT( min( price ) , " - $", max( price ) , " / ", Rentals_pricing.type ) AS price,
    GROUP_CONCAT(DISTINCT Rentals_pricing.type SEPARATOR '/') as type
    with this:
    Code:
    GROUP_CONCAT(DISTINCT CONCAT('$', price, '/', Rentals_pricing.type) SEPARATOR '/') AS type
    this will successfuly handle cases where you have more than 2 rates, where your original query will not.

  6. #6
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Longneck, that's exactly what I wanted

    How come mysql would return the value of the GROUP_CONCAT as a BLOB, though? In my first code, I'm concatenating a VARCHAR field, so it's not like it's combining various field types, which would be the only reason I can think of of why it would return a BLOB.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that behavior is a bit screwy. group_concat() is new and has a lot of quirks. expect that to improve as time goes on.

    however, it's probably unimportant outside the development stage. in your host languange, strings are probably binary-safe, so a blob would be returned as a string and not a special data type. therefore, the distinction is unimportant once you actually start dealing with the data in your application.

    phpmyadmin is a special case since it must be able to deal with any data type of any size at any time. the developers opted to, by default, protect you from downloading potentially huge blobs.

  8. #8
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, interesting. Thanks for the info, longneck


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •