activerecord query building question

[Aldric Giacomoni]

I have this:

require 'activerecord'

class RefPhys < ActiveRecord::Base
set_table_name "tbRefPhys"
has_many :refphyslocations, :foreign_key => "refphysid"
end

class RefPhysLocation < ActiveRecord::Base
set_table_name "tbRefPhysLocations"
belongs_to :refphys, :foreign_key => "refphysid"
end

I want a list of the refphys names (in refphys) in a specific city (in
refphyslocation).
So...
RefPhys.findall, :joins => ['refphyslocation'] .... ?

I don't grok AR yet :/
Thanks,
-Aldric

 

[Phrogz]

I have this:

require 'activerecord'

class RefPhys < ActiveRecord::Base
    set_table_name "tbRefPhys"
    has_many :refphyslocations, :foreign_key => "refphysid"
end

class RefPhysLocation < ActiveRecord::Base
    set_table_name "tbRefPhysLocations"
    belongs_to :refphys, :foreign_key => "refphysid"
end

I want a list of the refphys names (in refphys) in a specific city (in
refphyslocation).

For comparison with ActiveRecord (which I've all but forgotten now)
here's how you could do this in the Sequel[1] library:

RefPhys.filter( :refphysid => refphyslocation.id )

The cool thing about Sequel is that until you ask it to go and fetch
the records, you have a Dataset object that you can keep filter. For
example:

correct_location = RefPhys.filter( :refphysid =>
refphyslocation.id )

# Further pare down the Dataset
my_phys = correct_location.filter( wner_id => me.id )

# Filter to match multiple criteria, one of which is an array of
items
my_open = my_phys.filter( :status => Status::OPEN_IDS, :hidden =>
false )

my_open.each do |refphys|
# Now you've run the single query and are running through the
results
end

[1] http://sequel.rubyforge.org/

 

[Mark Thomas]

I have this:

require 'activerecord'

class RefPhys < ActiveRecord::Base
    set_table_name "tbRefPhys"
    has_many :refphyslocations, :foreign_key => "refphysid"
end

class RefPhysLocation < ActiveRecord::Base
    set_table_name "tbRefPhysLocations"
    belongs_to :refphys, :foreign_key => "refphysid"
end

I want a list of the refphys names (in refphys) in a specific city (in
refphyslocation).
So...
RefPhys.findall, :joins => ['refphyslocation'] .... ?

I don't grok AR yet :/

It would be something like this:

RefPhysLocation.find_all_by_state("OH").each do |location|
puts location.refphys.name
end

 

[Aldric Giacomoni]

I have this:

require 'activerecord'

class RefPhys < ActiveRecord::Base
set_table_name "tbRefPhys"
has_many :refphyslocations, :foreign_key => "refphysid"
end

class RefPhysLocation < ActiveRecord::Base
set_table_name "tbRefPhysLocations"
belongs_to :refphys, :foreign_key => "refphysid"
end

I want a list of the refphys names (in refphys) in a specific city (in
refphyslocation).
So...
RefPhys.findall, :joins => ['refphyslocation'] .... ?

I don't grok AR yet :/

It would be something like this:

RefPhysLocation.find_all_by_state("OH").each do |location|
puts location.refphys.name
end

That would probably work really well in a Rails app, but I get a
nomethoderror when I try that.. Maybe there's something else I need to
do before those methods will work.

 

[Mark Thomas]

That would probably work really well in a Rails app, but I get a
nomethoderror when I try that.. Maybe there's something else I need to
do before those methods will work

Yes, you have to change the methods to match your database. You did
not provide a schema, so I just used examples. find_all_by_state won't
work if you don't have a state field. You'll have to change
find_all_by_state to find_all_by_zip or whatever find criteria you
will use to determine the area. You'll also have to change
refphys.name to actual attribute(s) of a RefPhys.

 

[Aldric Giacomoni]

Yes, you have to change the methods to match your database. You did
not provide a schema, so I just used examples. find_all_by_state won't
work if you don't have a state field. You'll have to change
find_all_by_state to find_all_by_zip or whatever find criteria you
will use to determine the area. You'll also have to change
refphys.name to actual attribute(s) of a RefPhys.

Right- I tried the following, which works for my schema (by which I
mean, there is a 'City' column in the refphyslocation table):

RefPhysLocation.find_all_by_City("Mineola").each do |location|
puts location.refphys.fullname
end


and.. the first bit works (I'd forgotten to capitalize 'city' before)..
But I now get 'uninitialized constant RefPhysLocation::Refphys' . I
guess there's something I didn't set up properly :/ Do you have any idea
what? (I know, I should rtfm and google it.. I don't have time right
now, because our mail server at work almost blew up).
Thanks for getting me this far at least

 

[Aldric Giacomoni]

Refphys.findall, :include => :rephyslocation, :conditions =>
"refphyslocation.city = ?", "cityname"])

That's closer to what I need. Thanks for the help though, I learned
something new from what you gave me

 

[Mark Thomas]

Right- I tried the following, which works for my schema (by which I
mean, there is a 'City' column in the refphyslocation table):

RefPhysLocation.find_all_by_City("Mineola").each do |location|
   puts location.refphys.fullname
end

and.. the first bit works (I'd forgotten to capitalize 'city' before)..
But I now get 'uninitialized constant RefPhysLocation::Refphys' . I
guess there's something I didn't set up properly :/ Do you have any idea
what?

You probably have an capitalization where it should be lowercase
somewhere.

 

[Aldric Giacomoni]

You probably have an capitalization where it should be lowercase
somewhere.

I got a friend to look at it - you're right, that's it. it considered
some things class names instead of methods because of how I was writing
it.. Thanks!