find_by_sql - multiple selects, same statement SQL error

rubyonrails-talk
1

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

2

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.

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

3

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,

4

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,