So, now, i want to make a query that returns to me only the courses that are ministred on Monday,
but just ONLY Monday. For example, if a course is ministred Monday and Wednesday i dont want.
In this example, i would like that my query returns just History, because is the only course
that is just on Monday.
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
AND times.day = ‘Monday’
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…
You can make it a Rails question by re-formulating the question in
terms of your ActiveRecord relationships and asking how to code a find
operation to return the objects you want. In practice this should be
your initial approach anyway, only resorting to SQL if you cannot code
it using Rails helpers.
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..
You can make it a Rails question by re-formulating the question in
terms of your ActiveRecord relationships and asking how to code a find
operation to return the objects you want. In practice this should be
your initial approach anyway, only resorting to SQL if you cannot code
it using Rails helpers.
Agreed -- mostly. I'm very comfortable with SQL, so I often find it
helpful to consider a query in SQL terms first, and then figure out how
to abstract that SQL with ActiveRecord. That usually leads to better
queries anyway -- ActiveRecord can be ridiculously inefficient for
certain operations.
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…
You can make it a Rails question by re-formulating the question in
terms of your ActiveRecord relationships and asking how to code a find
operation to return the objects you want. In practice this should be
your initial approach anyway, only resorting to SQL if you cannot code
it using Rails helpers.
Agreed – mostly. I’m very comfortable with SQL, so I often find it
helpful to consider a query in SQL terms first, and then figure out how
to abstract that SQL with ActiveRecord. That usually leads to better
queries anyway – ActiveRecord can be ridiculously inefficient for
certain operations.
Could you name some of these certain operations where its better to go for SQL queries instead of ActiveRecord.
Also, is ARel also inefficient for the certain operations that you are mentioning??
Until now, I have learned that you should not write SQl queries in your code, rather go for Active Record, as it allows you to change your choice of database later on. But, If you are certain that you are going to use MySQL or a specific DB for an app then I guess there is no problem in going for SQL queries.
Could you name some of these certain operations where its better to go for
SQL queries instead of ActiveRecord.
I didn't mean to use raw SQL *instead of* ActiveRecord. But sometimes
ActiveRecord's abstractions aren't terribly good (notably with bulk and
aggregate operations, as well as certain types of joins), and
find_by_sql becomes necessary.
Also, is ARel also inefficient for the certain operations that you are
mentioning??
I haven't used Arel yet, so I'm not sure.
Until now, I have learned that you should not write SQl queries in your
code, rather go for Active Record, as it allows you to change your
choice of
database later on.
That is true.
But, If you are certain that you are going to use
MySQL
or a specific DB for an app then I guess there is no problem in going
for
SQL queries.
Not quite. If I need literal SQL (which is rare), my practice is to
write it as far as possible in terms compliant with standard ANSI SQL
syntax, without proprietary extensions. This gives the best portability
across databases.
Could you name some of these certain operations where its better to go for
SQL queries instead of ActiveRecord.
I didn’t mean to use raw SQL instead of ActiveRecord. But sometimes
ActiveRecord’s abstractions aren’t terribly good
(notably with bulk and
aggregate operations, as well as certain types of joins),
Could you elaborate a little bit on these operations and joins, name a few and tell why ActiveRecord isn’t good with them. I am pretty low on my learning curve in this domain, so your experience would really help.
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.
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](http://courses.id)
FROM courses
INNER JOIN courses_times ON courses_times.course_id = [courses.id](http://courses.id/)
INNER JOIN times ON courses_times.time_id = [times.id](http://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)
I didn't mean to use raw SQL *instead of* ActiveRecord. But sometimes
ActiveRecord's abstractions aren't terribly good
(notably with bulk and
aggregate operations, as well as certain types of joins),
Could you elaborate a little bit on these operations and joins, name a
few
and tell why ActiveRecord isn't good with them. I am pretty low on my
learning curve in this domain, so your experience would really help.
Well, for aggregate operations, AR just doesn't provide much abstraction
at all. Yes, User.count and User.max :age are helpful, but more complex
aggregate operations (for example, on associated tables) have to be done
in SQL, or in the application layer. Many Rails developers would opt
for the latter, but IMHO that's inefficient and silly, since the DB can
do these operations for more efficiently.
Perhaps Arel has changed this. I gather that this is probably a goal,
but I don't know if it's been achieved.
Bulk updates are another area where AR is really inefficient, since
update_all is pretty anemic. ar-extensions can help here, though.
I think your subquery may be too complex. Don't you just want to
select ids from Times where the day isn't 'Monday' and use that set as
the filter on the top-level query?
Check out Fred's earlier example. Using the count of courses with only
one courses_times row in conjunction with the having clause you can
filter out courses scheduled on more than one day.
The only potential problem I could foresee with this would be if a
course could be scheduled for multiple times on the same day. But
that's not reflected in the data model you posted so it shouldn't be
an issue.
hum… but actually in my database it occurs… that are some coursers that have two times that reffers to the same day (because actually in my db, time has the hour too, not only the day)
but, using Fred`s example, i would have to make a select in the courses and use that another select that fred used as a condition for this first select?
like this?
SELECT DISTINCT courses. *
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.dia = ‘Monday’
WHERE EXISTS (
SELECT count( * ) AS times_scheduled
FROM courses_times
INNER JOIN courses_times AS other_times ON other_times.course_id = courses_times.course_id
WHERE courses_times.time_id = times.id
GROUP BY courses_times.course_id
HAVING times_scheduled =1
)
Sorry for the question but i`m new to sql and all this stuff…
hum.... but actually in my database it occurs... that are some coursers that
have two times that reffers to the same day (because actually in my db, time
has the hour too, not only the day)
but, using Fred`s example, i would have to make a select in the courses and
use that another select that fred used as a condition for this first select?
like this?
SELECT DISTINCT courses. *
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.dia = 'Monday'
WHERE EXISTS (
SELECT count( * ) AS times_scheduled
FROM courses_times
INNER JOIN courses_times AS other_times ON other_times.course_id =
courses_times.course_id
WHERE courses_times.time_id = times.id
GROUP BY courses_times.course_id
HAVING times_scheduled =1
)
I think you can do it with a similar query to my first, something
along the lines of
select *, count(distinct other_times) as days_scheduled from
course_times
inner join times on course_times.time_id = times.id
inner join course_times as other_course_times on
course_times.course_id = other_course_times.course_id
inner join times as other_times on other_times.id =
other_course_times.time_id
where times.day = 'Monday'
group by course_id
having days_scheduled = 1
You select course_times whose corresponding time has a day of monday.
You then join the course_times and times of the same course_id and
count the number of distinct days. In general, if you can write
something without a dependant subquery, then you should.
Have you considered an inner and outer join to the courses_times
table?
select distinct courses.*
from courses
inner join course_times monday_courses on courses.id =
monday_courses.course_id and monday_courses.time_id = 1
left outer join course_times non_monday_courses on courses.id =
non_monday_courses.course_id and non_monday_courses.time_id <> 1
where non_monday_courses.id is null
