SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist ikeo's Avatar
    Join Date
    Oct 2004
    Location
    Austin Texas
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not getting the associated columns when using :join in find

    I have a users table that has these columns
    [id/twitter_id/name/twitter_name/twitter_image]

    and a tweets table that has these
    [id/twitter_id/text/date]

    and I'm trying to do a custom join by using
    Code Ruby:
    @tweets = Tweet.find(:all, :joins => 'LEFT JOIN users ON users.twitter_id = tweets.twitter_id' )

    however doing an @tweets.inspect shows that only columns in the tweets table are being returned? (so there are not columns from the users table in the result set)

    Does anyone know why this is?

    I originally tried
    Code Ruby:
    @tweets = Tweet.find_by_sql('SELECT * FROM tweets, users where tweets.twitter_id = users.twitter_id;' )

    but that was doing the same thing.
    I'm a bit confused, because I was certain the column/attributes from the users table should have been returned as attributes?

  2. #2
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your joins aren't really doing anything.

    Do you have a "twitter" table?

    If not, I think you could simplify your system by having:

    users table that has these columns
    [id/name/twitter_name/twitter_image]

    and a tweets table that has these
    [id/user_id/text/date]

    Then in your User model you'd add:
    Code:
    has_many :tweets
    And in the Tweets model you'd add:
    Code:
    belongs_to :user
    Once that is in place, you'll be able to do this:
    Code:
    #Get a user
    user = User.find(:first)
    all_of_this_users_tweets = user.tweets
    
    #Get a tweet
    tweet = Tweet.find(:first)
    user_who_owns_tweet = tweet.user

  3. #3
    SitePoint Evangelist ikeo's Avatar
    Join Date
    Oct 2004
    Location
    Austin Texas
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your joins aren't really doing anything.
    ---- I don't think that's correct. Both tables have a twitter_id that I'm joining on to get data. I'm running that query in the find_by_sql statement in mysql itself and it is returning data.

    ---- As for your other points, I'm well aware of how to setup associations in rails, however the database structure has to be like that for a particular reason (The twitter id, should be the primary key on the table, but because of the way I am creating new user records.
    I can't always get the twitter_id of the user at the moment the record is created, so I had to go with this model)

    This is why I'm trying to pull the records using a join, because I need the user information for each tweet I try to display.

    Does this make any sense?

  4. #4
    SitePoint Evangelist ikeo's Avatar
    Join Date
    Oct 2004
    Location
    Austin Texas
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doing this gives me what I want, but in an array instead of a named hash

    Code Ruby:
    @tweets = ActiveRecord::Base.connection.execute'SELECT * FROM tweets, users where tweets.twitter_id = users.twitter_id' 
            @tweets.each do |row|
              render :text => row[5] and return false
    end

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As no-one else seems to be picking this up, you'll have to manage with my feeble assistance I'm afraid.

    I've had a play and I think you are right. find_by_sql should be dynamically creating the attributes based on the field names returned by the SQL statement rather than just the methods defined by the model.

    As a guess, I wonder whether its the duplication of id fields that is causing the problem. It might be worth adding a :select argument to the find_by_sql to define and limit the fields being returned by the find_by_sql statement.

    So something like:
    Code:
    @tweets = Tweet.find(:all, 
          :joins => 'LEFT JOIN users ON users.twitter_id = tweets.twitter_id',
          :select => 'users.id, 
                         users.name, 
                         users.twitter_name, 
                         users.twitter_image,
                         tweets.text,
                         tweets.date')
    Also, if you end up going for a custom SQL solution, you can simplify your code by making either a user or tweets model method to do the work. As all standard models inherit their properties from Base, they already have their own connection method available. So you can do:
    Code:
    def self.array_of_user_hashes
      sql = <<EOF
    SELECT *
    FROM users
    EOF
      connection.select_all(sql)
    end
    This will return an array of hashes. Adding the self to the method name makes this a class method rather than an instance method. Have a look at the RoR api for more information on connection and select_all.


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
  •