Is there a way to use raw SQL statement in Rails?

rubyonrails-talk
1

Hello, I have five tables A, B, C, D, and E, and this SQL statement:

SELECT A.column2, B.column5, C.column3, D.column7, D.name, E.name as role from A INNER JOIN B on A.foreignKey1 = B.id INNER JOIN C on A.foreignKey2 = C.id INNER JOIN D on C.id = D.foreignKey INNER JOIN E on D.id = E.foreignKey

I find it difficult to perform this query statement the Rails way or perhaps I didn’t do it correctly. I’m looking for a fall-back solution in case that I cannot achieve it the Rails way. Is there a way to run a raw SQL statement like this in a Ruby on Rails application, whether in the controller or model ? Thanks so much!

2

You can always use find_by_sq()l to run any query you can think up. However, the returned value is not a scope, like where() would give you, instead, it’s either a single record or an array of “found” records, because under the hood, it’s using the same mechanism as find(). Your entire long-hand query goes inside find_by_sql’s single text parameter. You can (and should) use bound parameters, substituting for the single string an array consisting of a string (the query) and N parameters, if you pass any external parameters into the query.

Walter

3

Thanks Walter! My experience is that find_by_sql() doesn’t return the “selected” columns from “each” table. Rather, it simply returns all columns (*) of the first table only; which is not what I want. Is there a way to get the selected columns from each table ? Many thanks!

4

It’s up to you to set that in your query.

If you use Foo.find_by_sql(‘select id from foos’), you’re going to get just the IDs back. Each one will be wrapped in an otherwise-empty Foo object, with a parameter set for each of the columns you put in your select clause. If you used select.*, then yes, you will get all the columns back.

It’s important to note that you could do Foo.find_by_sql(‘select id from bars’) and you would get an array of Foo objects, each containing only the id of all of your Bars. It’s literally running just whatever you place in the query string, warts and all, and returning it wrapped in whatever the parent model was.

If you wanted it to be completely agnostic, you could run it as ApplicationModel.find_by_sql(…). So it’s up to you to put whatever you want to select in your SQL query. To get these raw values back out, you’ll want to use pluck on the result. Here’s a ridiculous example:

2.6.6 :008 > Title.find_by_sql(‘select id, name from people’).pluck(:id, :name) Title Load (0.3ms) select id, name from people => [[1, “Barney Rubble”], [2, “Mr. Slate”], [3, “Fred Flintstone”]]

If you need to get same-name parameters from across your multi-join SQL, then just as if you were doing this long-hand in Sequel Pro, you would assign aliases to each of the attributes you wanted to find:

select titles.name as title, people.name as author from titles, people where titles.person_id = people.id

That’s going to return [title: “Rocks I Have Crushed”, author: “Fred Flintstone”] etc.

Walter

5

You should be able to do something like TableName.joins(…).select(…) without any issues. Specially because they’re all inner joins.

You can select fields from other tables using ‘table_name.field’

1 Like
6

Thanks Julian! I have tried but didn’t get what I expect. I think Rails has a limited capability in terms of performing queries compared to the traditional SQL statements, especially when it comes to dealing with table joins with selected columns from each table as I described in the above example. The online Ruby on Rails documentation doesn’t seem to cover this part thoroughly. It seems that the find_by_sql method can only handle simple SQL queries. I could be wrong but, so far, I haven’t seen find_by_sql(query) return the same result, or close enough, as what I would have received if I had run the query directly against a database. But, I appreciate your attention to my concern. Thanks!

7

I am big fan of Rails and working with it long time, what I normally use raw SQL in Rails projects is reporting side to make it more efficient.

result = ActiveRecord::Base.connection.execute "select users.id as user_id, user_roles.id as user_roles_id from users inner join user_roles on users.id = user_roles.user_id"

Above code is one example to solve your case, and it returns

#<PG::Result:0x007fc5429cfb30 status=PGRES_TUPLES_OK ntuples=2322 nfields=2 cmd_tuples=2322>
result[0]
=> {"user_id"=>85, "user_roles_id"=>111}

Please take a look and let me know if you have any issues.

8

Thank you, the solution ActiveRecord::Base.connection.execute “sql_statement” works. Thanks again!