How to make this SQL Query?

rubyonrails-talk
21

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

22

Just tacking on another suggestion to this if people are reading back through here: if you do need literal SQL its a good idea to put it in a configuration file with a lookup key (i.e. :count_all_my_angry_birds); that way if you switch db engines or support multiple ones all your specific SQL is in one location that you can ensure works for whatever different dbs you need to support. And of course keep that file as ANSI compliant so that there are as little changes required as possible.

\Peter

23

ppgengler@prevailhs.com wrote in post #972869:

syntax, without proprietary extensions. This gives the best portability across databases.

Just tacking on another suggestion to this if people are reading back through here: if you do need literal SQL its a good idea to put it in a configuration file with a lookup key (i.e. :count_all_my_angry_birds);

Why not just use a named scope (or the Rails 3 equivalent)? That's what I tend to do for complex queries. Granted, you don't get all the SQL in one file, but that's a *good* thing: it means you're looking at the SQL in context.

I want to like your config file idea, but I think it's just reinventing stored procedures in a way that removes their remaining advantages.

that way if you switch db engines or support multiple ones all your specific SQL is in one location that you can ensure works for whatever different dbs you need to support. And of course keep that file as ANSI compliant so that there are as little changes required as possible.

...in which case your proposed solution isn't necessary anyway. :slight_smile:

\Peter

Best,

24

Hi thoen,

Well… i tried to use your query…

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

but, actually, in my database, at times table, i have another attribute like the hour of the course… and so, i cant ask non_monday_courses.time_id <> 1, because actually i dont know if what i want to search is the register of id =1 of my courses_times table, i only know things about times… i have to make a join in the times table and ask times.day <> ‘Seg’, could you understand? i wasn’t so clear…

how could i make your query but using times.day <> ‘Seg’ instead of non_monday_courses <> 1 ???

Thanks

Fernando