How to pull reviews from database and display in a gridview

Question

I followed a tutorial to build a Restaurant reviews site on Ruby on Rails and i adapted it for Hospitals.

I would like to display results from the database in a sorted format like highest reviews or highest number of reviews or by location (all available data in the database) in a gridview (limited to Top 6).

I am unsure as to where to even start , I know it might sound a but simple but i have researched and keep seeing so many options and got confused as to where to start. Some solutions seem to fit small data sets but i fully expect this to grow exponentially in the future so the most effective way would be best for me. I have SQLlite on my local environment and Postgres in my production environment.

This is what i have on my search page that displays all my Hospitals and what i have in my index page which displays all records in a grid. Any assistance would be greatly appreciated.

search.html.erb

<div class="table-responsive table-box card"> 
  <%= form_tag search_hospitals_path, method: :get, class: "form-group" do %>
  <div class="input-group">
    <div class="input-group-addon">Search and Review</div>
          <p>
            <%= text_field_tag :search, params[:search],  class: "form-control formInput", placeholder: "Hospital Name" %>
          </p>
  </div>
    <% end %>

    <div class="container hospital_display">
      <% @hospitals.each do |hospital| %>
        <div class="row">
            <div class="col-sm-6">                
              <%= link_to image_tag(hospital.image), hospital, class: "responsive" %>
            </div>
              <div class="col-sm-6">
                  <%= link_to hospital.name, hospital %><br>
                  <%= hospital.address %><br>
                  <%= hospital.phone %><br>
                  <%= link_to hospital.website, hospital.website %>
                  <%# Checks for admin %>
                  <% if user_signed_in? && current_user.admin? %>
                    <%= link_to 'Edit', edit_hospital_path(hospital), class: "btn btn-link" %>
                    <%= link_to 'Destroy', hospital, method: :delete, data: { confirm: 'Are you sure?' }, class: "btn btn-link" %>
                  <% end%>
                  <%# Rating %>
                  <% if hospital.reviews.count == 0 %>
                    No reviews yet, be the first to write one
                  <% elsif hospital.reviews.count == 1 %>
                  <div class="star-rating" data-score= <%= hospital.avg_rating %> ></div>
                    1 Review
                  <% else %>
                  <div class="star-rating" data-score= <%= hospital.avg_rating %> ></div>
                    <%= hospital.length %> <%= "Reviews" %>
                <% end %>
              </div>
            </div>
          <% end %>
      </div>
    </div>
  </div>
</div>
<br>

<script>
    $('.star-rating').raty({
        path: 'https://s3-us-west-2.amazonaws.com/morafamedapp/stars',
        readOnly: true,
        score: function() {
            return $(this).attr('data-score');
        }
    });
</script>

I have this on my index page index.html.erb

<div class="jumboFluid">
<div class="jumbotron">
<section class="content">


<%= form_tag search_hospitals_path, method: :get, class: "form-group" do %>
<div class="input-group">
<div class="input-group-addon">Search</div>
        <p>
          <%= text_field_tag :search, params[:search],  class: "form-control formInput", placeholder: "Eye, Maternity" %>
          <%# <%= submit_tag "Search", name: nil, class: "btn btn-default" %>
        </p>
</div>
  <% end %><br>



  <h3 class="intro2">Find the best HealthCare Options around you with Medapp.
  <br><%# Explore the best of healthcare available in your community.<br>
    Read and leave reviews to assist others seeking the best health care and keep our hospitals on their toes. %></h3>
</section>
</div>
</div>
<div class="hospitalList">
<h1 id="hospitalBanner">Hospitals</h1>
<blockquote> 
  <p class="text-center"><cite>&#8220;Explore the best of healthcare available in your community&#8221;</cite> </p>
</blockquote>

<% content_for(:body_attributes) do %>
    data-no-turbolink="false"
  <% end %>
<main>
<div class="container-fluid fluid">
<div class="row">
<% @hospitals.each do |hospital| %>
  <div class="col-md-4 col-xs-6">
    <div class="thumbnail">
        <%= link_to image_tag(hospital.image), hospital %>
        <div class="caption">
          <h4> <%= link_to hospital.name, hospital %></h4><br>
          <% if hospital.reviews.count == 0 %>
            0 Reviews
          <% elsif hospital.reviews.count == 1 %>
            <div class="star-rating" data-score= <%= hospital.avg_rating %> ></div>
            1 Review
            <% else %>
            <div class="star-rating" data-score= <%= hospital.avg_rating %> ></div>
            <%= hospital.length %> <%= "Reviews" %>
          <% end %>
    </div>
  </div>
  </div>
<% end %>
 </div>

</main>


<br>

<% if user_signed_in? && current_user.admin? %>
  <%= link_to 'New Hospital', new_hospital_path, class: "btn btn-primary btn-lg btn-special" %>
<% end %>

<!--Start of Tawk.to Script-->
<script type="text/javascript">
window.$_Tawk = undefined;
var Tawk_API=Tawk_API||{}, Tawk_LoadStart=new Date();
(function(){
var s1=document.createElement("script"),s0=document.getElementsByTagName("script")[0];
s1.async=true;
s1.src='https://embed.tawk.to/592da976b3d02e11ecc677a1/default';
s1.charset='UTF-8';
s1.setAttribute('crossorigin','*');
s0.parentNode.insertBefore(s1,s0);
s1.style.background = 'yellow';
})();
</script>
<!--End of Tawk.to Script-->

<script>
  $('.star-rating').raty({
    path: 'https://s3-us-west-2.amazonaws.com/morafamedapp/stars',
    readOnly: true,
    score: function() {
      return $(this).attr('data-score');
    }
  });
