Forum: Ruby on Rails sorting in different ways (with the same index action)

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
78d56bb0f91c899d40f1767b3040d825?d=identicon&s=25 bingo bob (bingobob)
on 2008-10-20 16:14
I've got a puzzle question.

I've got a load of news_items that I find and sort by date in my
controller and then display in the view. Works just fine. I do this in
the index action.

It appears as tabular information, with column headings. As I say by
default I'm sorting by date.

What I'd like to do is after the list is rendered in the view, I'd like
to offer the user the ability to click on a different column heading to
sort by that column.

e.g. allow the user to sort by news_item.stock.sector

What I think I need is a link_to helper at the top for each column
heading to pass something back to the index action of the controller so
that I can repopulate the @news_items with a newly sorted list (as the
user clicks different column headings).

Am I on the right lines?

Can anyone help me do this?

Thanks


bb
7b5c142a67af70ddc5cf696d952b2a81?d=identicon&s=25 Andreas Wolff (Guest)
on 2008-10-20 16:36
(Received via mailing list)
IMO you are on the right way. Pass a sort_by parameter to your index
action and use it as :order option for your News.find method..


lg // andreas

--
DynamicDudes

Lightweight Ruby on Rails application development
http://dynamicdudes.com
http://rubyphunk.com




On Mon, Oct 20, 2008 at 4:14 PM, bingo bob
78d56bb0f91c899d40f1767b3040d825?d=identicon&s=25 bingo bob (bingobob)
on 2008-10-20 17:05
Andreas Wolff wrote:
> IMO you are on the right way. Pass a sort_by parameter to your index
> action and use it as :order option for your News.find method..
>

OK that's great, could I trouble you for a short example?

def index
  if (the link_to has returned some :order)
    find some stuff in some order
  else if (the link_to has returned some :order)
    find some stuff in some order
  end
end

The above maybe better as a case?
7b5c142a67af70ddc5cf696d952b2a81?d=identicon&s=25 Andreas Wolff (Guest)
on 2008-10-20 17:38
(Received via mailing list)
For sure..

def index
  if params[:sort_by]
    News.find(.. , :order => params[:sort_by])
  else
    News.find(..)
  end
end

You could even use the shorter version

def index
  News.find(.., :order => params[:sort_by])
end

which passes nil (order by id) or the value of :sort_by to the :order
option..


lg // andreas

--
DynamicDudes

Lightweight Ruby on Rails application development
http://dynamicdudes.com
http://rubyphunk.com




On Mon, Oct 20, 2008 at 5:05 PM, bingo bob
78d56bb0f91c899d40f1767b3040d825?d=identicon&s=25 bingo bob (bingobob)
on 2008-10-20 19:14
Any chance you could show me how you'd do the link_to in the view to
pass the param?
D6434aa0b7b350f8c3ed0119d81b2ead?d=identicon&s=25 Roy Pardee (rpardee)
on 2008-10-21 17:15
(Received via mailing list)
I use this helper:

  def sort_link(show_text, sort_by)
    link_to show_text, {:action => 'index', :order_by => sort_by},
:class => 'big-link', :title => "Sort by #{show_text}"
  end

One thing I haven't done & would like to is to have the app 'remember'
what the previous sort order was, so that users can toggle the
ascending/descending nature of the sort.
78d56bb0f91c899d40f1767b3040d825?d=identicon&s=25 bingo bob (bingobob)
on 2008-10-21 21:40
Roy Pardee wrote:
> I use this helper:
>
>   def sort_link(show_text, sort_by)
>     link_to show_text, {:action => 'index', :order_by => sort_by},
> :class => 'big-link', :title => "Sort by #{show_text}"
>   end

OK great, how would i invoke that in a view and what might the
controller piece look like?
D6434aa0b7b350f8c3ed0119d81b2ead?d=identicon&s=25 Roy Pardee (rpardee)
on 2008-10-21 21:46
(Received via mailing list)
In my view I've got:

  <tr>
    <th><%= sort_link('Name', 'name') %></th>
    <th><%= sort_link('Grant number', 'grant_number') %></th>
    <th><%= sort_link('Status', 'status_id') %></th>
  </tr>

The controller starts out with:

  def index
    order_by = params[:order_by] || 'name'
    @projects = Project.find(:all, :order => order_by)

HTH,

-Roy
78d56bb0f91c899d40f1767b3040d825?d=identicon&s=25 bingo bob (bingobob)
on 2008-10-22 11:38
Thanks, this is great. It works and I can see the value of the helper!
Makes the controller code so much easier. Seem "right".

I could do with a bit more help though.

1) Out of interest - how do you use the [:title => "Sort by
#{show_text}"] bit?

2) Most importantly, I got this working when I sort by columns in my
news_items table, e.g. news_item.date BUT I can't get it to work for me
as I ahve relationships built up such that I have, news_item.stock and a
stock has a name. How would I use this to sort my news_items by the
names of fields of items with them, e.g. a news_item stock name, or a
news_item news_type name.

