Rails - Sep 2009 - find_by_sql - multiple selects, same statement SQL error

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

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

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.