</script>

</div>

Hospitals_controller.rb

class HospitalsController < ApplicationController
  before_action :set_hospital, only: [:show, :edit, :update, :destroy]
  before_action :authenticate_user!, except: [:search, :index, :show]
  before_action :check_user, except: [:search, :index, :show]
  protect_from_forgery with: :null_session
  protect_from_forgery except: ["create"]

  def search
    if params[:search].present?
      @hospitals = Hospital.search(params[:search])
    else
      @hospitals = Hospital.all
    end
  end

  def import
   Hospital.import(params[:file])
  end


  # GET /hospitals
  # GET /hospitals.json
  def index
    @hospitals = Hospital.all
  end

  # GET /hospitals/1
  # GET /hospitals/1.json
  def show
    @reviews = Review.where(hospital_id: @hospital.id).order("created_at DESC")
    if @reviews.blank?
      @avg_rating = 0
    else
    @avg_rating = @reviews.average(:rating).round(2)
    end
  end

  # GET /hospitals/new
  def new
    @hospital = Hospital.new
  end

  # GET /hospitals/1/edit
  def edit
  end

  # POST /hospitals
  # POST /hospitals.json
  def create
    import if params[:file] # <= this here is the call to your import method

    @hospital = Hospital.new(hospital_params)

    respond_to do |format|
      if @hospital.save
        format.html { redirect_to @hospital, notice: 'Hospital was successfully created.' }
        format.json { render :show, status: :created, location: @hospital }
      else
        format.html { render :new }
        format.json { render json: @hospital.errors, status: :unprocessable_entity }
      end
    end
  end

  # PATCH/PUT /hospitals/1
  # PATCH/PUT /hospitals/1.json
  def update
    respond_to do |format|
      if @hospital.update(hospital_params)
        format.html { redirect_to @hospital, notice: 'Hospital was successfully updated.' }
        format.json { render :show, status: :ok, location: @hospital }
      else
        format.html { render :edit }
        format.json { render json: @hospital.errors, status: :unprocessable_entity }
      end
    end
  end

  # DELETE /hospitals/1
  # DELETE /hospitals/1.json
  def destroy
    @hospital.destroy
    respond_to do |format|
      format.html { redirect_to hospitals_url, notice: 'Hospital was successfully destroyed.' }
      format.json { head :no_content }
    end
  end

  private
    # Use callbacks to share common setup or constraints between actions.
    def set_hospital
      @hospital = Hospital.find(params[:id])
    end

    def check_user
    unless current_user.admin?
        redirect_to root_url, alert: "Sorry, only admins can do that!"
    end
  end

    # Never trust parameters from the scary internet, only allow the white list through.
    def hospital_params
      params.require(:hospital).permit(:name, :address, :city_town, :state,  :phone, :website, :safe_care, :jci, :cohsasa, :best_known_4, :image )
    end
end

Answer 1

In your Hospital model, you could just define scopes that you can append to any query:

# app/models/hospital.rb
class Hospital < ActiveModel
  scope :order_by_highest_rated, -> { includes(:reviews).group("hospital_id").order("avg(reviews.rating) desc") }
  scope :order_by_most_reviews, -> { includes(:reviews).group("hospital_id").order("count(reviews.id) desc") }
end

then in your Hospital controller, you can add functionality to choose the sort order

class HospitalsController < ApplicationController
  def index
    if params[:search_by] == 'highest_ranked'
      @hospitals = Hospital.order_by_highest_rated
    else 
      @hospitals = Hospital.order_by_most_reviews
    end
  end
end

See here and here for more readings

Answer 2

If you don't care about accessing the value itself and only want the order I suggest going with andrew21s answer. With that out of the way, let's get to my answer.

The question is simple enough although the answer is more complicated than you would expect. I assume the following associations in the models:

class Hospital < ApplicationRecord
  has_many :reviews
end

class Review < ApplicationRecord
  belongs_to :hospital
end

With the above present this issue can be solved with the following scopes:

class Hospital < ApplicationRecord

  # ...

  def self.include_review_count
    review_count = Review.select(Review.arel_table[:id].count)
                         .where(Review.arel_table[:hospital_id].eq(arel_table[:id]))
                         .as('review_count')

    select(*attribute_names.map(&:to_sym)).select(review_count)
  end

  def self.include_review_average
    review_average = Review.select(Review.arel_table[:rating].average)
                           .where(Review.arel_table[:hospital_id].eq(arel_table[:id]))
                           .as('review_average')

    select(*attribute_names.map(&:to_sym)).select(review_average)
  end

  # ...

end

With these scopes present you can now do the following in your controller:

class HospitalsController < ApplicationController

  # ...

  def index
    @hospitals = Hospital.include_review_count
                         .include_review_average
                         .order('review_count DESC', 'review_average DESC', :id)
                         .limit(6)
  end

  # ...

end

Note: In the code above you can't use .order(review_count: :desc, review_average: :desc, id: :asc) because "review_count" is not a real attribute, but merely an alias. For this reason you'll have to use the literal (String) variant. The same is true when using this added attribute in a where statement. For example: .where(review_count: 2) doesn't work, whereas .where('review_count = ?', 2) does function.

This solution makes only one query and also allows you access the values without additional queries. An example could be (index.html.erb):

<table>
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Review Count</th>
      <th>Review Average</th>
    </tr>
  </thead>
  <tbody>
    <% @hospitals.each do |hospital| %>
      <tr>
        <td><%= hospital.id %></td>
        <td><%= hospital.name %></td>
        <td><%= hospital.review_count %></td>
        <td><%= hospital.review_average %></td>
      </tr>
    <% end %>
  </tbody>
</table>