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.
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…
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
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.
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.