Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

ActiveRecord :has_many with :finder_sql generates invalid query #415

Closed
martinsp opened this Issue May 6, 2011 · 9 comments

Comments

Projects
None yet
9 participants

martinsp commented May 6, 2011

To ilustrate issue, create 2 models:

Parent (id, name)
class Parent < ActiveRecord::Base
has_many :childs
has_many :finder_childs, :class_name => 'Child', :finder_sql => 'SELECT * from children WHERE parent_id=#{id}'
end

Child(id, name, parent_id)
class Child < ActiveRecord::Base
end

p = Parent.first
p.childs.all.map(&:id) # works ok
p.finder_childs.all.map(&:id) # raises sql exception because of invalid query

Last line generates exception in sqlite and mysql. Error for sqlite:
SQLite3::SQLException: only a single result allowed for a SELECT that is part of an expression: SELECT "children".* FROM "children" WHERE (SELECT * from children WHERE parent_id=1)

using p.finder_childs.map(&:id) works fine.

@ghost ghost assigned jonleighton May 6, 2011

Member

jonleighton commented May 6, 2011

What version of Rails is this on?

martinsp commented May 6, 2011

Rails 3.0.6

Contributor

jgaskins commented May 15, 2011

Is the lack of a belongs_to call intentional here?

Contributor

dmathieu commented Jul 6, 2011

Do you also reproduce it with 3.0 ? And 3.1rc ?
Have you tried @jgaskins suggestion to add the reverse belongs_to ?

Contributor

ksob commented Jul 7, 2011

Adding belongs_to won't help as the problem lies in the use of "all" method here:
p.finder_childs.all.map(&:id)

The "all" method in this context is defined as a wrapper for find(:all, *args) so the above line is interpreted as:
p.finder_childs.find(:all).map(&:id)

what results in double SELECT

The solution is to remove the method call:
p.finder_childs.map(&:id)

any simple workaround? this breaks also in various validation generated code.

thanks.

As a workaround, you can use a proc:

has_many :messages, :finder_sql => proc { "SELECT * FROM messages WHERE author_id = #{self.id} or recipient_id = #{self.id}" }

@jeremy jeremy closed this Oct 9, 2011

Contributor

rorra commented Oct 13, 2011

I was just having that issue. The rails doc says that #{id} could be used, but that brings the object id, the proc worked fine. Perhaps the doc should be updated to reflect that in order to create relations with custom finders, a Proc should be created.

  #   has_many :subscribers, :class_name => "Person", :finder_sql =>
  #       'SELECT DISTINCT people.* ' +
  #       'FROM people p, post_subscriptions ps ' +
  #       'WHERE ps.post_id = #{id} AND ps.person_id = p.id ' +
  #       'ORDER BY p.first_name'

sadly the proc doesnt work on rails 2.x :(

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment