CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3activerecord
victor
victor

Reputation: 29

find_by_sql Ruby on Rails 3 no results

I have a very strange issue when trying to use find_by_sql with Rails 3.0.7. Rails doesn't return anything (empty result, []) but if I copy & paste that exact same query into mysql, it returns a result.

This is what I'm trying in Rails:

Document.find_by_sql(["select d.* from documents d, categorizations cg, combinations co where d.id = cg.document_id and co.id = cg.combination_id and co.assigned_parent_category_id=?", 1)

returns: []

and this is what I do in mysql:

select documents.*
from documents, categorizations, combinations
where documents.id = categorizations.document_id
    and combinations.id = categorizations.combination_id
    and combinations.assigned_parent_category_id=1

returns: 1 result

Could this be a Rails bug or am I doing something wrong? Thanks !

Upvotes: 0

Views: 1730

Answers (2)

Salil
Salil

Reputation: 47472

Change

Document.find_by_sql(["select d.* from documents d, categorizations cg,
            combinations co where d.id = cg.document_id and co.id = cg.combination_id
            and co.assigned_parent_category_id=?", 1)

To

Document.find_by_sql(["select d.* from documents d, categorizations cg,
            combinations co where d.id = cg.document_id and co.id = cg.combination_id
            and co.assigned_parent_category_id=?", 1])

OR

Document.find_by_sql("select d.* from documents d, categorizations cg,
            combinations co where d.id = cg.document_id and co.id = cg.combination_id
            and co.assigned_parent_category_id=1")

Upvotes: 2

mylescarrick
mylescarrick

Reputation: 1680

Is there a reason you're using find_by_sql?

The docs suggest that supplying an array (your syntax looks wrong anyway - where's the closing square bracket?) isn't an option - you have to supply the sql

# File activerecord/lib/active_record/base.rb, line 472
def find_by_sql(sql)
  connection.select_all(sanitize_sql(sql), "#{name} Load").collect! { |record| instantiate(record) }
end

EDIT: looks like I'm wrong - sanitize_sql will take a string, array or hash. Still...

My tip: try looking to do this without find_by_sql()

Upvotes: 0

Related Questions