15

After reading : this

I still don't get it. in console:

puts Category.joins(:posts)

It perform join or left join on Category and Post.

However, all data returned is just columns in Category table. How to also get those column data in Post. ?

should I make another model to achieve this ?

after reading: this Is find_by_SQL is the only way ? I want ActiveRecord way if possible.

Thanks.

Community
  • 1
  • 1
hey mike
  • 2,431
  • 6
  • 24
  • 29
  • You should be more specific about what you try to achieve. If you want it "the Active Record way", you shouldn't even talk about "columns" because you manipulate Objects, not tables. If you really need the raw data, you can use `ActiveRecord::Base.connection.select_all( 'SELECT...')`, this will return you an array of hashes (no model instanciation). [the documentation is here](http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-select_all) – m_x Feb 27 '12 at 11:18
  • thanks, I'm using this way now: ActiveRecord::Base.connection.select_all( Category.joins(:posts).to_sql )) , is this way being too nasty ? – hey mike Feb 27 '12 at 11:20
  • No problem. However, if you really want to work the ORM way, you should only use these kind of tricks for really complex SQL queries. For such a trivial problem, i'd use @abhishek's solution + `includes` instead. Why bother using an ORM if you don't code in an OO way ? – m_x Feb 27 '12 at 11:35

2 Answers2

8

You can try select() method:

Category.select("categories.*, posts.*").joins(:posts)
Ineu
  • 1,363
  • 9
  • 15
  • 1
    Thanks for reply. This can't work. It still only returning "categories.*" . Ever though the SQL generated is " SELECT categories.*, posts.* from ... – hey mike Feb 27 '12 at 10:28
  • How do you check if it's working? I've just tested it, works fine for me. – Ineu Feb 27 '12 at 10:53
  • I run this command in "rails console". Using puts and ap(awesome print): puts Category.select("categories.*, posts.*").joins(:posts) It just return # object. And only column of Category inside. – hey mike Feb 27 '12 at 10:59
  • 1
    this method works if you name each column in your `select` statement, and then if you invoke the column name as a method on the instantiated objects (these will be marked readonly, btw, [see the guides](http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields)). However, this method has always seemed a bit hackish to me : if you have columns with identical names in your two models, this will mess things up, save for using an alias. As i said, if you need the raw data, use `select_all` or even `select_row`. – m_x Feb 27 '12 at 11:29
  • agree, same named columns is a problem – Ineu Feb 27 '12 at 12:48
  • or `Category.select('*').joins(:posts)` – maxhungry Oct 30 '14 at 01:09
  • this query returns records where the ids are `Post` ids. In other words, `id` and `post_id` of the returned records are the same for me. It seems like `Category` ids were substituted with `Post` ids. Is it suppose to be like that? – molexi Dec 02 '20 at 16:12
4

You can get columns in posts tables by doing further querying, something like -

Category.joins(:posts).collect{|category| category.posts.map{|post| post.attributes.merge(category.attributes) } }

This will give you a giant list of post and category attributes merged together for each Category.

But the point of doing the join on Category is to get a set of Categories that have certain follow certain join criteria. If we take the next example in the same guide,

Post.joins(:category, :comments)

This also gives you a list of Posts only, but the list contains only the posts that follow the join constraint, which is, they all have a category and a comment.

abhishek
  • 998
  • 6
  • 9
  • 3
    beware, the `collect` will likely result in a N+1 query problem. To avoid this, use `includes`. see [the RoR guides on eager loading](http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations) – m_x Feb 27 '12 at 11:21
  • Doesn't Post.joins(:category, :comments) returns a post multiple times, if it has more than one category and/or more than one comment? – radiospiel Sep 27 '13 at 12:32