Not getting the associated columns when using :join in find

[ikeo]

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

@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

@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?

[ReggieB]

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:

has_many :tweets

And in the Tweets model you'd add:

belongs_to :user

Once that is in place, you'll be able to do this:

#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

[ikeo]

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?

[ikeo]

Doing this gives me what I want, but in an array instead of a named hash

@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

[ReggieB]

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:

@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:

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.