db_select() ignores condition() when used with join

Question

I want to select all rows from 'ha' table, which access column are >= 0 and node type of that nid is equal $this->ctype.

  protected function queryAlter() {

    if(isset($this->ctype)) {

        $this->query->leftJoin('node', 'n', 'n.nid = ha.nid');
        $this->query->conditions(
                db_and()
                ->condition('ha.access', 0, '>=')
                ->condition('n.type', $this->ctype));
        //dpr($this->query);
    }
    else {
        $this->query->condition('ha.access', 0, '>=');
    }
  }

$this->ctype is set and equal to 'ad'. And I have not any nodes with that type. But db_select() ignores condition('n.type', $this->ctype) and returns all rows from ha (which NID's are not ad type).

Why this happens? And how to debug db_select() in general?

Answer 1

HearthbeatStream::$query is an object of the class PagerActivity which extend the class SelectQuery; this is evident from the code for HeartbeatStream::createQuery(), where the value of the property is initialized with the following line.

$this->query = db_select('heartbeat_activity', 'ha')->extend('PagerActivity');

The fact PagerActivity is an extender of SelectQuery means that every method implemented by SelectQuery is also implemented from PagerActivity.

SelectQuery::conditions() is a getter method, not a setter method. The only method to set the condition associated with a SelectQuery object is SelectQuery::condition().

The code should be re-written as:

  protected function queryAlter() {
    if (isset($this->ctype)) {
      $this->query->leftJoin('node', 'n', 'n.nid = ha.nid');
      $this->query->condition(('ha.access', 0, '>=')
        ->condition('n.type', $this->ctype));
    }
    else {
        $this->query->condition('ha.access', 0, '>=');
    }
  }

To make a comparison, this is the code executed from blog_page_user():

  $query = db_select('node', 'n')->extend('PagerDefault');
  $nids = $query
    ->fields('n', array('nid', 'sticky', 'created'))
    ->condition('type', 'blog')
    ->condition('uid', $account->uid)
    ->condition('status', 1)
    ->orderBy('sticky', 'DESC')
    ->orderBy('created', 'DESC')
    ->limit(variable_get('default_nodes_main', 10))
    ->addTag('node_access')
    ->execute()
    ->fetchCol();

There are some similarities with your code:

• in both the cases, the value returned from db_select() is passed to the extend() method
• in both the cases, more than one condition is applied

Using the value returned from db_and() with SelectQuery::condition() is not necessary, as the constructor of SelectQuery initializes the value of SelectQuery::$where (used by SelectQuery::condition()) with the following code:

  $this->where = new DatabaseCondition('AND');

The returned value is the same returned by db_and().

function db_and() {
  return new DatabaseCondition('AND');
}