How to construct database query with conditions that include 'and' and 'or' clauses?

Question

I am converting a Drupal 6 module to a Drupal 7 module. I have come across the following database query that I don't know how to convert to the new Drupal 7 format.

$sql = "
  SELECT id
  FROM {room_reservations}
  WHERE (
    (deleted = '%s' AND room = '%s' AND date = '%s' AND time = '%s' AND length > %d)
    OR
    (deleted = '%s' AND room = '%s' AND date = '%s' AND time = '%s' AND length > %d)
    OR
    (deleted = '%s' AND room = '%s' AND date = '%s' AND time = '%s' AND length > %d)      
  )
";

Although the where clauses do repeat itself, each substituted value in there is different. My question is how does one construct such a statement in Drupal 7 using the new database API? Can you mix and and or statements?

Thanks.

Answer 1

Here's an example, taken from statistics.pages.inc

$query = db_select('accesslog', 'a', array('target' => 'slave'))->extend('PagerDefault')->extend('TableSort');
$query->join('users', 'u', 'a.uid = u.uid');

$query
  ->fields('a', array('aid', 'timestamp', 'url', 'uid'))
  ->fields('u', array('name'))
   ->condition(db_or()
    ->condition('a.path', 'node/' . $node->nid)
    ->condition('a.path', 'node/' . $node->nid . '/%', 'LIKE'))
  ->limit(30)
  ->orderByHeader($header);

What you want to notice, is the use of db_or(), which adds an OR to your query. There is also the similar db_and(). You can mix and match the two as much as you want. The db_and() documentation also has comments with examples of the two used together.