[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

activerecord query building question

aldric[removeme]

3/30/2009 6:31:00 PM

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
8 Answers

Phrogz

3/30/2009 8:21:00 PM

0

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( :owner_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.ruby...

Mark Thomas

3/31/2009 2:18:00 AM

0

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

aldric[removeme]

3/31/2009 12:17:00 PM

0

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.

Mark Thomas

3/31/2009 2:10:00 PM

0

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

aldric[removeme]

3/31/2009 2:48:00 PM

0

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 :)

aldric[removeme]

3/31/2009 6:39:00 PM

0

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 :)

Mark Thomas

3/31/2009 7:32:00 PM

0


> 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

aldric[removeme]

3/31/2009 8:02:00 PM

0

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!