Wednesday, January 5, 2011
Actually, i tried to make this query using NOT IN, i made something like this:
SELECT courses.*
FROM courses
INNER JOIN courses_times ON courses_times.course_id = courses.id
INNER JOIN times ON courses_times.time_id = times.id
where courses.id
NOT IN (
SELECT courses.id
FROM courses
INNER JOIN courses_times ON courses_times.course_id = courses.id
INNER JOIN times ON courses_times.time_id = times.id
AND times.day != 'Monday'
)
AND times.day = 'Monday'
But, its not very useful, im using MySql, and it wasted like 30 seconds to give me the result.. o.O
its because my tables have a lot of registers: coursers (8000), times (300), courses_times (1300)
Does anyone have another ideia for this query?
Fernando
2011/1/5 Frederick Cheung <frederick.cheung@gmail.com>
On Jan 5, 5:38 pm, Fernando Leandro <fernandoleandro1...@gmail.com>
wrote:
In sql terms, how about
> When i use a query like above, for example, it returns to me all the courses
> that have association with Monday,
> it returns Math, Chimestry and History, and i would like to receive just
> History
>
> I know that this question is not about rails but i need to solve this
> problem to continue my rails app,
> and i would appreciate if someone could help me..
>
> Does anyone have a solution for this?
select *, count(*) as times_scheduled from course_times
inner join course_times as other_times on other_times.course_id =
course_times.course_id
where course_times.time_id = 1
group by course_times.course_id
having times_scheduled = 1
Arel is supposed to be way better than old skool activerecord at
generating joins like this where you need to be careful about aliasing
table names and so on.
Fred
--
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 rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
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 rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment