Rails - Jun 2005 - Help with insert_sql on has_and_belongs_to_many

Hi, I''m having some problems understanding some options for the
has_and_belongs_to_many relationship.

Here''s one of the relationships for the class Paper (there''s
two other
similar relationships in the model):

  has_and_belongs_to_many :owners, :class_name => ''User'', 
    :finder_sql =>
      ''SELECT DISTINCT users.*'' +
      ''  FROM users, papers_users'' +
      ''  WHERE papers_users.paper_id = #{id} AND papers_users.role
\''owner_of\'' AND users.id = papers_users.user_id'' +
      ''  ORDER BY users.id'',
    :insert_sql =>
      ''INSERT INTO papers_users'' +
      ''  (user_id, paper_id, role)'' +
      ''  values (#{user.id}, #{id},
\''owner_of\'')'',
    :delete_sql =>
      ''DELETE FROM papers_users'' +
      ''  WHERE user_id = #{user.id} AND paper_id = #{id} AND role
\''owner_of\''''

I need to refer to the user id in some way in the insert_sql and in
the delete_sql, but I don''t know how. Does anyone have any advice?
(I''d prefer not to follow the advice from David on page 234 of the
Rails book, if it can be at all avoided; mainly because my brain is
currently destroyed).

There HAS to be a way to refer to the object being sent to
paper.owners<<, right?

Thanks, 
Dave

-- 
Site: http://dave.antidisinformation.com/

On 6/8/05, David Barrett
<david.barrett-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Hi, I''m having some problems understanding some options for the
> has_and_belongs_to_many relationship.
> 
> Here''s one of the relationships for the class Paper
(there''s two other
> similar relationships in the model):
Unfortunately I can''t help with the finder and insert problems
you''re
having,  however this is the same kind of problem I see people having
with java development.

Why don''t you try something like
 
class Owner < ActiveRecord::Base
  has_and_belongs_to_many :books
  belongs_to :user
end

class User < ActiveRecord::Base
  has_one :owner
  # etc.
end

This is the ''Role Archetype'' from Java Modelling in Colour
with UML
(http://www.pcoad.com/download/bookpdfs/jmcuch01.pdf) which I find
extremely useful when building systems.
 
>   has_and_belongs_to_many :owners, :class_name =>
''User'',
>     :finder_sql =>
>       ''SELECT DISTINCT users.*'' +
>       ''  FROM users, papers_users'' +
>       ''  WHERE papers_users.paper_id = #{id} AND papers_users.role
> \''owner_of\'' AND users.id =
papers_users.user_id'' +
>       ''  ORDER BY users.id'',
>     :insert_sql =>
>       ''INSERT INTO papers_users'' +
>       ''  (user_id, paper_id, role)'' +
>       ''  values (#{user.id}, #{id},
\''owner_of\'')'',
>     :delete_sql =>
>       ''DELETE FROM papers_users'' +
>       ''  WHERE user_id = #{user.id} AND paper_id = #{id} AND role
> \''owner_of\''''
> 
> I need to refer to the user id in some way in the insert_sql and in
> the delete_sql, but I don''t know how. Does anyone have any advice?
> (I''d prefer not to follow the advice from David on page 234 of the
> Rails book, if it can be at all avoided; mainly because my brain is
> currently destroyed).
> 
> There HAS to be a way to refer to the object being sent to
> paper.owners<<, right?
> 
> Thanks,
> Dave
> 
> --
> Site: http://dave.antidisinformation.com/
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

> I need to refer to the user id in some way in the insert_sql and in
> the delete_sql, but I don''t know how. Does anyone have any advice?
> (I''d prefer not to follow the advice from David on page 234 of the
> Rails book, if it can be at all avoided; mainly because my brain is
> currently destroyed).
> 
> There HAS to be a way to refer to the object being sent to
> paper.owners<<, right?
Try #{record.id}.  I made a patch for this because it wasn''t
implemented with delete_sql, and I believe insert_sql is using the
same thing (which is why I chose ''record'').  Finding a simpler
''rails''
way is definitely recommended, however :)

-- 
rick
http://techno-weenie.net

On 6/7/05, Rick Olson
<technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> > I need to refer to the user id in some way in the insert_sql and in
> > the delete_sql, but I don''t know how. Does anyone have any
advice?
> > (I''d prefer not to follow the advice from David on page 234
of the
> > Rails book, if it can be at all avoided; mainly because my brain is
> > currently destroyed).
> >
> > There HAS to be a way to refer to the object being sent to
> > paper.owners<<, right?
> 
> Try #{record.id}.  I made a patch for this because it wasn''t
> implemented with delete_sql, and I believe insert_sql is using the
> same thing (which is why I chose ''record'').  Finding a
simpler ''rails''
> way is definitely recommended, however :)
I''ll give that a go. If that doesn''t work, I''ll have
to go with three
different join tables (destroyed brain not think laterally).

