CODEWITHSUNDEEP
ruby-on-railssortingareljoin
Jay Godse
Jay Godse

Reputation: 15503

Arel: join table, sort on joined field

I have the following ActiveRecord Models

class Publication < ActiveRecord::Base
   attr_accessible :id, :pname

   has_many :advertisements
end

class Vendor < ActiveRecord::Base
   attr_accessible :id, :vname
   has_many :advertisements
end

class Advertisement < ActiveRecord::Base
  attr_accessible :id, :vendor_id, :publication_id, :prose, :aname
  belongs_to :vendor
  belongs_to :publication

end

The tables for these have the same fields as their accessible attributes.

I would like to be able to sort on the publication name, ad name, or vendor name, ascending or descending.

I also have a controller for the advertisements, where I want to display a list of ads. The list displays the name of the ad (aname), prose of the ad (prose), the name of the vendor (vname), and the name of the publication (pname).

The SQL query for ordering by publication name would look something like:

SELECT ads.aname AS aname, ads.id, ads.prose, ven.vname AS vname, pub.pname AS pname  
    FROM advertisements AS ads
      INNER JOIN publications AS pub ON ads.publication_id = pub.id
      INNER JOIN vendors AS ven ON ads.vendor_id = ven.id
      ORDER BY <sort_column> <sort_order>

Where sort_column could be one of "pname", "aname", or "vname", and sort_order could be one of "ASC" or "DESC", and both would come as parameters from the web form along with the pagination page number.

The controller index code looks like this:

class AdvertisementsController < ApplicationController
   def index
     sort_column = params[:sort_column]
     sort_order = params[:sort_order]

     @ads = Advertisement.join( somehow join tables)
               .where(some condition).where(some other condition)
               .order("#{sort_column} #{sort_order}")      ### I don't know what to do here
               .paginate(page: params[:page], per_page: 10) #from will_paginate

   end

   #   other controller methods.......
end

The index view table snippet (written in SLIM) looks like this:

tr
- @ads.each do |ad|
  td = ad.id
  td = ad.aname
  td = ad.pname
  td = ad.vname

I am aware that I could use AREL to do this, but I have been mucking around with AREL in the Rails console trying to generate and execute this query with pagination, and reading tutorials on the web and I can't figure out how to get this query in AREL, with sorting on joined fields, and with the ability to use a will_paginate Ruby query clause to paginate the query.

How does one use AREL, or even ActiveRecord to do this? I appreciate any help I can get.

Upvotes: 1

Views: 1211

Answers (2)

jake
jake

Reputation: 2411

You can have the solution work with both Arel and ActiveRecord. I would suggest you stick to ActiveRecord as much as you can unless you cant do it with AR.

Arel is great, but lately I have seen that in my code base, it reduces overall readability, esp if you mix it with AR or use too much of it.

Also couple of other suggestions:

  1. On the same query about try using "includes" instead of using "joins". You might that its easier than having to add the select clause. I use includes for outerjoins, but for a more detailed comparison, google "includes vs joins", it is very interesting.

  2. In complete opposite direction of my first suggestion, in case you queries are going to get complex, I highly recommend using https://github.com/activerecord-hackery/ransack or https://github.com/activerecord-hackery/squeel for your use case. Especially if you not doing the above for just learning purposes.

Upvotes: 0

messanjah
messanjah

Reputation: 9278

You can accomplish what you want with vanilla ActiveRecord methods, without Arel. What you have is pretty close, this might help you get there.

# whitelist incoming params
sort_column = %w(pname aname vname).include?(params[:sort_column]) ? params[:sort_column] : "pname"
sort_order  = %w(asc desc).include?(params[:sort_order])           ? params[:sort_order]  : "desc"

@ads = Advertisement.select("advertisements.*, vendors.vname, publications.pname").
  joins(:publication, :vendor).
  where(some condition).
  where(some other condition).
  order("#{sort_column} #{sort_order}").
  page(params[:page]).per_page(10)

Upvotes: 2

Related Questions