Forum: Ruby on Rails How do you paginate a SQL result with conditions?

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.
450046d5f684ae5001c0b1a079d447d4?d=identicon&s=25 Sean Schertell (df-sean)
on 2005-12-10 12:24
Hi guys and gals,

I've been googling and scouring the wiki and re-reading the agile book
for hours to no avail.

Can someone please explain to me in a nutshell how I can paginate a
result with conditions?  Here's a simple example.


## Model ##
def index
  @members =  Member.find_by_first_name('Sean')
end


## Controller ##
def members
    @member_pages, @members = paginate :members, :per_page => 40
end


## View ##
<%= render_partial "partial/paginator", @member_pages %>


... then I've got a partial that does the actual links and stuff.

Everything works great except that my pages show all results in the
database, not just the ones with first_name of 'Sean'.  What am I doing
wrong?  This is driving me crazy :-)
C27e9fe209955cdd75f249e7466302e9?d=identicon&s=25 jdell (Guest)
on 2005-12-11 00:37
(Received via mailing list)
Sean Schertell wrote:
> Hi guys and gals,
>
> I've been googling and scouring the wiki and re-reading the agile book
> for hours to no avail.
>
> Can someone please explain to me in a nutshell how I can paginate a
> result with conditions?  Here's a simple example.
>
[snip]

I had some trouble with finding good examples.

Have a look here:
http://api.rubyonrails.org/classes/ActionControlle...

I adapted the 'custom' pagination example to get this example which
paginates users by current domain:

  def list
    @user_pages = Paginator.new self,
                   User.count(["domain_id = ?",
session[:user].domain_id]),
                   20,
                   @params['page']
    @users = User.find(:all, :order => 'last_name',
                             :limit  =>  @user_pages.items_per_page,
                             :offset =>  @user_pages.current.offset,
                             :conditions => ["domain_id = ?",
session[:user].domain_id])
  end

I'm a noob, so I'd appreciate feedback if I'm doing something dumb here.

Regards,
John
C27e9fe209955cdd75f249e7466302e9?d=identicon&s=25 jdell (Guest)
on 2005-12-11 00:54
(Received via mailing list)
John Dell wrote:
> session[:user].domain_id])
>  end
>
> I'm a noob, so I'd appreciate feedback if I'm doing something dumb here.
Well, I can pick on myself.  Such is the rate of my progress with RoR
:-), I saw somebody do this in another email thread so the above
becomes:

  def list
    @user_pages, @users = paginate :user, {
      :per_page => 20,
      :conditions => ["domain_id = ?", session[:user].domain_id],
    }
  end

Much cleaner and nicer!
450046d5f684ae5001c0b1a079d447d4?d=identicon&s=25 Sean Schertell (df-sean)
on 2005-12-11 02:49
> Much cleaner and nicer!

I think I get it.  So that means that the "fully-automated" version of
the paginate helper is only for paginating raw database tables with no
conditions?
C27e9fe209955cdd75f249e7466302e9?d=identicon&s=25 jdell (Guest)
on 2005-12-11 04:25
(Received via mailing list)
Sean Schertell wrote:
> I think I get it.  So that means that the "fully-automated" version of
> the paginate helper is only for paginating raw database tables with no
> conditions?
>
Sorry I didn't say that explicitly.  But yes, without :conditions you
are paginating the entire model (table).

Regards,
John
450046d5f684ae5001c0b1a079d447d4?d=identicon&s=25 Sean Schertell (df-sean)
on 2005-12-11 04:39
Thanks for being so helpful guys.  One more thing I'm still unclear
about though.  If my model fetches a table with conditions, can't I just
use that instance variable ( @live_members in my example ) instead of
repeating myself in the controller?  In other words, doesn't it make
sense to set my conditions in the model and then just paginate that?

Sean