thought i could do something like ...

    <th><%= sort_link('Sector', 'news_item.sector.name') %></th>

But I can't, the SQL select generated blows up.

I might have set my relationships up wrong, but I don't think so.

BB
D6434aa0b7b350f8c3ed0119d81b2ead?d=identicon&s=25 Roy Pardee (rpardee)
on 2008-10-22 17:15
(Received via mailing list)
Yeah, that's a problem I haven't actually tackled myself yet. :P  If you
notice, I did this:

  <th><%= sort_link('Status', 'status_id') %></th>

Which does the sort by the numeric value of the id for the status,
rather than the text the user sees.  Very bush-league.

So I fixed it just now ;-) like so:

  - Add an :include clause in the .find call to bring the fields from
the child table into the SQL generated by AR.
  - Changed my field references to include the table names, for any
fields whose names are in both tables.

# controller
  def index
    order_by = params[:order_by] || 'projects.name' #<-- added the table
prefix, since both Projects and Statuses have name fields.
    @projects = Project.find(:all, :order => order_by, :include =>
'status') #<-- The :include causes rails to join both tables in the
resulting query.

Then the view becomes:

# view
  <th><%= sort_link('Name', 'projects.name') %></th>
  <th><%= sort_link('Grant number', 'grant_number') %></th>
  <th><%= sort_link('Status', 'statuses.name') %></th>

This seems to work pretty well.  I'm not sure how well it would scale if
you've got e.g., fifteen different child objects you need to bring in.

The {:title => } stuff causes the <a> tags to have a title attribute,
which gets shown in a tooltip when the user hovers over the link.
(link_to takes an optional hash of tag attributes.)

HTH,

-Roy
78d56bb0f91c899d40f1767b3040d825?d=identicon&s=25 bingo bob (bingobob)
on 2008-10-23 07:55
Excellent!

That works! well at least partly, I'm wondering if my relationships are
set up correctly...

here's some code that I am using

(top two work, bottom one not).

<th><%= sort_link('Date (just the date)', 'news_items.date') %></th>
<th><%= sort_link('Stock Name (stock.name)', 'stocks.name') %></th>
<th><%= sort_link('Sector Name', 'stocks.sector.name') %></th>

this is the error... "SQLite3::SQLException: no such column:
stocks.sector.name: SELECT "news_items"."id" AS t0_r0,
"news_items"."news_type_id" AS t0_r1, "news_items"."stock_id" AS t0_r2,
"news_items"."description" AS t0_r3, "news_items"."date" AS t0_r4,
"news_items"."created_at" AS t0_r5, "news_items"."updated_at" AS t0_r6,
"stocks"."id" AS t1_r0, "stocks"."name" AS t1_r1, "stocks"."ticker" AS
t1_r2, "stocks"."active" AS t1_r3, "stocks"."sector_id" AS t1_r4,
"stocks"."created_at" AS t1_r5, "stocks"."updated_at" AS t1_r6 FROM
"news_items"  LEFT OUTER JOIN "stocks" ON "stocks".id =
"news_items".stock_id      ORDER BY stocks.sector.name"  <- clearly
doesn't like stocks.sector.name

here's teh cont code

    order_by = params[:order_by] || 'news_items.date'
    @news_items = NewsItem.find(:all, :order => order_by, :include =>
'stock')

---

I also tried

    @news_items = NewsItem.find(:all, :order => order_by, :include =>
'stock', 'sector')

and

    @news_items = NewsItem.find(:all, :order => order_by, :include =>
'stock', 'sectors')

I feel it's close...


As i say, these work perfectly..

<th><%= sort_link('Date (just the date)', 'news_items.date') %></th>
<th><%= sort_link('Stock Name (stock.name)', 'stocks.name') %></th>

models are a bit like this...

class Stock < ActiveRecord::Base
  belongs_to :sector
  has_many :news_items
end

class Sector < ActiveRecord::Base
  has_many :stocks
end

class NewsType < ActiveRecord::Base
end

class NewsItem < ActiveRecord::Base
  belongs_to :stock
  belongs_to :news_type
  belongs_to :sector
end


I'd like to sort by all of those....

phew. not easy this relational db stuff !
D6434aa0b7b350f8c3ed0119d81b2ead?d=identicon&s=25 Roy Pardee (rpardee)
on 2008-10-23 17:38
(Received via mailing list)
Ah, so you've got a grandchild object (sector).  I just had a parent &
one child.  I bet you are close.  Try this:

  @news_items = NewsItem.find(:all, :order => order_by, :include =>
['stock', 'sector'])

and in the view:

  <th><%= sort_link('Sector Name', 'sectors.name') %></th>

(So--singular forms in :include--you're naming associated classes, but
plural form in the string that gets passed to :order, as that just gets
unceremoniously squirted into the SQL statement, so it's got to be a
valid table_name.field_name designation.)

I *think* that will work.

HTH,

-Roy
This topic is locked and can not be replied to.