Ruby-on-Rails [SOLVED]: Rails: mysql error unknown column in sub query

Ruby-on-Rails [SOLVED]: Rails: mysql error unknown column in sub query

Home Forums Frameworks Ruby on Rails Tutorials Ruby-on-Rails [SOLVED]: Rails: mysql error unknown column in sub query

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #246618

    Cloudy Point
    Keymaster

    QuestionQuestion

    I am implementing a search filter in my web app. using sub-queries like this:

    tool = Tool.select('*, (select ROUND(AVG(ratings.rating)) from ratings where tool_id = tools.id AND rating_type = 2) as ratings, 
    
    3956 * 2 * ASIN(SQRT(POWER(SIN(('+"#{params[:latitude]}"+' - abs(tools.latitude)) * pi()/180 / 2), 2) + COS('+"#{params[:latitude]}"+' * pi()/180 ) * COS(abs(tools.latitude) * pi()/180) * POWER(SIN(('+"#{params[:longtude]}"+' - abs(tools.longitude)) * pi()/180 / 2), 2) )) as  distance').where('user_id != ? AND pause_status =?', user_id, 0).order('distance asc')
    
        # =>delivery type only if delivery type is 1
        if params[:search].present? && !params[:search].nil?
          tool = tool.where('title LIKE ? OR description LIKE ?', "%#{params[:search]}%","%#{params[:search]}%")
        end
    
        # =>Category search
        if params[:category_id].present? && !params[:category_id].nil?
          tool = tool.where('category_id =?', params[:category_id])
        end
    
        # =>price range
        if params[:max_price].present? && params[:min_price].present? && !params[:max_price].nil? && !params[:min_price].nil?
          tool = tool.where('price >= ? AND price <= ?', params[:min_price].to_f, params[:max_price].to_f)
        end
    
        # => filter availability
        if params[:availability].present? && !params[:availability].nil?
          if params[:availability].to_i == 2
            tool = tool.where('available_type =?', 2) #=> weekend
          elsif params[:availability].to_i == 1
            tool = tool.where('available_type =?', 1) # => weekdays
          end
        end
    
        if params[:rating].present? && !params[:rating].nil?
          tool = tool.having('ratings > 5')
        end
    
    
        if params[:delivery_type].present? && !params[:delivery_type].nil?
          if params[:delivery_type].to_i == 0
            tool = tool.where('delivery_type = ?', 0)
          end
        end
    
        if tool.empty?
          return []
        else
          tool_array = []
          tool.each do |t|
            tool_hash = {}
            tool_hash['id'] = t.id
            tool_hash['title'] = t.title
            tool_hash['latitude'] = t.latitude
            tool_hash['longitude'] = t.longitude
            tool_hash['attachment'] = Image.get_single_attachment(t.id)
            tool_array.push(tool_hash)
          end
          return tool_array
        end
    

    when I pass rating parameter it print the query like this:

    SELECT COUNT(*) FROM `tools` WHERE (user_id != 3 AND pause_status =0) HAVING (ratings > 5)"
    

    and without rating parameter:

    SELECT *, (select ROUND(AVG(ratings.rating)) from ratings where tool_id = tools.id AND rating_type = 2) as ratings, 3956 * 2 * ASIN(SQRT(POWER(SIN((30.657797735213 - abs(tools.latitude)) * pi()/180 / 2), 2) + COS(30.657797735213 * pi()/180 ) * COS(abs(tools.latitude) * pi()/180) * POWER(SIN((76.7327738833397 - abs(tools.longitude)) * pi()/180 / 2), 2) )) as  distance FROM `tools` WHERE (user_id != 3 AND pause_status =0) ORDER BY distance asc"
    

    and I a error like this in my having clause:

    "error": "Mysql2::Error: Unknown column 'ratings' in 'having clause': SELECT COUNT(*) FROM `tools` WHERE (user_id != 3 AND pause_status =0) HAVING (ratings > 5)",
      "code": 301
    

    and if I comment the each loop it works.
    Please tell where I am doing wrong.

    #246619

    Cloudy Point
    Keymaster
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.