How do you paginate a SQL result with conditions?


#1

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 :slight_smile:


#2

Sean S. 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/ActionController/Pagination.html

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


#3

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?


#4

John D. 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!


#5

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 S. 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


#6

Sean S. 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


#7

Sean S. 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


#8

Hi Sean,

Sean S. 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 T. 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


#9

John D. 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


#10

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 T. led me astray?

Sean

jdell wrote:

Sean S. 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