jdell wrote:
> Sean Schertell wrote:
>> I think I get it.  So that means that the "fully-automated" version of
>> the paginate helper is only for paginating raw database tables with no
>> conditions?
>>
> Sorry I didn't say that explicitly.  But yes, without :conditions you
> are paginating the entire model (table).
>
> Regards,
> John
C27e9fe209955cdd75f249e7466302e9?d=identicon&s=25 jdell (Guest)
on 2005-12-11 05:42
(Received via mailing list)
Sean Schertell wrote:
> Thanks for being so helpful guys.  One more thing I'm still unclear
> about though.  If my model fetches a table with conditions, can't I just
> use that instance variable ( @live_members in my example ) instead of
> repeating myself in the controller?  In other words, doesn't it make
> sense to set my conditions in the model and then just paginate that?
>
Hmm.  My 2 cents... The only place I have my conditions in the
controller are in the 'list' method for pagination.  I don't see how
that's any more work than putting it in the model.  Also, if you do put
it in the model (again not sure what that would look like), then it
seems to me that you have crippled the model.  The model is for
modelling and the controller is for controlling.  I think you would be
trying to put controller type logic in the model if you did that.

Regards,
John
450046d5f684ae5001c0b1a079d447d4?d=identicon&s=25 Sean Schertell (df-sean)
on 2005-12-11 08:29
I see your point John.  Thanks for the interesting conversation.  My
idea of putting some simple conditions in the model was stolen from page
68 of the AWDR book where Dave says:
-----
We want to program at a decent level of abstraction, so letâ??s just
assume we can ask the model for
a list of the products we can sell.

def index
  @products = Product.salable_items
end
-----

It seemed like a smart idea at the time, but I think your reasoning
makes sense -- keep the login in the controller.  Any thoughts on this?
Has Dave Thomas led me astray?

Sean




jdell wrote:
> Sean Schertell wrote:
>> Thanks for being so helpful guys.  One more thing I'm still unclear
>> about though.  If my model fetches a table with conditions, can't I just
>> use that instance variable ( @live_members in my example ) instead of
>> repeating myself in the controller?  In other words, doesn't it make
>> sense to set my conditions in the model and then just paginate that?
>>
> Hmm.  My 2 cents... The only place I have my conditions in the
> controller are in the 'list' method for pagination.  I don't see how
> that's any more work than putting it in the model.  Also, if you do put
> it in the model (again not sure what that would look like), then it
> seems to me that you have crippled the model.  The model is for
> modelling and the controller is for controlling.  I think you would be
> trying to put controller type logic in the model if you did that.
>
> Regards,
> John
C27e9fe209955cdd75f249e7466302e9?d=identicon&s=25 John Dell (Guest)
on 2005-12-11 23:30
(Received via mailing list)
Hi Sean,

Sean Schertell wrote:
> end
> -----
>
> It seemed like a smart idea at the time, but I think your reasoning
> makes sense -- keep the login in the controller.  Any thoughts on this?
> Has Dave Thomas led me astray?
>
Hmm.  I guess it comes down to how to implement 'proper' MVC.  I just
read that page in Dave's book and on further reflection, I think you are
right (can't argue with Dave!)

I re-implemented a few of my models with this technique and removed the
extra :condition logic from my controller and I like it.  I think I just
learned something important about MVC that I didn't understand before.
My definition of what a controller does was a bit skewed, I've been
putting too much 'model' logic in my controllers.

For a more difficult model than's Dave's example, the model SQL would
likely require dynamic :conditions that vary with user input.  So the I
guess the controller would pass the arguments to the model, which would
be responsible for constructing the query and return the result.   So,
is the model responsible for validating the SQL arguments, or does the
controller do that?

Talk about a win-win, I try to answer your question and you end up
teaching me about to better structure my code!

John
C27e9fe209955cdd75f249e7466302e9?d=identicon&s=25 John Dell (Guest)
on 2005-12-12 00:30
(Received via mailing list)
John Dell wrote:
> I re-implemented a few of my models with this technique and removed
> the extra :condition logic from my controller and I like it.  I think
> I just learned something important about MVC that I didn't understand
> before.  My definition of what a controller does was a bit skewed,
> I've been putting too much 'model' logic in my controllers.
Sorry, I spoke too soon.  I thought it was working, but my example was
too simple.  I now have the same problem that Sean had when he
originally posted this question!

It seems to me that this is a design choice or a limitation of
paginate.    I can't make paginate use a custom method in my model that
implements find and already has :conditions.  I tried the :class_name
option to force it to use my method in paginate, but I couldn't get that
to work either.

So, I'm back to my revised solution. I'd be happy for someone enlighten
us on how to paginate a custom model method that has the conditions in
the model.  Or, perhaps explain why this shouldn't be done?

Thanks!
John
This topic is locked and can not be replied to.