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 <[email protected]>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 <[email protected]>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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
