Return models using association sort

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

On Tuesday, May 24, 2011 12:33:40 PM UTC-6, Ruby-Forum.com User wrote:

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.

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

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

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

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 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_sql
SELECT “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”, …>]

On Wednesday, May 25, 2011 4:13:14 PM UTC-6, CarlosCD wrote:

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs