Use join table in paginate

Rails
1

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.

2

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

3

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

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

4

Steven H. 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.

5

Tom M. wrote:

That’s easy!

It’s because when you’re learning new things, everything is
difficult. :slight_smile:
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 :slight_smile:
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.