Will paginate is dropping me the pages #211

Closed
workdreamer opened this Issue Dec 13, 2011 · 6 comments

Projects

None yet

2 participants

@workdreamer

It's only in one case when i am ordering a statute.

  @cities.ordering(params[:name])
 
   #raise @cities.length.inspect #if i do this here  i receive 14
   @cities = @cities.paginate(:page => params[:page])
 

if the ordering is no executed i receive two pages. otherwise it return only one page. I'm doing only a ORDER BY cities.name DESC

Thank you.

@mislav
Owner

Sorry, I have no idea what you said. First, are you using Active Record in Rails 3 or some other combination? Second, what's your problem? What's the code which fails for you, what did you expect by that code and what did you get as result?

@workdreamer

Sorry for the late response...

i've created a sample project here:

https://github.com/workdreamer/WillPaginateSample

if you do rake db:seed you have some data.

Ok clues for this:

if you config WillPaginate.per_page = 5, the pagination appear but only for 10 results

if you remove the :group on scope, it works perfectly. but on my real case i need the group by because the MS SQL.

I hope it helps to figure out the problem.

Thank you very much.

@workdreamer

Hi, any news?

@mislav
Owner

Yes, I have found your problem. Not thanks to your help, though – you have ignored my question "what did you expect by that code and what did you get as result?" I had to open your project and guess which controller action exhibits the problem and what did you expect in the first place.

However before I explain it to you, you'll have to tell me what did you want to display. Did you want to show list of all entities (people) with each having their city listed, or did you want to show all cities, each listing all the entities that belong to it?

@workdreamer

Hi, thank you for your attention. Sorry to avoid your question before, but i've supposed if i give to you an example you would understand better my issue.

I want to display all entities (having or not a city) ordered descending by the name of the city (although this could be nil).
Since i have 13 entities and 5 cities ( alttough some entities don't have any city associated ), i would expect to see 2 pages (first page with 10 entites and the second with 3 on this particular case). The problem is that i only get one page.

The problem in this case is because i use the group by, and is really necessary for the MS-SQL

Thank you for your time and attention.

@mislav
Owner

I appreciate you providing me an example, but when this example is not small, but a whole Rails app, you have to indicate which controller action should I look into, what did you expect to get with that action, and what happened in turn. But OK, this is behind us now.

Your problem is a combination of 2 problems: one is that you're using GROUP BY wrong, and the other is that Active Record didn't perform consistently for your particular query. This second thing is likely a bug with Active Record, but we're not going to investigate it because we're going to work around it.

You're using GROUP BY wrong. You want to display all 13 entities with each listing their city, but if you properly group entities by city, you're going to finish with just 6 results (one for each 5 cities and one extra with a blank city). This is what GROUP BY does – collapses together all rows which share the same value (a city name, even if it's blank) into a single row. You don't want that.

What you want is to select all entities, but just order them by city name. Since the city name is in another table, you have to join this table on entities. You already did this properly, and you ordered the result set in the right way. What you didn't have to do is the whole :group clause. You remove that, and things will probably start working properly for you.

Something like this should work:

Entity.join(:cities).order('cities.name').limit(10).page(1)

I haven't tried it. If a simple join(:cities) doesn't work, then just do what you did previously: write the join clause manually.

@mislav mislav closed this Jan 10, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment