Rails - Feb 2005 - has_and_belongs_to_many problems

Hi all,

Before I start, I apologize for the long post.  With that done, I would like 
you all to imagine a world where I thought it might be a good idea to 
implement something like del.icio.us, mainly just to see if I can.

I have the following tables/fields :

   bookmarks ( id, url, created_on )
   tags ( id, name )
   users ( id, login, password )
   bookmarks_tags_users ( bookmark_id, tag_id, user_id )


I have Bookmark, Tag, User models, each with two has_and_belongs_to_many 
relations setup something like the following (These are from Bookmark, as an 
example) :

   has_and_belongs_to_many :users, :finder_sql => ''SELECT DISTINCT
users.*
FROM bookmarks_tags_users, users WHERE users.id = bookmarks_tags_users.user_id 
AND bookmark_id = #{id}'', :uniq => true
   has_and_belongs_to_many :tags, :finder_sql => ''SELECT DISTINCT
tags.* FROM
bookmarks_tags_users, tags WHERE tags.id = bookmarks_tags_users.tag_id AND 
bookmark_id = #{id}'', :uniq => true


In addition, in the bookmark model, I have :

   def tags_for_user( user )
     return Bookmark.find_by_sql [ "SELECT DISTINCT tags.* FROM 
bookmarks_tags_users, tags WHERE tags.id = bookmarks_tags_users.tag_id AND 
bookmark_id = ? AND user_id = ?", id, user.id ]
   end


In the bookmark controller, I''ve got :

   def list
     @bookmarks = @session[ "user" ].bookmarks
   end

and in the bookmark/list.rhtml view, I''ve got something like :

   <% @bookmarks.each do |bookmark| %>
    <strong><%= bookmark.id %> : <%= h bookmark.url
%></strong><br />
 
 

    <pre><%= h bookmark.inspect %></pre>
 
 

    All : <% bookmark.tags.each do |tag| %>
     <%= h tag.name %>
    <% end %><br />
 
 

    User : <% bookmark.tags_for_user( @session[ "user" ] ).each do
|tag| %>
     <%= h tag.name %>
    <% end %><br /><br />
 
 

   <% end %>

(All: should list all tags that all users have for this bookmark - User: is 
the list for the user logged in)


So, I am getting the correct list of bookmarks back for @bookmarks.  It is 
also displaying the tags for bookmark.tags_for_user correctly.  However, for 
the second bookmark, the All: tags are being displayed incorrectly (There is 
one user, so All and User should always say the same thing) :

1 : http://www.google.com/
#<Bookmark:0xb7b93d50
@attributes={"created_on"=>"2005-02-28 15:34:04.679924",
"url"=>"http://www.google.com/",
"id"=>"1"}>
All : search google
User : search google

2 : http://search.msn.com/
#<Bookmark:0xb7b93cec
@attributes={"created_on"=>"2005-02-28 16:09:52.281833",
"url"=>"http://search.msn.com/",
"id"=>"2"}>
All : search google
User : search google shit


This is my development.log, take a look at the "Tag Load" lines :

   Parameters: {:controller=>"bookmark",
:"bookmark/list.html/list"=>nil,
:action=>"list"}
Rendering bookmark/list (200 OK)
   Bookmark Load (0.011113)   SELECT DISTINCT bookmarks.* FROM 
bookmarks_tags_users, bookmarks WHERE bookmarks.id = 
bookmarks_tags_users.bookmark_id AND user_id = 1
   SQL (0.051961)   SELECT column_name, column_default, 
character_maximum_length, data_type FROM information_schema.columns WHERE 
table_catalog = ''gimpu_development'' AND table_schema =
''public'' AND table_name
= ''bookmarks'';
   Tag Load (0.006634)   SELECT DISTINCT tags.* FROM bookmarks_tags_users, 
tags WHERE tags.id = bookmarks_tags_users.tag_id AND bookmark_id = 1
   SQL (0.050964)   SELECT column_name, column_default, 
character_maximum_length, data_type FROM information_schema.columns WHERE 
table_catalog = ''gimpu_development'' AND table_schema =
''public'' AND table_name
= ''tags'';
   Bookmark Load (0.005531)   SELECT DISTINCT tags.* FROM 
bookmarks_tags_users, tags WHERE tags.id = bookmarks_tags_users.tag_id AND 
bookmark_id = 1 AND user_id = 1
   Tag Load (0.005471)   SELECT DISTINCT tags.* FROM bookmarks_tags_users, 
tags WHERE tags.id = bookmarks_tags_users.tag_id AND bookmark_id = 1
   Bookmark Load (0.006061)   SELECT DISTINCT tags.* FROM 
bookmarks_tags_users, tags WHERE tags.id = bookmarks_tags_users.tag_id AND 
bookmark_id = 2 AND user_id = 1
Completed in 0.172163 (5 reqs/sec) | Rendering: 0.166796 (96%) | DB: 0.211712 
(122%)


As you can see, it is using "bookmark.id = 1" for both Tag Load
queries.  Any
ideas why?  I just can''t see what I''m doing wrong, so any
suggestions would be
gratefully received.  Thanks in advance.

Best Regards,
Carl.