Ruby on Rails - Query Multiple Models

Question

I created a ActiveRecord model that does not have a corresponding table in the database. I want it to manage the application 'scoped' queries:

class Meter <ActiveRecord::Base
  def self.daily_tests
    self.connection.execute(sanitize_sql(["SELECT b.*, m.*, i.* FROM bgtests AS b JOIN meals AS m ON b.user_id = m.user_id JOIN injections AS i ON i.user_id = m.user_id WHERE b.user_id = 2 AND m.user_id = 2 AND i.user_id = 2"]))
  end
end

The query executes however the result set brings back this:

{"id"=>1, "value"=>712, "category"=>"basal", "time_of_day"=>"Before dinner", "comments"=>"Est et aut. Est maxime sunt. Dolor doloribus distinctio sed reprehenderit culpa. Autem ipsam atque modi dolor ut non. Aut dicta voluptate occaecati.", "user_id"=>2, "created_at"=>"2015-06-21 18:58:55.806367", "updated_at"=>"2015-06-21 18:58:55.806367", "name"=>"snack", "carbohydrates"=>142, "description"=>"Facere reiciendis non officia velit consequatur voluptas eum. Veritatis quia cumque. Dolor non eaque quod. Dignissimos quae aut eveniet sunt ea amet. Iste et aut unde consequatur quia commodi.", "num_of_units_taken"=>1.0, 0=>10, 1=>712, 2=>"smbg", 3=>"Before dinner", 4=>"Est et aut. Est maxime sunt. Dolor doloribus distinctio sed reprehenderit culpa. Autem ipsam atque modi dolor ut non. Aut dicta voluptate occaecati.", 5=>2, 6=>"2015-06-21 18:58:55.595625", 7=>"2015-06-21 18:58:55.595625", 8=>12, 9=>"snack", 10=>142, 11=>"Facere reiciendis non officia velit consequatur voluptas eum. Veritatis quia cumque. Dolor non eaque quod. Dignissimos quae aut eveniet sunt ea amet. Iste et aut unde consequatur quia commodi.", 12=>2, 13=>"2015-06-21 18:58:56.115615", 14=>"2015-06-21 18:58:56.115615", 15=>1, 16=>1.0, 17=>"basal", 18=>2, 19=>"2015-06-21 18:58:55.806367", 20=>"2015-06-21 18:58:55.806367"},

The initial key-values are return as column-value however it then switches and adds subsequent records as 5=>2 for example.

My question is how can I query these multiple models on the foreign_key 'user_id" and have the result set bring back all the results in the typical object notation specific to the model?

Can you tag with what db are you using? Also, why do you want to query in this way? You can write this query in AR... — eirikir
– eirikir, Commented Aug 7, 2015 at 23:39
I'm not able to reproduce the issue with Rails 4.1 + PostgreSQL. Do you have columns with the same name on different tables? — eirikir
– eirikir, Commented Aug 7, 2015 at 23:40
I'd prefer to use AR. I do have a user_id column in each of the three tables. — porterhaus
– porterhaus, Commented Aug 8, 2015 at 13:37

eirikir · Accepted Answer · 2015-08-09 14:00:55Z

Most of the methods defined by the ActiveRecord query interface accept SQL string arguments in addition to other forms specified in the docs. You can also arbitrarily chain these methods, and AR will combine to create a proper SQL. When in doubt, use to_sql to view the SQL generated. Assuming bgtests corresponds to a model called Bgtest, you could for example do the following:

Bgtest.select("b.*, m.*, i.*").from("bgtests b")
.joins("JOIN meals AS m ON b.user_id = m.user_id")
.joins("JOIN injections AS i ON i.user_id = m.user_id")
.where("b.user_id = 2 AND m.user_id = 2 AND i.user_id = 2")

Method chaining works this way because each method returns an ActiveRecord::Relation object that accepts the same ActiveRecord::QueryMethods methods as ActiveRecord::Base (which your models subclass). Ordering of the method calls is therefore arbitrary. The SQL is not actually executed until you attempt to extract a value from the relation, whether due to the console calling to_s or because you call each in a view.

Note that the above can be greatly simplified (and much more "Rails-y") by defining the correct associations on the models. In particular, the classes Bgtest, Meal, and Injection would all apparently call belongs_to :user, and User would call has_many :bgtests, etc. You can then use has_many with :through option in other classes, e.g. Bgtest could call has_many :meals, through: user, etc. AR would then know to INNER JOIN based on referencing the association names alone:

Bgtest.joins(:meals, :injections).where(user_id: 2)

or LEFT OUTER JOIN:

Bgtest.includes(:meals, :injections).where(user_id: 2)

Collectives™ on Stack Overflow

Ruby on Rails - Query Multiple Models

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related