On Sep 16, 2009, at 12:53 PM, 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)
>
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
