return models using association sort

rubyonrails-talk
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

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.

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

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

6

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

7

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

8

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

9

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