# Find\_by\_sql - multiple selects, same statement SQL error **URL:** \ **Category:** Rails\ **Created:** [September 16, 2009, 9:55pm UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-selects-same-statement-sql-error/175635 "2009-09-16T21:55:24Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Gee-off](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/35a633/32.png) [@Gee-off](https://www.ruby-forum.com/u/Gee-off)\ **Post date:** [September 16, 2009, 9:55pm UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-selects-same-statement-sql-error/175635/1 "2009-09-16T21:55:24Z")
I’m trying to get the following statement to work using variable substitution (it works if hard-code the where (med\_type) param in the inner SQL statement): med\_type = ‘game’ @mediaformats = MediaFormat.find\_by\_sql(“select \* from media\_formats where media\_formats.media\_type\_id = (select media\_type\_id from media\_types where media\_type = ?, :med\_type) order by description”) The error is generated by MySQL because the query it receives is: select \* from media\_formats where media\_formats.media\_type\_id = (select media\_type\_id from media\_types where media\_type = ?, :med\_type) which indicates I have a Rails syntax error in my code. Any help appreciated - I’m using find\_by\_sql only because I’m more comfortable with SQL than Rails queries. geoff ---
**Author:** ![Gee-off](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/35a633/32.png) [@Gee-off](https://www.ruby-forum.com/u/Gee-off)\ **Post date:** [September 16, 2009, 10:22pm UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-selects-same-statement-sql-error/175635/2 "2009-09-16T22:22:55Z")
On Sep 16, 2009, at 12:53 PM, Gee-off wrote: > The error is generated by MySQL because the query it receives is: > > select \* from media\_formats where media\_formats.media\_type\_id = > (select media\_type\_id from media\_types where media\_type > = ?, :med\_type) > > which indicates I have a Rails syntax error in my code. No error… you’re literally passing what you see on to MySQL and mysql is complaining. find\_by\_sql won’t do any replacement like normal find() queries do. If you’re going to do it that way you need to do “…where media\_type = ‘#{med\_type}’)…” and of course make sure you’re escaping med\_type appropriately so you don’t get a SQL injection. You might also look into putting this into your model so you can use construct\_finder\_sql which _can_ do those replacements… > Any help appreciated - I’m using find\_by\_sql only because I’m more > comfortable with SQL than Rails queries. If it were me, I’d take the time to figure out the Rails way. It will pay off in the long run. I’m guessing you have a MediaType model as well… media\_type\_ids = MediaType.all(:conditions =\> [‘media\_type = ?’, ‘game’]).map(&:id) @mediaformats = MediaFormat.all(:conditions =\> [‘media\_type\_id IN (?)’, media\_type\_ids], :order =\> ‘description’) Is one way. Or you can :include =\> :media\_type in your query and then set the condition directly (this would do a SQL join underneath). -philip ---
**Author:** ![Gee-off](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/35a633/32.png) [@Gee-off](https://www.ruby-forum.com/u/Gee-off)\ **Post date:** [September 17, 2009, 1:27am UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-selects-same-statement-sql-error/175635/3 "2009-09-17T01:27:01Z")
Gee-off wrote: > I’m trying to get the following statement to work using variable > substitution (it works if hard-code the where (med\_type) param in the > inner SQL statement): > > med\_type = ‘game’ > @mediaformats = MediaFormat.find\_by\_sql(“select \* from media\_formats > where media\_formats.media\_type\_id = (select media\_type\_id from > media\_types where media\_type = ?, :med\_type) order by description”) Well, you’re trying do string interpolation wrong, as Philip pointed out. But there’s no point using find\_by\_sql. Assuming you have your associations set up properly, all you need is @mediaformats = MediaType.find\_by\_name(med\_type).media\_formats . […] > Any help appreciated - I’m using find\_by\_sql only because I’m more > comfortable with SQL than Rails queries. That is a terrible reason to use find\_by\_sql. If you’re going to use Rails, take the time to actually learn to _use_ it. If not, do yourself a favor and remove it from your project. There is no point is using a framework if you’re just going to ignore it. > geoff ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![Gee-off](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/35a633/32.png) [@Gee-off](https://www.ruby-forum.com/u/Gee-off)\ **Post date:** [September 17, 2009, 1:36am UTC](https://www.ruby-forum.com/t/find-by-sql-multiple-selects-same-statement-sql-error/175635/4 "2009-09-17T01:36:17Z")
Gee-off wrote: > I’m trying to get the following statement to work using variable > substitution (it works if hard-code the where (med\_type) param in the > inner SQL statement): > > med\_type = ‘game’ > @mediaformats = MediaFormat.find\_by\_sql(“select \* from media\_formats > where media\_formats.media\_type\_id = (select media\_type\_id from > media\_types where media\_type = ?, :med\_type) order by description”) > > The error is generated by MySQL because the query it receives is: > > select \* from media\_formats where media\_formats.media\_type\_id = > (select media\_type\_id from media\_types where media\_type > = ?, :med\_type) One other thing. Even if you are using raw SQL, this is not a well-designed query. It could be done more readably with a join. Subqueries are great when they’re necessary, but they’re necessary far less often than you might expect. > which indicates I have a Rails syntax error in my code. No, it indicates that you don’t quite understand the difference between Ruby string interpolation and SQL query parameters. > Any help appreciated - I’m using find\_by\_sql only because I’m more > comfortable with SQL than Rails queries. Unfortunately, it looks like you’re comfortable with neither. You’ll really need to know both to use Rails profitably. > geoff ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid