SQL Query to select upcoming events with start and end dates

Question

I need to display upcoming events from the database. The problem is that when I use the query that I am currently using for any events with a missed start day, it will appear below in the list of upcoming events, regardless of whether they are current

My table (yaml):

columns: title: type: string(255) notnull: true default: Untitled Event start_time: type: time end_time: type: time start_day: type: date notnull: true end_day: type: date description: type: string(500) default: This event has no description category_id: integer 

My request (doctrine):

  $results = Doctrine_Query::create() ->from("sfEventItem e, e.Category c") ->select("e.title, e.start_day, e.description, e.category_id, e.slug") ->addSelect("c.title, c.slug") ->orderBy("e.start_day, e.start_time, e.title") ->limit(5) ->execute(array(), Doctrine_Core::HYDRATE_ARRAY); 

Basically, I would like the events that are currently happening (so today between start_day and end_day) to be at the top of the list. How can I do this if possible? Raw sql queries are good answers because they are pretty easy to convert to DQL.

DECISION Based on one of the following answers:

  $results = Doctrine_Query::create() ->from("sfEventItem e, e.Category c") ->select("e.title, e.start_day, e.description, e.category_id, e.slug") ->addSelect("c.title, c.slug") ->addSelect("IF(NOW() BETWEEN e.start_day AND e.end_day,0,1) as score") ->orderBy("score, e.start_day, e.start_time, e.title") ->limit(5) ->execute(array(), Doctrine_Core::HYDRATE_ARRAY); 

Answer 1

This should do:

 ->orderBy("IF(NOW() BETWEEN e.start_day AND e.end_day,0,1) , e.start_day, e.start_time, e.title") 

Answer 2

You can use case .

Add the case when the current date is between the start and end dates, with a rating of 1. All others with a score of 0

And then the order of the result.