Rails - Aug 2005 - Trying to rows with zero matches in related table...

Hey, gang. I''m brand new to Rails (although not new at all to web 
applications ;) and I think it''s an awesome environment. However, some 
of the object-relational stuff, and some of the straight SQL, still 
throws me.

Here''s my situation, I''ve got a semi-involved set of table
relations:
Districts contain both Applicants and Interviewers (standard has_many 
relation), Applicants and Interviewers are a HABTM (I can show those 
definitions if they''re important). What I need is a way to select all 
the applicants for a particular district that have no interviewers. 
Here''s what I''ve come up with for the District model:

class District < ActiveRecord::Base
	has_many 	:interviewers
	has_many 	:applicants
	has_many 	:unassigned_applicants,
				:class_name => "Applicant",
				:finder_sql => "select applicants.* from applicants " +
				"where (select count(*) from applicants_interviewers " +
				"where applicants.id = applicants_interviewers.applicant_id) = 0"
end

But it''s throwing a Syntax Error that I don''t understand:

SyntaxError in District#unassigned_list

(eval):1:in `instance_eval'': compile error
(eval):1: syntax error
%(SELECT COUNT(*) FROM applicants_interviewers where applicants.id = 
applicants_interviewers.applicant_id) > 0)

Hopefully, I''m missing something butt-simple. Any ideas? Am I even
doing
this the correct way?

Thanks, in advance.

-Brian

> (eval):1:in `instance_eval'': compile error
> (eval):1: syntax error
> %(SELECT COUNT(*) FROM applicants_interviewers where applicants.id = 
> applicants_interviewers.applicant_id) > 0)

Didn''t delve into the Rails code, but your SQL has a problem.  Try 
getting your sql to work outside Rails first.

Specifically:

SELECT COUNT(*) FROM applicants_interviewers where applicants.id = 
applicants_interviewers.applicant_id)
having count(*) > 0

look in a sql book under "having"

I''m also pretty sure that I read somewhere:

http://one.textdrive.com/pipermail/rails/2005-February/002751.html

that finder_sql can''t take dbl quotes (or can, but something fish
happens).
it needs single quotes around the query... maybe?



On 8/4/05, Steve Downey <sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org>
wrote:
> 
> 
> > (eval):1:in `instance_eval'': compile error
> > (eval):1: syntax error
> > %(SELECT COUNT(*) FROM applicants_interviewers where
applicants.id<http://applicants.id>> >
applicants_interviewers.applicant_id) > 0)
> 
> 
> Didn''t delve into the Rails code, but your SQL has a problem. Try
> getting your sql to work outside Rails first.
> 
> Specifically:
> 
> SELECT COUNT(*) FROM applicants_interviewers where
applicants.id<http://applicants.id>>
applicants_interviewers.applicant_id)
> having count(*) > 0
> 
> look in a sql book under "having"
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails