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

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

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

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:

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

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

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