I have 3 models
model1
belongs_to :model2
model2
belongs_to :model3
model3
Now I want to do a search on fields in model1 and model3. Currently I do:
foo = Model1.find_by_sql("select field1,field2 from ((model1 m1 join
model2 m2 on m1.model2_id=m2.id) join model3 m3 on m2.model3_id=m3.id)
where m1.field1=X and m3.field2=Y")
This works, however if I want to paginate the results I have a problem
the standard paginate code looks something like:
@object_pages = Paginator.new self, total, per_page,
@params[''page'']
@objects = model.find_by_sql(sql + " LIMIT #{per_page} " +
"OFFSET
#{@object_pages.current.to_sql[1]}")
The problem is that I don''t know the value of ''total''
when
Paginator.new is called. I usually just give it the maximum number of
objects however one of the users clicked on the next page link when
there was no next page and hit an error.
This style call:
@person_pages, @people = paginate :people, :order_by => ''last_name,
first_name''
works fine on single tables but I can''t seem to get the syntax right
for the 3 table join I have listed above.
- Michael
On 7/29/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > This style call: > @person_pages, @people = paginate :people, :order_by => ''last_name, first_name'' > > works fine on single tables but I can''t seem to get the syntax right > for the 3 table join I have listed above. >Does this help? Paginate an already-fetched result set: http://www.bigbold.com/snippets/posts/show/389 -- Jack Baty Fusionary Media - http://www.fusionary.com Weblog - http://blog.jackbaty.com
That looks like exactly what I need, funny thing is that when I was doing a google search it was coming up with a different snipet from bigbold. - Michael On 7/29/05, Jack Baty <jackbaty-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 7/29/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > This style call: > > @person_pages, @people = paginate :people, :order_by => ''last_name, first_name'' > > > > works fine on single tables but I can''t seem to get the syntax right > > for the 3 table join I have listed above. > > > > Does this help? > > Paginate an already-fetched result set: > http://www.bigbold.com/snippets/posts/show/389 > > > -- > Jack Baty > Fusionary Media - http://www.fusionary.com > Weblog - http://blog.jackbaty.com > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Ok small problem, when I click next page I am getting an error:
cannot convert nil into String
app/controllers/tests_controller.rb:154:in `+''
app/controllers/tests_controller.rb:154:in `search''
line 154 in tests_controller looks like:
@test_results = Test.find_by_sql("SELECT test.* FROM (((testnotes join
tests on testnotes.test_id=tests.id) join teams on
testnotes.team_id=teams.id)\
join problemnames on tests.problemname_id=problemnames.id) where
#{@params[''search'']} group BY tests.incident_number")
@test_pages, @tests = paginate_collection @test_results, :per_page => 50
And before anyone says it, yes I know I shouldn''t be using a param in
an SQL search without sanitizing it first. That will be fixed after I
actually get this working.
- Michael
On 7/29/05, Michael King <kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> That looks like exactly what I need, funny thing is that when I was
> doing a google search it was coming up with a different snipet from
> bigbold.
>
> - Michael
>
> On 7/29/05, Jack Baty
<jackbaty-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 7/29/05, Michael King
<kingmt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > >
> > > This style call:
> > > @person_pages, @people = paginate :people, :order_by =>
''last_name, first_name''
> > >
> > > works fine on single tables but I can''t seem to get the
syntax right
> > > for the 3 table join I have listed above.
> > >
> >
> > Does this help?
> >
> > Paginate an already-fetched result set:
> > http://www.bigbold.com/snippets/posts/show/389
> >
> >
> > --
> > Jack Baty
> > Fusionary Media - http://www.fusionary.com
> > Weblog - http://blog.jackbaty.com
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>