Querying equivalent Mysql queries
I am new to rails. What is the equivalent rails query for the following SQL query?
mysql> select a.code, b.name, b.price, b.page from article a inner join books b on a.id =b.article_id;
I'm trying to
Article.joins(:books).select("books.name, books.price, books.page, articles.code")
Active record relationship only returns table 1 data
=> #<ActiveRecord::Relation [#<Article id: 1, code: "x", created_at: "2014-11-12 13:28:08", updated_at: "2014-11-14 04:16:06">, #<Article id: 2, code: "y", created_at: "2014-11-12 13:28:08", updated_at: "2014-11-14 04:00:16">]>
What is the solution to joining both tables?
source to share
You usually don't ask for this directly with Rails. Instead, you created your models and used other related models to achieve this. If speed is an issue, you can use eager download. If you absolutely need this connection, it is:
class Article < ActiveRecord::Base
has_many :books
scope :with_books, lambda {
joins(:books)
.select('articles.code, books.name, books.price, books.page')
}
end
class Book < ActiveRecord::Base
belongs_to :article
end
But this is not that useful. It generates the connection you want, but getting information about a book like that won't be fun. You can do something like:
a = Article.with_books.where("books.name = 'Woken Furies'").first
a.code
And that should give you the article code. Depending on what you need, the best way might be to remove the scope from the class Article
and instead query as:
b = Book.where(name: 'Woken Furies')
.joins(:article)
.where("articles.code = 'something'")
b = Book.where("name = 'Woken Furies' AND articles.code = 'something'")
.joins(:article)
Both of these queries must be equivalent. You can navigate from one related post to another:
book = b.first article_code = book.article.code
I'm not sure what you need to do with the join, but I think you can get prettier code using a simple ActiveRecord. If you need speed boost, avoid n + 1 problems, etc., it might make sense to write these connections by hand.
I hope I understood your question correctly.
More on joining in Rails guides:
http://guides.rubyonrails.org/active_record_querying.html#joining-tables
Update: . You can use pluck
if you need to extract eg. just code and name:
Article.with_books
.where("books.name = 'Woken Furies'")
.pluck('articles.code, books.name')
source to share