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
On 5 January 2011 17:38, Fernando L.
[email protected] wrote:
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.
Colin
Colin L. wrote in post #972572:
On 5 January 2011 17:38, Fernando L.
[email protected] wrote:
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,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
On Wed, Jan 5, 2011 at 5:50 PM, Marnen Laibow-Koser
[email protected]wrote:
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.
Jatin K. wrote in post #972576:
On Wed, Jan 5, 2011 at 5:50 PM, Marnen Laibow-Koser
[email protected]wrote:
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,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
On Wed, Jan 5, 2011 at 6:10 PM, Marnen Laibow-Koser
[email protected]wrote:
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.
and
It sounds like you want a “NOT IN” subquery.
On Jan 5, 12:38pm, Fernando L. [email protected]
On Jan 5, 5:38pm, Fernando L. [email protected]
wrote:
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?
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
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 C. [email protected]
Jatin K. wrote in post #972585:
On Wed, Jan 5, 2011 at 6:10 PM, Marnen Laibow-Koser
[email protected]wrote:
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,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
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?
On Jan 5, 1:35pm, Fernando L. [email protected]
On Wed, Jan 5, 2011 at 6:40 PM, Marnen Laibow-Koser
[email protected]wrote:
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.
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.
On Jan 5, 1:50pm, Fernando L. [email protected]
djangst,
yes… its exactly this… but how can i do that in another way?
Thanks
2011/1/5 djangst [email protected]
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
2011/1/5 djangst [email protected]
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
2011/1/5 Frederick C. [email protected]
Should be other_times.day
Sent from my iPhone
On Jan 5, 8:31pm, Fernando L. [email protected]
wrote:
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
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
On Jan 5, 12:38pm, Fernando L. [email protected]
On Jan 5, 10:59pm, Fernando L. [email protected]
wrote:
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…
Depends on what indexes there are. You can use explain to see how the
database is executing your query. Even a fairly small join will run
incredibly slowly in the presence of insufficient indexes
Fred