Hi all, Think this is a really simple question, but so far cant find answers after much searching! I have 2 models, Event and Venue, an Event belongs_to a Venue. A venue has an attribute locality, which is a string I want to get an array of events, for which the venue they belong_to has the locality attribute equal to London. A list of events in London! That''s all! I can think of a horrible way to do this, where I return all events, go through the array, adding to a new array events where event.venue.locality == "london", but this seems daft, is there a way I can do this in a query, something like @ed_events = Event.where(Venue.locality => "London") I''ve found lots of help on more complicated joins between tables, but cant find the syntax for this! Thanks, Mike -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Tuesday, May 24, 2011 12:33:40 PM UTC-6, Ruby-Forum.com User wrote:> > Hi all, > > Think this is a really simple question, but so far cant find answers > after much searching! > > I have 2 models, Event and Venue, an Event belongs_to a Venue. > A venue has an attribute locality, which is a string > I want to get an array of events, for which the venue they belong_to has > the locality attribute equal to London. A list of events in London! > > That''s all! I can think of a horrible way to do this, where I return all > events, go through the array, adding to a new array events where > event.venue.locality == "london", but this seems daft, is there a way I > can do this in a query, something like > > @ed_events = Event.where(Venue.locality => "London") > > I''ve found lots of help on more complicated joins between tables, but > cant find the syntax for this! >Was this one of the resources your read (from the Rails Guides): http://guides.rubyonrails.org/active_record_querying.html#joining-tables Specifically read section 3.2, "Using Array/Hash of Named Association" You should be able to do something like: Event.join(:venue).where(:venue => { :locality => "London" }) Hope this helps. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Ah ok,
I had read this document, the reason I didn''t find what I was looking
for was that the associations in here were all opposite to my needs, eg
- one of the examples is
time_range = (Time.now.midnight - 1.day)..Time.now.midnight
Client.joins(:orders).where(:orders => {:created_at => time_range})
where a Client presumably has_many orders, and an Order belongs_to a
client.
I was looking for something like as you say:
Event.join(:venue).where(:venue => { :locality => "London" })
where an Event belongs_to a Venue, and a venue has many events. looking
that way round
Unfortunately, the exact line you give returns
undefined method `join'' for #<Class:0x104b2fd68>
trying ''joins'' instead returns:
SQLite3::SQLException: no such column: venue.locality: SELECT
"events".*
FROM "events" INNER JOIN "venues" ON
"venues"."id" = "events"."venue_id"
WHERE ("venue"."locality" = ''London'')
there definitely is a column in venue called locality!
I think I tried this as part of my searching, but kept getting errors
along these lines.
Is there an easy way to make the query, when an Event belongs_to a
Venue, and I''m searching using an attribute in the Venue model.
Thanks for your help
Mike
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
Kind of makes sense that it failed based on the SQL sentence it produces.
What about something like this:
Event.find_by_sql("select events.* FROM events INNER JOIN venues ON
venues.id = events.venue_id WHERE venue.locality =
''London''")
Let me know,
C
On Wed, May 25, 2011 at 5:49 AM, Michael Baldock
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:
> Ah ok,
>
> I had read this document, the reason I didn''t find what I was
looking
> for was that the associations in here were all opposite to my needs, eg
> - one of the examples is
>
> time_range = (Time.now.midnight - 1.day)..Time.now.midnight
> Client.joins(:orders).where(:orders => {:created_at => time_range})
>
> where a Client presumably has_many orders, and an Order belongs_to a
> client.
>
> I was looking for something like as you say:
>
> Event.join(:venue).where(:venue => { :locality => "London"
})
>
> where an Event belongs_to a Venue, and a venue has many events. looking
> that way round
>
> Unfortunately, the exact line you give returns
>
> undefined method `join'' for #<Class:0x104b2fd68>
>
> trying ''joins'' instead returns:
>
> SQLite3::SQLException: no such column: venue.locality: SELECT
"events".*
> FROM "events" INNER JOIN "venues" ON
"venues"."id" = "events"."venue_id"
> WHERE ("venue"."locality" = ''London'')
>
> there definitely is a column in venue called locality!
>
> I think I tried this as part of my searching, but kept getting errors
> along these lines.
>
> Is there an easy way to make the query, when an Event belongs_to a
> Venue, and I''m searching using an attribute in the Venue model.
>
> Thanks for your help
>
> Mike
>
> --
> Posted via http://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
If you look the SQL sentence it produces, kind of makes sense that it
failed... What about something like this:
Event.find_by_sql("select events.* from events INNER JOIN venues ON
venues.id = events.venue_id WHERE venue.locality =
''London''")
Let me know,
C
On Wed, May 25, 2011 at 5:49 AM, Michael Baldock
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:
> Ah ok,
>
> I had read this document, the reason I didn''t find what I was
looking
> for was that the associations in here were all opposite to my needs, eg
> - one of the examples is
>
> time_range = (Time.now.midnight - 1.day)..Time.now.midnight
> Client.joins(:orders).where(:orders => {:created_at => time_range})
>
> where a Client presumably has_many orders, and an Order belongs_to a
> client.
>
> I was looking for something like as you say:
>
> Event.join(:venue).where(:venue => { :locality => "London"
})
>
> where an Event belongs_to a Venue, and a venue has many events. looking
> that way round
>
> Unfortunately, the exact line you give returns
>
> undefined method `join'' for #<Class:0x104b2fd68>
>
> trying ''joins'' instead returns:
>
> SQLite3::SQLException: no such column: venue.locality: SELECT
"events".*
> FROM "events" INNER JOIN "venues" ON
"venues"."id" = "events"."venue_id"
> WHERE ("venue"."locality" = ''London'')
>
> there definitely is a column in venue called locality!
>
> I think I tried this as part of my searching, but kept getting errors
> along these lines.
>
> Is there an easy way to make the query, when an Event belongs_to a
> Venue, and I''m searching using an attribute in the Venue model.
>
> Thanks for your help
>
> Mike
>
> --
> Posted via http://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wednesday, May 25, 2011 11:22:24 AM UTC-6, CarlosCD wrote:> > Kind of makes sense that it failed based on the SQL sentence it produces. > What about something like this: >Yeah, it failed because I introduced two typos. My bad (that''s what I get for not double-checking before posting). Try the corrected: Event.joins(:venue).where(:venues => { :locality => "London" }) Notice joins has been fixed (as you figured out on your own) but also that the symbol :venue was pluralized to :venues. This should work (I even did a quick test app and verified it does for me).> > Event.find_by_sql("select events.* FROM events INNER JOIN venues ON > venues.id = events.venue_id WHERE venue.locality = ''London''") > > Let me know, > C > > On Wed, May 25, 2011 at 5:49 AM, Michael Baldock <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote: > >> Ah ok, >> >> I had read this document, the reason I didn''t find what I was looking >> for was that the associations in here were all opposite to my needs, eg >> - one of the examples is >> >> time_range = (Time.now.midnight - 1.day)..Time.now.midnight >> Client.joins(:orders).where(:orders => {:created_at => time_range}) >> >> where a Client presumably has_many orders, and an Order belongs_to a >> client. >> >> I was looking for something like as you say: >> >> Event.join(:venue).where(:venue => { :locality => "London" }) >> >> where an Event belongs_to a Venue, and a venue has many events. looking >> that way round >> >> Unfortunately, the exact line you give returns >> >> undefined method `join'' for #<Class:0x104b2fd68> >> >> trying ''joins'' instead returns: >> >> SQLite3::SQLException: no such column: venue.locality: SELECT "events".* >> FROM "events" INNER JOIN "venues" ON "venues"."id" = "events"."venue_id" >> WHERE ("venue"."locality" = ''London'') >> >> there definitely is a column in venue called locality! >> >> I think I tried this as part of my searching, but kept getting errors >> along these lines. >> >> Is there an easy way to make the query, when an Event belongs_to a >> Venue, and I''m searching using an attribute in the Venue model. >> >> Thanks for your help >> >> Mike >> >> -- >> Posted via http://www.ruby-forum.com/. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Ruby on Rails: Talk" group. >> To post to this group, send email to rubyonra...-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org >> To unsubscribe from this group, send email to >> rubyonrails-ta...-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org >> For more options, visit this group at >> http://groups.google.com/group/rubyonrails-talk?hl=en. >> >> >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wednesday, May 25, 2011 11:36:58 AM UTC-6, CarlosCD wrote:> > If you look the SQL sentence it produces, kind of makes sense that it > failed... What about something like this: >It only only makes sense in that the original version I posted has a typo (missing ''s'' in two places). When fixed, it works fine.> > Event.find_by_sql("select events.* from events INNER JOIN venues ON > venues.id = events.venue_id WHERE venue.locality = ''London''") > > Let me know, > C > > >SQL created the the code in my last post: SELECT "events".* FROM "events" INNER JOIN "venues" ON "venues"."id" = "events"."venue_id" WHERE "venues"."locality" = ''London'' This works. Just checked it again. There is no need for Event.find_by_sql (which is an ugly last resort). This is what i did to verify correctness: $ rails new example ... $ cd example $ bundle install ... $ rails g scaffold venue name:string locality:string ... $ rails g scaffold event venue:references name:string ... $ rake db:migrate ... $ vi app/models/venue.rb $ cat app/models/venue.rb class Venue < ActiveRecord::Base has_many :events end $ cat app/models/event.rb class Event < ActiveRecord::Base belongs_to :venue end $ rails c Loading development environment (Rails 3.0.7)> Venue.create :name => "The Globe Theatre", :locality => "London"=> <#<Venue id: 1, name: "The Globe Threatre", locality => "London", ...>> Event.create :name => "Hamlet", :venue => Venue.find(1)=> #<Event id: 1, venue_id: 1, name: "Hamlet", ...>> puts Event.joins(:venue).where(:venues => {:locality => "London"}).to_sqlSELECT "events".* FROM "events" INNER JOIN "venues" ON "venues"."id" = "events"."venue_id" WHERE "venues"."locality" = ''London'' => nil> x = Event.joins(:venue).where(:venues => {:locality => "London"})=> [#<Event id: 1, venue_id: 1, name: "Hamlet", ...>] -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Yes, in my case it was a typo too (a missing s on venues.locality =>
''London''). It should be:
Event.find_by_sql("SELECT events.* FROM events INNER JOIN venues ON
venues.id = events.venue_id WHERE venues.locality =
''London''")
I did test it before posting, but on different models, so my error was when
changing the entity names to "venues" and "events".
Both sentences (find_by_sql vs. joins.where) generate the same SQL statement
on the database side, which means that would be equivalent,
performance-wise. I posted the show the find_by_sql so you can see what it
does under the hood.
I would use one or the other based only on readability of your code. Choose
the easier to maintain (or add a comment with the SQL statement that
generates). Being something so simple, ot probably doesn''t matter much.
I
like a bit more the "joins.where", more ruby-style than just SQL, but
it is
your choice, they are fully equivalent.
C
On Wed, May 25, 2011 at 3:30 PM, Kendall Gifford
<zettabyte-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote:
> On Wednesday, May 25, 2011 11:22:24 AM UTC-6, CarlosCD wrote:
>>
>> Kind of makes sense that it failed based on the SQL sentence it
produces.
>> What about something like this:
>>
>
>
> Yeah, it failed because I introduced two typos. My bad (that''s
what I get
> for not double-checking before posting).
>
> Try the corrected:
>
> Event.joins(:venue).where(:venues => { :locality =>
"London" })
>
> Notice joins has been fixed (as you figured out on your own) but also that
> the symbol :venue was pluralized to :venues. This should work (I even did a
> quick test app and verified it does for me).
>
>
>
>>
>> Event.find_by_sql("select events.* FROM events INNER JOIN venues
ON
>> venues.id = events.venue_id WHERE venue.locality =
''London''")
>>
>> Let me know,
>> C
>>
>> On Wed, May 25, 2011 at 5:49 AM, Michael Baldock
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>wrote:
>>
>>> Ah ok,
>>>
>>> I had read this document, the reason I didn''t find what I
was looking
>>> for was that the associations in here were all opposite to my
needs, eg
>>> - one of the examples is
>>>
>>> time_range = (Time.now.midnight - 1.day)..Time.now.midnight
>>> Client.joins(:orders).where(:orders => {:created_at =>
time_range})
>>>
>>> where a Client presumably has_many orders, and an Order belongs_to
a
>>> client.
>>>
>>> I was looking for something like as you say:
>>>
>>> Event.join(:venue).where(:venue => { :locality =>
"London" })
>>>
>>> where an Event belongs_to a Venue, and a venue has many events.
looking
>>> that way round
>>>
>>> Unfortunately, the exact line you give returns
>>>
>>> undefined method `join'' for #<Class:0x104b2fd68>
>>>
>>> trying ''joins'' instead returns:
>>>
>>> SQLite3::SQLException: no such column: venue.locality: SELECT
"events".*
>>> FROM "events" INNER JOIN "venues" ON
"venues"."id" = "events"."venue_id"
>>> WHERE ("venue"."locality" =
''London'')
>>>
>>> there definitely is a column in venue called locality!
>>>
>>> I think I tried this as part of my searching, but kept getting
errors
>>> along these lines.
>>>
>>> Is there an easy way to make the query, when an Event belongs_to a
>>> Venue, and I''m searching using an attribute in the Venue
model.
>>>
>>> Thanks for your help
>>>
>>> Mike
>>>
>>> --
>>> Posted via http://www.ruby-forum.com/.
>>>
>>> --
>>> You received this message because you are subscribed to the Google
Groups
>>> "Ruby on Rails: Talk" group.
>>> To post to this group, send email to
rubyonra...-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
>>> To unsubscribe from this group, send email to
>>> rubyonrails-ta...-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
>>> For more options, visit this group at
>>> http://groups.google.com/group/rubyonrails-talk?hl=en.
>>>
>>>
>> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Wednesday, May 25, 2011 4:13:14 PM UTC-6, CarlosCD wrote:> > Yes, in my case it was a typo too (a missing s on venues.locality => > ''London''). It should be: > > Event.find_by_sql("SELECT events.* FROM events INNER JOIN venues ON > venues.id = events.venue_id WHERE venues.locality = ''London''") > > I did test it before posting, but on different models, so my error was when > changing the entity names to "venues" and "events". > > Both sentences (find_by_sql vs. joins.where) generate the same SQL > statement on the database side, which means that would be equivalent, > performance-wise. I posted the show the find_by_sql so you can see what it > does under the hood. >Well, yours doesn''t _generate_ SQL, it''s hard-coded.> > I would use one or the other based only on readability of your code. Choose > the easier to maintain (or add a comment with the SQL statement that > generates). Being something so simple, ot probably doesn''t matter much. I > like a bit more the "joins.where", more ruby-style than just SQL, but it is > your choice, they are fully equivalent. > >Yes, as always, choose the best tool for the job at hand. As for readability and maintainability, I (obviously) like mine better :). It''d take a far more complex SQL query than this one for me to begin to hard-code SQL fragments. In fact, with the meta_where (and the rails 3.1 replacement "squeel") gem, you can pretty much avoid even the smallest SQL fragment, even for moderately complex queries. it''s pretty cool stuff. It''s very rails-3-ish to avoid any SQL (and rely on the relational algebra implemented through arel). -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.