In a simplified manner, I have two tables (Jobs and Technicians) that
I have mapped together with Jobs belonging to Technicians.
Technicians
------------------
id
name
Jobs
------------------
id
technician_id
stage
data
I want to have a list of Technician ids and names that have a job
with a stage of "D". I don''t want duplicate Technician ids
for each
Job though. (Technician may have multiple jobs in the same stage)
I have the following code which only does half of what I need it to.
(The other half would require yet again traversing throughout the
array and retrieving all the names for each active Technician.)
<controller-code>
## Active Jobs List
@techs_with_active_jobs = Job.find_by_sql "SELECT
technician_id FROM jobs WHERE stage = ''D'' ORDER BY
technician_id"
@techs = Array.new
@techs_with_active_jobs.each do |job|
@techs << job.technician_id
end
@techs_with_active_jobs = @techs
@techs = nil
@techs_with_active_jobs.uniq!
</controller-code>
The other problem I have is that Job.find_by_sql returns the Job
object instead of just the row I requested, so I have to traverse
through the array of objects to generate my array with ids only.
This gives me an array with all the active technician ids, but I
still would like to grab their names. I''m sure there must be an
easier way of doing this, it seems like this is the long way around
(especially copying the data from one array into another from the Job
object), but maybe it''s the only way.
I would appreciate any help to lead me in the right direction here.
Thanks,
Jason
On 28.9.2005, at 7.52, Jason Ketterman wrote:> In a simplified manner, I have two tables (Jobs and Technicians) > that I have mapped together with Jobs belonging to Technicians. > > Technicians > ------------------ > id > name > > Jobs > ------------------ > id > technician_id > stage > data > > I want to have a list of Technician ids and names that have a job > with a stage of "D". I don''t want duplicate Technician ids for each > Job though. (Technician may have multiple jobs in the same stage)class Technician < AR::Base def self.find_with_active_jobs find :all, :joins => "left join jobs j on technicians.id = jobs.technician_id", :conditions => ["j.stage = ?", "D"] end end There you go. When you want those people, just call Technician.find_with_active_jobs. //jarkko> > I have the following code which only does half of what I need it > to. (The other half would require yet again traversing throughout > the array and retrieving all the names for each active Technician.) > > <controller-code> > ## Active Jobs List > @techs_with_active_jobs = Job.find_by_sql "SELECT > technician_id FROM jobs WHERE stage = ''D'' ORDER BY technician_id" > @techs = Array.new > @techs_with_active_jobs.each do |job| > @techs << job.technician_id > end > @techs_with_active_jobs = @techs > @techs = nil > @techs_with_active_jobs.uniq! > </controller-code> > > The other problem I have is that Job.find_by_sql returns the Job > object instead of just the row I requested, so I have to traverse > through the array of objects to generate my array with ids only. > > This gives me an array with all the active technician ids, but I > still would like to grab their names. I''m sure there must be an > easier way of doing this, it seems like this is the long way around > (especially copying the data from one array into another from the > Job object), but maybe it''s the only way. > > I would appreciate any help to lead me in the right direction here. > > Thanks, > > Jason > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Here''s something untested and posted too late at night ;)
class Technician < ActiveRecord::Base
has_many :jobs
def self.with_active_jobs
self.jobs.find(:all, :conditions => "state =
''D''")
end
end
class Job < ActiveRecord::Base
belongs_to :technician
end
# In controller
@techs = Technician.with_active_jobs
@techs_by_ids = @techs.map { |tech| tech.id } if @techs
-Scott Reilly (coffee2code)
On 9/28/05, Jason Ketterman
<jason.ketterman-YHLYzh9cQ8BjrEqMGRc/AA@public.gmane.org>
wrote:> In a simplified manner, I have two tables (Jobs and Technicians) that
> I have mapped together with Jobs belonging to Technicians.
Thanks. I''ve never understood what SQL joins did... guess it''s time to go learn up on that. - Jason On Sep 28, 2005, at 1:33 AM, Jarkko Laine wrote:> > On 28.9.2005, at 7.52, Jason Ketterman wrote: > > >> In a simplified manner, I have two tables (Jobs and Technicians) >> that I have mapped together with Jobs belonging to Technicians. >> >> Technicians >> ------------------ >> id >> name >> >> Jobs >> ------------------ >> id >> technician_id >> stage >> data >> >> I want to have a list of Technician ids and names that have a job >> with a stage of "D". I don''t want duplicate Technician ids for >> each Job though. (Technician may have multiple jobs in the same >> stage) >> > > class Technician < AR::Base > > def self.find_with_active_jobs > find :all, :joins => "left join jobs j on technicians.id = > jobs.technician_id", > :conditions => ["j.stage = ?", "D"] > end > end > > There you go. When you want those people, just call > Technician.find_with_active_jobs. > > > //jarkko > > >> >> I have the following code which only does half of what I need it >> to. (The other half would require yet again traversing throughout >> the array and retrieving all the names for each active Technician.) >> >> <controller-code> >> ## Active Jobs List >> @techs_with_active_jobs = Job.find_by_sql "SELECT >> technician_id FROM jobs WHERE stage = ''D'' ORDER BY technician_id" >> @techs = Array.new >> @techs_with_active_jobs.each do |job| >> @techs << job.technician_id >> end >> @techs_with_active_jobs = @techs >> @techs = nil >> @techs_with_active_jobs.uniq! >> </controller-code> >> >> The other problem I have is that Job.find_by_sql returns the Job >> object instead of just the row I requested, so I have to traverse >> through the array of objects to generate my array with ids only. >> >> This gives me an array with all the active technician ids, but I >> still would like to grab their names. I''m sure there must be an >> easier way of doing this, it seems like this is the long way around >> (especially copying the data from one array into another from the >> Job object), but maybe it''s the only way. >> >> I would appreciate any help to lead me in the right direction here. >> >> Thanks, >> >> Jason >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> >> > > -- > Jarkko Laine > http://jlaine.net > http://odesign.fi > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 2005-09-28 02:33:02 -0400, Jarkko Laine <jarkko@jlaine.net> said:> > On 28.9.2005, at 7.52, Jason Ketterman wrote: > >> In a simplified manner, I have two tables (Jobs and Technicians) that >> I have mapped together with Jobs belonging to Technicians. >> >> Technicians >> ------------------ >> id >> name >> >> Jobs >> ------------------ >> id >> technician_id >> stage >> data >> >> I want to have a list of Technician ids and names that have a job with >> a stage of "D". I don''t want duplicate Technician ids for each Job >> though. (Technician may have multiple jobs in the same stage) > > class Technician < AR::Base > > def self.find_with_active_jobs > find :all, :joins => "left join jobs j on technicians.id = > jobs.technician_id", > :conditions => ["j.stage = ?", "D"] > end > endOr use a subquery: def self.find_with_active_jobs find :all, :conditions => "id IN (SELECT DISTINCT technician_id FROM jobs WHERE stage = ''D'')" end Also, watch out for NULL values in the jobs.technician_id column, if they are allowed. I prefer avoiding joins for such things, although both solutions work. Ben> > There you go. When you want those people, just call > Technician.find_with_active_jobs. > > > //jarkko > >> >> I have the following code which only does half of what I need it to. >> (The other half would require yet again traversing throughout the >> array and retrieving all the names for each active Technician.) >> >> <controller-code> >> ## Active Jobs List >> @techs_with_active_jobs = Job.find_by_sql "SELECT technician_id FROM >> jobs WHERE stage = ''D'' ORDER BY technician_id" >> @techs = Array.new >> @techs_with_active_jobs.each do |job| >> @techs << job.technician_id >> end >> @techs_with_active_jobs = @techs >> @techs = nil >> @techs_with_active_jobs.uniq! >> </controller-code> >> >> The other problem I have is that Job.find_by_sql returns the Job >> object instead of just the row I requested, so I have to traverse >> through the array of objects to generate my array with ids only. >> >> This gives me an array with all the active technician ids, but I still >> would like to grab their names. I''m sure there must be an easier way >> of doing this, it seems like this is the long way around (especially >> copying the data from one array into another from the Job object), but >> maybe it''s the only way. >> >> I would appreciate any help to lead me in the right direction here. >> >> Thanks, >> >> Jason >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails