Breaking down a list view


#1

I’m trying to figure out how to change the order of a list view and it’s
obvious to me that I don’t understand what I get from a simple scaffold
to know enough to alter it.

If someone would be so kind to tell me what this means …

def list
@placment_pages, @placements = paginate :placements, :per_page => 10
end

@placement_pages, # I am guessing that this calculates the total pages

@placements = paginate :placements, # I am guessing that this calculates
# pagination from total placements /
# :per_page value

:per_page => 10 # this one is obvious to me

what I am interested in is changing the ‘order’ of things…and probably
having the most difficulty with the fact that I want the :order to be
values of a field from a related table to which this table belongs.

placements belongs_to :clients

each placement record has a value for client_id

I want to sort this placements list by placements.client.last_name

How do I do that?

Craig


#2

On Tue, 2006-02-07 at 16:12 -0700, Craig W. wrote:

@placement_pages, # I am guessing that this calculates the total pages

placements belongs_to :clients

each placement record has a value for client_id

I want to sort this placements list by placements.client.last_name

How do I do that?


I should point out that I’ve been trying everything under the sun…

@placement_pages, @placements = :paginate(:placements, order_by =>
‘referral_date’, :per_page => 10)

works because referral date is a column in placements table but

@placement_pages, @placements = :paginate(:placements, order_by =>
‘placement.client.last’, :per_page => 10)
or
@placement_pages, @placements = :paginate(:placements, order_by =>
‘placements.client.last’, :per_page => 10)
or
@placement_pages, @placements = :paginate(:placements, order_by =>
[‘placement’][‘client.last’], :per_page => 10)

doesn’t work… the first 2 give me schema placement[s] doesn’t exist
and the last one says it can’t convert String into integer

I know there must be a method for sorting records based on related
tables…anyone?

Craig


#3

On Feb 7, 2006, at 3:32 PM, Craig W. wrote:

@placement_pages, @placements = :paginate(:placements, order_by =>
‘placements.client.last’, :per_page => 10)
or
@placement_pages, @placements = :paginate(:placements, order_by =>
[‘placement’][‘client.last’], :per_page => 10)

That would be :order

:slight_smile:


– Tom M.


#4

OK - I tried to decipher what that link means…so I changed it to the
following…

@placement_pages, @placements = paginate(
:placements,
:joins =>Client.find(:all),
:order => ‘last_name’,
:per_page => 14)

and the resulting error completely baffles me…

ActiveRecord::StatementInvalid in Placements#list
RuntimeError: ERROR C42601 Msyntax error at or near
“#<” P34 Fscan.l L573 Ryyerror: SELECT COUNT(*) FROM placements
#Client:0xb79f1c1c#Client:0xb79f1be0

Craig


#5

Keep in mind that :order (and probably :order_by) take …An SQL
fragment
like “created_at DESC, name”… (from
http://api.rubyonrails.com/classes/ActiveRecord/Base.html)

It looks like you are providing active record objects and methods.

It appears you are not having trouble if it is attributes in your
placements
table. To get to columns in the clients table you need to do a join.
Something like:

@placement_pages, @placements = :paginate(:placements,
:join “clients ON clients.placements_id=placements.id”,
order_by => ‘clients.last’, :per_page => 10)

This is probably not correct. Run the query and look at your development
log
to see the sql that is being generated. You may have to add a :select
and
specifically list all of the fields needed to populate your model,
excluding
the ones that came in with the join.

-Kelly


#6

On Tue, 2006-02-07 at 15:36 -0800, Tom M. wrote:

‘placement.client.last’, :per_page => 10)
or
@placement_pages, @placements = :paginate(:placements, order_by =>
‘placements.client.last’, :per_page => 10)
or
@placement_pages, @placements = :paginate(:placements, order_by =>
[‘placement’][‘client.last’], :per_page => 10)

That would be :order

:slight_smile:


I was using the Agile book and that’s the way they did it. I can see by
the link from the other answer that order_by is deprecated now but
changing it to :order didn’t change the outcome.

Craig


#7

all I can get is syntax errors…trying this from irb and eliminating
the “order_by” for now until I can get the syntax correct…

@placement_pages, @placements = paginate(:placements, :join “clients
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):6: syntax error
@placement_pages, @placements = paginate(:placements, :join “clients ON
clients.placement_id=placement.id”)
^
(irb):6: syntax error
from (irb):6

@placement_pages, @placements = paginate(:placements, :join “Clients
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):7: syntax error
@placement_pages, @placements = paginate(:placements, :join “Clients ON
clients.placement_id=placement.id”)
^
(irb):7: syntax error
from (irb):7

@placement_pages, @placements = paginate(:placements, :joins “Clients
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):8: syntax error
@placement_pages, @placements = paginate(:placements, :joins “Clients ON
clients.placement_id=placement.id”)
^
(irb):8: syntax error
from (irb):8

@placement_pages, @placements = paginate(:placements, :joins “clients
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):9: syntax error
@placement_pages, @placements = paginate(:placements, :joins “clients ON
clients.placement_id=placement.id”)
^
(irb):9: syntax error
from (irb):9

@placement_pages, @placements = paginate(:placements, :joins “client
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):10: syntax error
@placement_pages, @placements = paginate(:placements, :joins “client ON
clients.placement_id=placement.id”)
^
(irb):10: syntax error
from (irb):10

@placement_pages, @placements = paginate(:placements, :joins “Client
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):11: syntax error
@placement_pages, @placements = paginate(:placements, :joins “Client ON
clients.placement_id=placement.id”)
^
(irb):11: syntax error
from (irb):11

Craig


#8

Would I need to have a join table for this?

client.rb
has_many :placements

placement.rb
belongs_to :client


#9

Your syntax is wrong. Try this instead

@placement_pages, @placements = paginate(:placements, :joins =>
“clients ON clients.placement_id=placement.id”)

it has to be :joins not :join and you have to use the hash
syntax :joins => ‘…’ .

-Ezra

On Feb 7, 2006, at 4:17 PM, Craig W. wrote:

the “order_by” for now until I can get the syntax correct…

@placement_pages, @placements = paginate(:placements, :join
“clients
ON clients.placement_id=placement.id”)
SyntaxError: compile error
(irb):6: syntax error
@placement_pages, @placements = paginate(:placements, :join
"clients ON

"Clients ON
"Clients ON
"clients ON
"client ON
"Client ON

http://api.rubyonrails.com/classes/ActiveRecord/Base.html)

@placement_pages, @placements = :paginate(:placements,

    http://lists.rubyonrails.org/mailman/listinfo/rails

Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
removed_email_address@domain.invalid


#10

Sorry - I already figured that out - the benefit of the console I
guess…speeds up testing.

My code looks like this…

def list
@client = Client.find(:all)
@facility = Facility.find(:all)
@placement_pages, @placements = paginate(
:placements,
:joins => “as pl inner join clients as cl ON cl.id=pl.client_id”,
:order_by => ‘clients.last_name’,
:per_page => 14)
end

and the only problem that I apparently have left is that it completely
duplicates the listings so that each entry in placements is now listed
twice…

I have tried ‘inner’ joins and ‘left’ joins and even switched
cl.id=pl.client_id
with
pl.client_id=cl.id

and haven’t come up with a combination that doesn’t have me seeing
double.

Craig


#11

And the answer was not :joins but rather :include

wow…that was one tough learning experience.

I am gonna have to make a wiki page on table associations for dummies as
there have been too many painful experiences to get here.

Thanks everyone…

Craig


#12

Craig,

You can add an :order clause to your paginate.

It is a little more challenging if you need to add a join on another
table
to establish the order. In that case you add a :join clause. This adds
all
the fields from the join table, which will likely override some of the
fields from your main table – specifically ‘id’. To fix that you can
add a
:select option.

http://api.rubyonrails.com/classes/ActionController/Pagination.html#M000082

-Kelly