activerecord query building question

Discussion in 'Ruby' started by Aldric Giacomoni, Mar 30, 2009.

  1. 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
    Aldric Giacomoni, Mar 30, 2009
    #1
    1. Advertising

  2. Aldric Giacomoni

    Phrogz Guest

    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( :eek: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/
    Phrogz, Mar 30, 2009
    #2
    1. Advertising

  3. Aldric Giacomoni

    Mark Thomas Guest

    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
    Mark Thomas, Mar 31, 2009
    #3
  4. 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.
    Aldric Giacomoni, Mar 31, 2009
    #4
  5. Aldric Giacomoni

    Mark Thomas Guest

    >
    > > 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.
    Mark Thomas, Mar 31, 2009
    #5
  6. 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 Giacomoni, Mar 31, 2009
    #6
  7. 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 :)
    Aldric Giacomoni, Mar 31, 2009
    #7
  8. Aldric Giacomoni

    Mark Thomas Guest


    > 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
    Mark Thomas, Mar 31, 2009
    #8
  9. 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!
    Aldric Giacomoni, Mar 31, 2009
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Midex
    Replies:
    24
    Views:
    915
  2. Raif S. Naffah

    ActiveRecord (1.1) question

    Raif S. Naffah, Nov 26, 2004, in forum: Ruby
    Replies:
    5
    Views:
    115
    Raif S. Naffah
    Nov 30, 2004
  3. Dave Burt
    Replies:
    3
    Views:
    134
    James Britt
    Feb 12, 2005
  4. Replies:
    5
    Views:
    225
    ara.t.howard
    Nov 6, 2007
  5. Alpha Blue
    Replies:
    3
    Views:
    403
    Alpha Blue
    Jul 28, 2009
Loading...

Share This Page