Scope joins with distinct only work through association and not on base model

[niedfelj]

I have a Movie model with showtimes and theaters that looks like this:

class Movie < ActiveRecord::Base
has_many :showtimes
has_many :theaters, :through => :showtimes, :uniq=>true

scope :with_showtimes_today, lambda {where('showtimes.showtime' => Time.now.midnight..(Time.now.midnight+26.hours))}
end

If I do something like this, the SQL correctly generates the join statement:

t=Theater.find(7114)
t.movies.with_showtimes_today

SELECT DISTINCT movies.* FROM movies INNER JOIN showtimes ON movies.id = showtimes.movie_id WHERE ((showtimes.theater_id = 7114)) AND (showtimes.showtime BETWEEN '2011-05-11 04:00:00' AND '2011-05-12 06:00:00')

I believe that works because the Theater model has movies through showtimes like this:

class Theater < ActiveRecord::Base
has_many :showtimes
has_many :movies, :through=>:showtimes, :uniq=>true
end

However, if I just call

Movie.with_showtimes_today

I get this error:

SELECT movies. FROM movies WHERE (showtimes.showtime BETWEEN '2011-05-11 04:00:00' AND '2011-05-12 06:00:00')
Mysql2::Error: Unknown column 'showtimes.showtime' in 'where clause': SELECT movies. FROM movies WHERE (showtimes.showtime BETWEEN '2011-05-11 04:00:00' AND '2011-05-12 06:00:00')

Because it's not doing the join. So if I change the scope to include a joins:

scope :with_showtimes_today, lambda {joins(:showtimes).where('showtimes.showtime' => Time.now.midnight..(Time.now.midnight+26.hours))}

Movie.with_showtimes_today works correctly and generates this SQL:

SELECT movies.* FROM movies INNER JOIN showtimes ON showtimes.movie_id = movies.id WHERE (showtimes.showtime BETWEEN '2011-05-11 04:00:00' AND '2011-05-12 06:00:00')

But then the association through Theaters generates this insanely long-running query with 2 INNER JOINS:

SELECT DISTINCT movies.* FROM movies INNER JOIN showtimes showtimes_movies ON showtimes_movies.movie_id = movies.id INNER JOIN showtimes ON movies.id = showtimes.movie_id WHERE ((showtimes.theater_id = 7114)) AND (showtimes.showtime BETWEEN '2011-05-11 04:00:00' AND '2011-05-12 06:00:00')

Even if it's not a bug, it's definitely in need of some improvements along with scope documentation/guides being completely lacking (for example, it was not apparent in reading any of the documentation that I had to wrap my Time.now statements in lambda's or they would be set at class load)

[jonkessler]

This seems like a tech support request more than an issue. I'd suggest this be closed.

[rafaelfranca]

@jwkpiano1 correct. I'm closing this now. Thank you so much.