Return models using association sort

Rails
1

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

2

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

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.

3

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

4

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

5

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

6

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

7

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

8

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

9

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