Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > activerecord query building question

Reply
Thread Tools

activerecord query building question

 
 
Aldric Giacomoni
Guest
Posts: n/a
 
      03-30-2009
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.find(:all, :joins => ['refphyslocation'] .... ?

I don't grok AR yet :/
Thanks,
-Aldric
 
Reply With Quote
 
 
 
 
Phrogz
Guest
Posts: n/a
 
      03-30-2009
On Mar 30, 12:30*pm, Aldric Giacomoni <"aldric[remove]"@trevoke.net>
wrote:
> 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/
 
Reply With Quote
 
 
 
 
Mark Thomas
Guest
Posts: n/a
 
      03-31-2009
On Mar 30, 2:30*pm, Aldric Giacomoni <"aldric[remove]"@trevoke.net>
wrote:
> 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.find(:all, :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
 
Reply With Quote
 
Aldric Giacomoni
Guest
Posts: n/a
 
      03-31-2009
Mark Thomas wrote:
> On Mar 30, 2:30 pm, Aldric Giacomoni <"aldric[remove]"@trevoke.net>
> wrote:
>
>> 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.find(:all, :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.
 
Reply With Quote
 
Mark Thomas
Guest
Posts: n/a
 
      03-31-2009
>
> > 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


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.
 
Reply With Quote
 
Aldric Giacomoni
Guest
Posts: n/a
 
      03-31-2009
Mark Thomas wrote:
>>> 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
>>

>
> 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
 
Reply With Quote
 
Aldric Giacomoni
Guest
Posts: n/a
 
      03-31-2009
Mark Thomas wrote:
>>> RefPhysLocation.find_all_by_state("OH").each do |location|
>>> puts location.refphys.name
>>> end
>>>

>>


Refphys.find(:all, :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
 
Reply With Quote
 
Mark Thomas
Guest
Posts: n/a
 
      03-31-2009

> 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.

> (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

 
Reply With Quote
 
Aldric Giacomoni
Guest
Posts: n/a
 
      03-31-2009
Mark Thomas wrote:
>> 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.
>
>
>> (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
>>

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!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need some help with Ruby query building and query time Alpha Blue Ruby 3 07-28-2009 06:52 PM
Long running SQL query via ActiveRecord blocking the entire Ruby process? estebanjang@gmail.com Ruby 5 11-06-2007 11:28 PM
Firefighters at the site of WTC7 "Move away the building is going to blow up, get back the building is going to blow up." Midex Python 24 05-07-2007 04:23 AM
Wireless building-to-building 101 Tim Jacob Wireless Networking 2 02-17-2006 09:46 AM
Building to Building wireless Patriot Cisco 2 11-04-2003 05:07 PM



Advertisments