SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    one table, many-to-many

    I have a database where one table is users, users can know other users so that there will be a many to many relation of which users know what other users. ActiveRecord assumes the names in the join table is the table+_id. If I was gonna follow ActiveRecords naming this would be:
    create table users_users(
    user_id int,
    user_id int,
    primary key(user_id, user_id)
    );
    Now that obviously won't work, so how do I deal with this?

  2. #2
    SitePoint Addict liquidautumn's Avatar
    Join Date
    Nov 2002
    Location
    Kharkov, Ukraine
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe you could add something like 'user_to_user' reference table with user_id and referenced_user_id fields to maintain many to many relationship

  3. #3
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This should work (not tested it though):

    Code:
    CREATE TABLE users_known_users (
      user_id INT,
      known_user_id INT,
      PRIMARY KEY(user_id, known_user_id)
    );
    
    class User < ActiveRecord::Base
      has_and_belongs_to_many :known_users,
                                           :join_table => 'users_known_users',
                                           :foreign_key => 'user_id',
                                           :association_foreign_key => 'known_user_id'
    end
    Now, I'm not 100% sure, but Rails might actually be able to work out the foreigh and association foreign keys by itself given the above schema, so you might be able to omit the last two keys of the habtm declaration - give it a try.

    Example Usage:

    Code:
    # Controller:
    @joe_bloggs = User.find(:name => 'Joe Bloggs')
    
    # View: 
    <% @joe_bloggs.known_users.each do |known_user| %>
      Joe Bloggs knows <%= known_user.name %>
    <% end %>

  4. #4
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll try this, thanks very much

  5. #5
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd love to be proved wrong, but a month or so ago I barked up the this tree for hours and I'm totally convinced it cannot be done with has_and_belongs_to_many. It's a limitation of that option.

    Still, I'd love to be proved wrong.

    The Agile Rails book suggests a combo of belongs_to and has_many on the same table, that may work for you.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  6. #6
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samsm
    I'd love to be proved wrong, but a month or so ago I barked up the this tree for hours and I'm totally convinced it cannot be done with has_and_belongs_to_many. It's a limitation of that option.

    Still, I'd love to be proved wrong.

    The Agile Rails book suggests a combo of belongs_to and has_many on the same table, that may work for you.
    Did you read my post?

    To the OP, did you try it?

  7. #7
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Luke Redpath
    Did you read my post?
    Yes, and I'm pretty sure I tried that.

    If you are representing to me that that works, I would love to try it again (wouldn't be the first time I thought I incorrectly eliminated a possibility), but I have wasted so ... much ... time ... on this already, I can't bare to proactively test it without some assurance that it will work.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  8. #8
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps the OP could let us know.

  9. #9
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dilbo
    I have a database where one table is users, users can know other users so that there will be a many to many relation of which users know what other users. ActiveRecord assumes the names in the join table is the table+_id. If I was gonna follow ActiveRecords naming this would be:
    create table users_users(
    user_id int,
    user_id int,
    primary key(user_id, user_id)
    );
    Now that obviously won't work, so how do I deal with this?
    I'm no DB expert, but the very premise of what you are attempting to do seems flawed to me. As I far as I can tell from your post you have a SINGLE table called "users".

    First, I don't think you make a join table from a single table. So that's probably why you can't do it in ActiveRecord. By definition, you make a join table when you "join" two tables. If you want results from a single table you execute a query against that table.

    Second, if you are just working with a single table you do not have a many-to-many relationship. A many-to-many relationship is where you have 2 tables and one row in Table A is related to one or more rows in Table B AND one row in Table B is related to one or more rows in Table A. A one-to-anything relationship requires that there be more than one table. Otherwise you just have a "one" relationship, and that ain't no relationship at all!

    I think you should show us what the schema is for your users table. It honestly sounds like more of a DB design issue than a problem with ActiveRecord.

  10. #10
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, this is all rather tiring - I've already posted a solution yet people continue to say its "not possible". Its certainly possible and just to reaffirm things, I've knocked up quick Rails test app and with one small tweak to my originally posted code, it works. I see no reason why there cannot be a many-to-many relationship between objects of a single class, or in this case, records of a single table. I do not know if many-to-many is the correct term in this case - I know a one to * relationship within a single table is known as a recursive or reflexive relationship - I don't know if this would be called the same, but it makes sense either way.

    You have a users table:

    Code:
    CREATE TABLE users (
      id INT,
      name VARCHAR(20),
      PRIMARY KEY(id)
    );
    A join table, eg:

    Code:
    CREATE TABLE users_known_users (
      user_id INT,
      known_user_id INT,
      PRIMARY KEY(user_id, known_user_id)
    );
    Now, the only change from my original code - you cannot specify the class name as "known_users" as there is no such model - just a user model. However, having code such as:

    Code:
    luke = User.find(1)
    user.users
    Isn't very clear, so you just create an alias for the users() method called known_users(). The full model:

    Code:
    class User < ActiveRecord::Base
      has_and_belongs_to_many  :users,
                               :join_table => 'users_known_users',
                               :foreign_key => 'user_id',
                               :association_foreign_key => 'known_user_id'
    
      def known_users
        self.users
      end
    end
    And the following works:

    Code:
    luke = User.create(:name => 'Luke')
    #=> #<User:0xb73d60b0 @attributes={"name"=>"Luke", "id"=>"1"}>
    luke.known_users.length
    #=> 0
    luke.known_users.create(:name => 'Dave')
    #=> #<User:0xb73be398 @new_record_before_save=true, @attributes={"name"=>"Dave", "id"=>2}, @new_record=false, @errors=#<ActiveRecord::Errors:0xb73bd858 @base=#<User:0xb73be398 ...>, @errors={}>>
    luke.known_users.length
    #=> 1
    Everybody happy now?

  11. #11
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Luke Redpath
    Now, the only change from my original code - you cannot specify the class name as "known_users" as there is no such model - just a user model.
    Just adding class_name => 'User' will work !
    Code:
    class User < ActiveRecord::Base
      has_and_belongs_to_many  :friends,
                               :join_table => 'users_known_users',
                               :foreign_key => 'user_id',
                               :association_foreign_key => 'known_user_id',
                               :class_name => 'User'
    end
    -Pratik

  12. #12
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I've done a bit of further testing, and discovered a slight flaw with the above, which is...if you connect Person A to Person B, Person B should be connected to Person A as well. This works with a traditional many-to-many because each foreign key column in the join table points to a different table. However, in this case it doesn't, so it becomes neccesary to create two rows in the join table for each many-to-many join -- not ideal, but not a major hassle either. Its easily accomplished using two one-liner association callbacks. The updated model is now:

    Code:
    class User < ActiveRecord::Base
      has_and_belongs_to_many  :users,
                               :join_table => 'users_known_users',
                               :foreign_key => 'known_user_id',
                               :association_foreign_key => 'user_id',
                               :after_add => :create_reverse_association,
                               :after_remove => :remove_reverse_association
    
      def known_users
        self.users
      end
    
      private
        def create_reverse_association(associated_user)
          associated_user.known_users << self unless associated_user.known_users.include?(self)
        end
    
        def remove_reverse_association(associated_user)
          associated_user.known_users.delete(self) if associated_user.known_users.include?(self)
        end
    end
    Note the checks to see if a relationship already exists inside the callbacks - these are nessecary otherwise you would end up with an infinite loop (as each association callback is creating another association, this in turn would call another association callback to the original user and so on...).

  13. #13
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, like I said, I'm happy! I wish I could find what I was doing that didn't work previously, I could have sworn it was exactly this, but obviously not.

    A little note for anyone watching, (this confused me for a second), known_users.create does add a user to the database but it doesn't seem to commit the relationship to the database. Looks to me like you have to:
    Code:
    >> luke.known_users.create(:name => 'Dave').save
    
    or
    
    >> dave = User.create(:name => 'Dave')
    >> luke.known_users << dave
    Or is there a better way to do it?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  14. #14
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems that is the expected behaviour. You could also bypass the temp variable:

    Code:
    luke.known_users << User.create(:name => 'Dave')

  15. #15
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Luke Redpath
    It seems that is the expected behaviour. You could also bypass the temp variable:
    Code:
    luke.known_users << User.create(:name => 'Dave')
    Good point! Thank you for looking into all this, by the way.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  16. #16
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Np

  17. #17
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Luke's method works perfectly, thanks everyone for your great help

  18. #18
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm...

    Seams to me, that what you've got to do - basically - is fart about all day, just to get this thing to work with the ActiveRecord?

    I'm sure there would - proberly - be a better approach to this no?

  19. #19
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston
    Seams to me, that what you've got to do - basically - is fart about all day, just to get this thing to work with the ActiveRecord?

    I'm sure there would - proberly - be a better approach to this no?
    It's actually super easy. I can't believe that I didn't have this working when I did this last time. Simply careless on my part.

    You just chose habtm, enter the custom database details because you aren't doing the usual tableA to tableB thing and you are good to go. Really, it's like 2 lines of code to get the model working.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  20. #20
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are you talking about? ActiveRecord works out of the box for no associations and with one or two lines of code for the common associations, and one or two lines of code for single table inheritance. And it took me all of an hour to work out how do do many to many relationships within the same table (hardly a common requirement).

    ActiveRecord can also be set up to work with hierarchial structures using one or two lines of code.

    You hardly have to "fart about all day". What are you babbling on about? Do you even have a point, or something worthwhile to contribute to this forum? You may contribute to the PHP forum but it seems that all you do in here is ramble on about nothing you know little to nothing about. Why do you even bother?

  21. #21
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Luke Redpath
    You may contribute to the PHP forum but it seems that all you do in here is ramble on about nothing you know little to nothing about. Why do you even bother?
    How else do you get to 4,000+ posts?

    BTW - Luke, didn't mean to imply by my earlier post that your solution wouldn't work. I'm still learning RoR, and in the original post it seemed as though the poster was going about things the wrong way. I guess as it turns out Rails uses slightly different terminology then I am used to.

    In the past I would have called the table users_known_users a "Linking Table" or a "Lookup Table". When I hear "Join Table" I think of a table created by executing a SQL JOIN statement, but I guess in RoR a "Join Table" == "Linking Table".

    Or maybe I have everything wrong.

  22. #22
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jpease
    How else do you get to 4,000+ posts?

    BTW - Luke, didn't mean to imply by my earlier post that your solution wouldn't work. I'm still learning RoR, and in the original post it seemed as though the poster was going about things the wrong way. I guess as it turns out Rails uses slightly different terminology then I am used to.

    In the past I would have called the table users_known_users a "Linking Table" or a "Lookup Table". When I hear "Join Table" I think of a table created by executing a SQL JOIN statement, but I guess in RoR a "Join Table" == "Linking Table".

    Or maybe I have everything wrong.
    No you are right, what I referred to as a "join table" is simply a table that links two tables in a many-to-many relationship, like you said.

  23. #23
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How else do you get to 4,000+ posts?


    What am I babbling on about? Well, despite the manner in which I posted, what I was actually asking, is that is there not another way that you could tackle this, bar using the Active Record?

    I would have thought that was a bit obvious really, if you took the time not to read too much in the part about farting about all day

    There are alternatives, I'm just asking if they are appropriate that's all. As to me not knowing much about Ruby, well that is why I visit the forum in the first place, to learn more about it.

    Maybe I'll just not bother the next time, if that's your attitude huh?

  24. #24
    SitePoint Addict jpease's Avatar
    Join Date
    Jul 2002
    Location
    In the network.
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston

    Maybe I'll just not bother the next time, if that's your attitude huh?
    I don't think anybody is saying "don't come around here anymore". But I think you have to admit the manner of your posts (at least the ones I have seen) in the Ruby forum have had a somewhat inflamatory tone.

    If you are genuinely interested in learning more about Ruby / Rails (and not just trying to start fights) then something along the lines of :

    Quote Originally Posted by What I Say I Meant was...
    Pardon me my fellow programmers...

    Using ActiveRecord to solve this problem seems to be more complicated then I would think it needs to be.

    Might there be a better solution?
    As opposed to :

    Quote Originally Posted by What I Actually Said was...
    Umm...

    Seams to me, that what you've got to do - basically - is fart about all day, just to get this thing to work with the ActiveRecord?

    I'm sure there would - proberly - be a better approach to this no?
    Of course at 4,000+ posts I would imagine you already know all that. So, what are we to conclude, except that you are just posting to cause trouble?

  25. #25
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've gotten the user example above working. I've been trying to add a third field into the user_known_user table. I'm calling it "introduced_by_id". It is essentially stating which user introduced the two users together. I've added another has_and_belongs to many relationship called "introduced_by" with a similar layout to the known_users example. Whenever I try to call it however I always seem to retrieve the 2nd row in the table and not the third. Does anyone why this is happening?

    Thanks!
    Last edited by Denyzine; Feb 23, 2006 at 15:25. Reason: Quesiton Update


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •