How to make this SQL Query?

Hello guys,

I have a problem with how to do a sql query.

I have 3 tables: courses, times and courses_times

So for example, if i have the following data:

Courses courses_times Times
id | name course_id | time_id id | day

1 | Math 1 | 1 1 | Monday
2 | Chimestry 1 | 2 2 | Wednesday
3 | History 1 | 3 3 | Friday
4 | Geografy 2 | 1
2 | 2
3 | 1

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…

Does anyone have a solution for this?

Thanks a lot

Fernando

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.

Colin

Colin Law wrote in post #972572:

FROM courses

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.

Colin

Best,

Colin Law wrote in post #972572:

FROM courses

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.

Please correct me, if I am wrong somewhere.

It sounds like you want a "NOT IN" subquery.

Jatin Kumar wrote in post #972576:

>
certain operations.

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.

Please correct me, if I am wrong somewhere.

Best,

Jatin Kumar wrote in post #972576:

certain operations.

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.

In sql terms, how about

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

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)

Does anyone have another ideia for this query?

Fernando

Jatin Kumar wrote in post #972585:

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.

Best,

Jatin Kumar wrote in post #972585:

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.

Thanks.

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?

djangst,

yes… its exactly this… but how can i do that in another way?

Thanks

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.

djangst,

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…

Thanks

Fernando

djangst,

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.

Fred

Hum… i tried it… but i always get this sql error

#1054 - Unknown column 'other_times' in 'field list'

do u know what can be that error?

Thanks

Should be other_times.day

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

Thanks Fred,

Well… i could make it work…
I dont know why, but it wastes about 25 seconds to give me query result…

Is it normal? the number of registers its like 8000 for courses, 13000 for coursers_times and 400 for times…

It must have a better way to do this… its a normal query…

Thanks

Fernando