Thanks Rick.

Michael - I''ll have a look at that PDF, cheers.

Dave
> 
> --
> rick
> http://techno-weenie.net
> 

-- 
Site: http://dave.antidisinformation.com/

On 6/8/05, David Barrett
<david.barrett-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 6/7/05, Rick Olson
<technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > I need to refer to the user id in some way in the insert_sql and
in
> > > the delete_sql, but I don''t know how. Does anyone have
any advice?
> > > (I''d prefer not to follow the advice from David on page
234 of the
> > > Rails book, if it can be at all avoided; mainly because my brain
is
> > > currently destroyed).
> > >
> > > There HAS to be a way to refer to the object being sent to
> > > paper.owners<<, right?
> >
> > Try #{record.id}.  I made a patch for this because it wasn''t
> > implemented with delete_sql, and I believe insert_sql is using the
> > same thing (which is why I chose ''record'').  Finding
a simpler ''rails''
> > way is definitely recommended, however :)
> 
> I''ll give that a go. If that doesn''t work, I''ll
have to go with three
> different join tables (destroyed brain not think laterally).
> 
> Thanks Rick.
> 
> Michael - I''ll have a look at that PDF, cheers.
The PDF is hugely overkill for what you''re trying to do.  All
I''m
suggesting is creating seperate classes for owner, creator, etc.  
This simplifies  your application hugely at the cost of one extra
level of associations.

Paper belongs_to :owner
Paper belongs_to :creator
etc.

so to get the creators name instead of 

@paper.creator.name 

you get 

@paper.creator.user.name

> Dave
> 
> >
> > --
> > rick
> > http://techno-weenie.net
> >
> 
> 
> --
> Site: http://dave.antidisinformation.com/
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Cheers

Koz

Unfortunately, the two other relationships ("author" and
"review")
are, by necessity, many to many. A paper may have several authors and
several reviewers, each of whom may be writing for and reviewing
several papers.

I know I didn''t mention these in any detail before.

This project is a small, non-commercial project with the main goals of:

1) Help a postgrad friend and I learn Rails and Ruby.
2) Provide something useful to my friend and fellow postgrads.

Getting something working, and learning from it, are what we''re
looking for. I''ve spent a couple of days being frustrated by lack of
progress (I don''t know Ruby or Rails very well *at all*[1]), and just
now the sweet joy of having one of our features working has hit.

As Rick''s solution involved a search and replace, that''s what
we''ll go
with for now. I''ll make sure that later in the project we''ll
look at
implementing your idea (and I''m sure I can keep the same interfaces /
change the interfaces then without incident), but right now that''ll
interfere with goal number 1.

Changing the model is not a priority, even if it means getting things
more "correct". That said, I''m not sure either my friend or I
know
enough about Ruby or Rails yet to know how to do anything "correctly"
:)

I am a guy who spent twenty minutes wondering why the heck
paper.owners.includes? was reporting a method not found error.

Thanks again,
Dave

1: I am not used to calling functions without braces, and because of
this symbols seemed to be doing quite a lot of "magic". That took far
too long to get my head around.

On 6/8/05, Michael Koziarski
<koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 6/8/05, David Barrett
<david.barrett-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > On 6/7/05, Rick Olson
<technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > I need to refer to the user id in some way in the insert_sql
and in
> > > > the delete_sql, but I don''t know how. Does anyone
have any advice?
> > > > (I''d prefer not to follow the advice from David on
page 234 of the
> > > > Rails book, if it can be at all avoided; mainly because my
brain is
> > > > currently destroyed).
> > > >
> > > > There HAS to be a way to refer to the object being sent to
> > > > paper.owners<<, right?
> > >
> > > Try #{record.id}.  I made a patch for this because it
wasn''t
> > > implemented with delete_sql, and I believe insert_sql is using
the
> > > same thing (which is why I chose ''record''). 
Finding a simpler ''rails''
> > > way is definitely recommended, however :)
> >
> > I''ll give that a go. If that doesn''t work,
I''ll have to go with three
> > different join tables (destroyed brain not think laterally).
> >
> > Thanks Rick.
> >
> > Michael - I''ll have a look at that PDF, cheers.
> 
> The PDF is hugely overkill for what you''re trying to do.  All
I''m
> suggesting is creating seperate classes for owner, creator, etc.
> This simplifies  your application hugely at the cost of one extra
> level of associations.
> 
> Paper belongs_to :owner
> Paper belongs_to :creator
> etc.
> 
> so to get the creators name instead of
> 
> @paper.creator.name
> 
> you get
> 
> @paper.creator.user.name
> 
> 
> > Dave
> >
> > >
> > > --
> > > rick
> > > http://techno-weenie.net
> > >
> >
> >
> > --
> > Site: http://dave.antidisinformation.com/
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> 
> 
> --
> Cheers
> 
> Koz
> 

-- 
Site: http://dave.antidisinformation.com/