Forum: Ruby on Rails use join table in paginate

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.
F20f15806d6fd68beae60d483f050c11?d=identicon&s=25 Koen Warner (koenwarner)
on 2006-05-22 21:07
How come I allways immediately run into difficult stuff when I'm trying
some new programming language? Am I blind for the simplicity of
Ruby/Rail, which a see must be there? Anyway, don't try and answer this
rhetoric question. I have got another one for you, seemingly difficult.

I'm struggling with a n:m relationship (in a database, that is) and its
join table.

Here are my objects:

class Painting
   has_and_belongs_to_many :themes

which maps to db entity
painting
  id

class Theme
   has_and_belongs_to_many :paintings

which maps to db entity
theme
   id

And of course the join table

paintings_themes
   painting_id
   theme_id

So, paintings have one or more themes, and a theme can belong to more
paintings.

In my ExpositionController, which shows the paintings, I have:

  @painting_pages, @paintings = paginate :paintings, :per_page => 10

But now I want only to show paintings with a certain theme (still
paginated of course).

So I have to put a :condition in the paginating code. I've tried
something like:

@painting_pages, @paintings = paginate :paintings, :per_page => 10,
 :conditions => ['displayable=? AND themes[0].name=?', true, 'flowers']

which shows only displayable paintings with theme=flowers, but the
themes[0].name part doesn't compile.
I'm stuck. My mind simple goes blank on this theme (pardon the confusing
last term).
Anyone who can help me out?
Thanks.
4034df6027cbecf3f4a19a711a248536?d=identicon&s=25 Steven Hansen (rooster)
on 2006-05-22 21:43
(Received via mailing list)
The :conditions portion of you statement should be thought of as generic
sql.  So in this case you want to filter on a value in the themes
table.  I'm not positive if you need to use the :include
clause to include the "themes" table in the query.  Since your query is
using multiple tables, you may need to prefix each table column with the
table name.

@painting_pages, @paintings = paginate :paintings, :per_page => 10,
 :conditions => ['displayable=? AND themes.name=?', true, 'flowers'],
 :include => :theme
59de94a56fd2c198f33d9515d1c05961?d=identicon&s=25 Tom Mornini (Guest)
on 2006-05-22 21:49
(Received via mailing list)
On May 22, 2006, at 12:07 PM, Koen Warner wrote:

> How come I allways immediately run into difficult stuff when I'm
> trying
> some new programming language?

That's easy!

It's because when you're learning new things, everything is
difficult. :-)

> something like:
>
> @painting_pages, @paintings = paginate :paintings, :per_page => 10,
>  :conditions => ['displayable=? AND themes[0].name=?', true,
> 'flowers']

Look up :include. You can create join queries with it, then you'll be
able to specify themes.name=? as you have in mind.

I'm curious as to how you arrived at the themes[0] syntax. :conditions
are SQL, and that syntax is ruby...

--
-- Tom Mornini
F20f15806d6fd68beae60d483f050c11?d=identicon&s=25 Koen Warner (koenwarner)
on 2006-05-23 00:30
Steven Hansen wrote:
> The :conditions portion of you statement should be thought of as generic
> sql.  So in this case you want to filter on a value in the themes
> table.  I'm not positive if you need to use the :include
> clause to include the "themes" table in the query.  Since your query is
> using multiple tables, you may need to prefix each table column with the
> table name.
>
> @painting_pages, @paintings = paginate :paintings, :per_page => 10,
>  :conditions => ['displayable=? AND themes.name=?', true, 'flowers'],
>  :include => :theme

Great Steven. It works! The only thing I had to adjust in your proposal
was
 :include => :themes
in stead of
 :include => :theme

I'm still not sure as to what exactly happens, but I'll have a look at
the logfile to see the executed SQL. That will clear things up.

Thanks a lot.
F20f15806d6fd68beae60d483f050c11?d=identicon&s=25 Koen Warner (koenwarner)
on 2006-05-23 00:34
Tom Mornini wrote:
> That's easy!
>
> It's because when you're learning new things, everything is
> difficult. :-)
Guess your right.

> Look up :include.
I'll certainly look into 'include', it seems usefull as Steven pointed
out to.

> I'm curious as to how you arrived at the themes[0] syntax. :conditions
> are SQL, and that syntax is ruby...
That's why my sample code didn't work :-)
You can use the syntax in something like
  @@my_log.debug("==>ExpositionController: a painting
#{painting.themes[0].name}")
because the painting object has an array of themes, and here we'll grab
the first one. But in SQL of course this won't work.

Thanks for the helpful reply.
This topic is locked and can not be replied to.