CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Kavi
Kavi

Reputation: 174

Model.find_by_sql vs connection.query, which one is better?

In rails-

I need to execute a sql query against the database; the query is not related to any specific Model it can have mix data from multiple tables or from some other table. I have ways to do this=-

Problem with this approach, I am not feeling comfortable with it because in User class objects I am capturing data of other table. like the first query result has data from customer table so in the user object I got customer's attribute. And more interesting problem is- if the resulting query has id attribute then

res.first.id will be id of customer and if User model has relation with UserRoles model and if I access this relation with res.first.roles then it will fetch roles from UserRole for customer id, which is completely wrong.

And there might be may problems also.

So I think it has lot of chaos.

And good part is we dont not need to deal with connection and result would be an array of objects. so accessing object attributes with res.first.id is easier the hast like row["id"].

this res = ActiveRecord::Base.connection.query("sql query") in place of query we can use select_one, select_all and can also make query parameterized.

The problem it has is it returns array of hash, but I need array of objects for easy accessibility in code. So I wrote a class to convert hash to object (I think rail does same thing in background) and is working fine.

So I need some suggestion on both the approach and need to decide which one is better.

Upvotes: 2

Views: 1389

Answers (1)

Inx Maurya
Inx Maurya

Reputation: 1155

First find_by_sql vs select_all

find_by_sql, This method returns an array of objects by initiating them.

users = User.find_by_sql("SELECT * FROM users"); #=>  [#, #, #, # ....]


Accessing properties
users[0].name   #Getting property in object oriented fashion

select_all, This method returns an array of objects but does not initiate them, and each object represents a row of database.

users = User.connection.select_all("SELECT * FROM users"); #=>  #

Accessing properties
users[0]["name"] #Getting property in non-object oriented fashion

Whether find_by_sql is better, because of it is a simple way of custom querying to the database and returns instantiated objects

Upvotes: 3

Related Questions