Query not executing

This query returns data rows (which is good):




$staff = (new \yii\db\Query())

    ->select('*')

    ->from('staff')

    ->where(['id' => $profile->staff_id])

    ->orWhere(['id' => $bus->id])

    ->andWhere(['<>', 'staff_id', $some->id])

    ->groupBy('staff_id')

    ->all();



But this query only returns the query object (which is not good):




$staff = (new \yii\db\Query())

    ->select('*')

    ->from('staff')

    ->where(['id' => $profile->staff_id]);

if ($bus->id != NULL) {

    $staff->orWhere(['id' => $bus->id]);

}

$staff->andWhere(['<>', 'staff_id', $some->id])

    ->groupBy('staff_id')

    ->all();



What gives?

Try outputting the raw SQL command and running it in phpMyAdmin or whatever GUI you use to see if results are being returned.


$staff = (new \yii\db\Query())

        ->select('*')

        ->from('staff')

        ->where(['id' => $profile->staff_id]);

        if ($bus->id != NULL) {

            $staff->orWhere(['id' => $bus->id]);

        }

    

echo    $staff->andWhere(['<>', 'staff_id', $some->id])

            ->groupBy('staff_id')

            ->createCommand()->getRawSql();

Yes, the query works. The two queries above are identical except that I make one of the where clauses conditional. In fact, anytime I break up any part of the query, it stops working. For example, this query executes:




 $staff = (new \yii\db\Query())

     ->select('staff_id')

     ->from('staff')

     ->all();



But this one does not:




 $staff = (new \yii\db\Query())

     ->select('staff_id')

     ->from('staff');

 $staff->all();



Those are not equivalent. The first assigns an array of query results to $staff while the second assigns a query object to $staff.

What is the output if, in the second, you change the last line to


\yii\helpers\VarDumper::dump($staff->all());

?

Given:




$staff = (new \yii\db\Query())

    ->select('staff_id')

    ->from('staff')

    ->where(['id' => $profile->staff_id]);



The result of


var_dump($staff);

is:




object(yii\db\Query)#150 (17) {

  ["select"]=>

  array(1) {

    [0]=>

    string(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' /> "staff_id"

  }

  ["selectOption"]=>

  NULL

  ["distinct"]=>

  NULL

  ["from"]=>

  array(1) {

    [0]=>

    string(5) "staff"

  }

  // ...etc.



And given


$staff->all();

the result of


var_dump($staff);

is:




object(yii\db\Query)#150 (17) {

  ["select"]=>

  array(1) {

    [0]=>

    string(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' /> "staff_id"

  }

  ["selectOption"]=>

  NULL

  ["distinct"]=>

  NULL

  ["from"]=>

  array(1) {

    [0]=>

    string(5) "staff"

  }

  // ...etc.



But


var_dump($staff->all());

returns data rows as expected.

Is there an addWhereCondition() kind of thing?

The guide has plenty of examples of building the query in a piecemeal way.

The only workaround I see at this point is to write two queries in an if…else block. uugh…

Larry Ullman’s book to the rescue. I found the problem.


$q = new Query;

$q ->select('*')

    ->from('staff')

    ->where(['id' => $profile->staff_id]);

if ($bus->id != NULL) {

    $q->orWhere(['id' => $bus->id]);

}

$q->andWhere(['<>', 'staff_id', $some->id])

    ->groupBy('staff_id');

$staff = $q->all();

I just did this




public function sendDepartmentEmail($department = null) {

    $query = User::find()->where(['is_active' => \common\models\User::STATUS_ACTIVE])

            ->joinWith('profile');


    if (!is_null($department)) {

        $query->andWhere(['profile.department_id' => $department]);

    }

    $users = $query->all();

...

}



and it gives me everyone if no department is selected, and ONLY those assigned to that department.

It looks like the trick is ::find() returns a query. You don’t get models until the ->all() statement.