SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    floats, sql, and oo

    Okay, so I've got a situation that I'm having trouble with and it has multiple parts.

    Here's the code segment in question, coming from a controller:
    Code:
      @avg = Review.find_by_sql ["SELECT AVG(rating) AS rating FROM reviews r WHERE r.product_id = ?", params[:product_id]]
      for i in @avg
      	Product.update(params[:product_id], {:avg_rating => i[:rating]})
      end
    Notes:
    - Table "products" has an "avg_rating" field that is a float

    Problems:
    - The avg_rating value is always being stored as an integer. For example, let's say I have two ratings for a product, a "4" and a "5". The average should be "4.5", but for some reason it always stores a "4".
    - There has to be a better way to access the "rating" value from my query rather than looping through. I'm still a bit of an OO noob.
    Don Lair
    GetItCheap.com - Earn cash back online.


  2. #2
    SitePoint Enthusiast Stevenwulf's Avatar
    Join Date
    May 2002
    Location
    Berkeley
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your problem could be that you have not properly defined the column--in your table--that this value is being stored in. Have you defined it as a float? Have you specified how many digits to the right of the decimal point you want to keep?

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried both float and double datatypes in the database (MySQL). I haven't tried specifying the number of digits to the right, but I believe the default setting includes decimals. I'll try an explicit setting though.
    Don Lair
    GetItCheap.com - Earn cash back online.


  4. #4
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried making avg_rating a FLOAT(3,2) and the problem is still occuring.
    Don Lair
    GetItCheap.com - Earn cash back online.


  5. #5
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've found a more efficient way to handle the query, but I'm still having the same fundamental problem.

    New Code:

    Code:
    @avg = Review.find_by_sql(["SELECT AVG(rating) AS rating FROM reviews r WHERE r.product_id = ?", params[:product_id]]).first.rating.to_f
    render :text => @avg
    For a product with two ratings, 4 and 5, @avg is = to 4.

    I've tried the query directly in MySQL and the result is 4.5 like expected. For some reason Ruby is dropping the decimal, even though I've told it to be a float.
    Don Lair
    GetItCheap.com - Earn cash back online.


  6. #6
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Try @avg.to_f, which should cast it as a float.

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I finally figured this out and it was a case of RoR doing more than I am used to.

    My table 'reviews' has a column 'rating' that is an int. In my query, I did asked for "AVG(rating) AS rating". Well, rails expects/wants reviews.rating to be an int, and it recast my aggregate rating as an int. I renamed the aggregate to avgrating and everything works as expected.
    Don Lair
    GetItCheap.com - Earn cash back online.



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
  •