phpmysqlyii2many-to-manyrelation

yii2 adding '0=1' to query on many to many relation


I have a project where users must be connected with each others - like friends. So i decided to connect them by the table 'connect'. Looks like it's rights. But when i tried to search for a friend requests i got the error, described below. Here is my model User:

<?php namespace common\models
use dektrium\user\models\User as BaseUser;
use Yii;
use yii\data\ActiveDataProvider;
use yii\helpers\ArrayHelper;
use dosamigos\taggable\Taggable;
use yii\db\ActiveQuery;
/**
* User model
*
* @inheritdoc
* @property string $search
* @property string $category
*
* @property ActiveQuery $requests
*/
class User extends BaseUser
{
#region Properties
public $category;
public $search;

const SCENARIO_CATEGORY = 'category';
const SCENARIO_SEARCH = 'search';
#endregion

#region Yii
/**
* @inheritdoc
*/
public static function tableName()
{
return '{{%user}}';
}

/**
* @inheritdoc
*/
public function rules()
{
return array_merge(parent::rules(), [
[['category'], 'safe', 'on' => self::SCENARIO_CATEGORY],
[['search'], 'safe', 'on' => self::SCENARIO_SEARCH],
]);
}

/**
* @inheritdoc
*/
function scenarios()
{
return array_merge(parent::scenarios(), [
self::SCENARIO_CATEGORY => ['category'],
self::SCENARIO_SEARCH => ['search']
]);
}
#endregion

#region Callbacks
function afterFind()
{
$this->category = implode(', ', ArrayHelper::map($this->getCategories()->asArray()->all(), 'id', 'name'));
parent::afterFind();
}

/**
* @inheritdoc
*/
public function behaviors()
{
return array_merge(parent::behaviors(), [
[
'class' => Taggable::className(),
'attribute' => 'category',
'relation' => 'categories',
]
]);
}
#endregion

#region Relations
/**
* @return \yii\db\ActiveQuery
*/
function getCategories()
{
return $this->hasMany(Category::className(), ['id' => 'category_id'])->viaTable('{{%category_user}}', ['user_id' => 'id']);
}

/**
* @return \yii\db\ActiveQuery
*/
function getRequests()
{
return $this->hasMany(User::className(), ['id' => 'user_two'])->viaTable(Connection::tableName(), ['user_one' => 'id']);
}
#endregion

#region Methods
function search($params)
{
$query = self::find();
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);

//        if (!$this->validate()) {
//            $query->where('0=1');
//            return $dataProvider;
//        }

$query->joinWith(['categories']);

$query->andFilterWhere(['like', 'category.name', $this->search]);

return $dataProvider;
}

function isConnected($user_id)
{
return Connection::isConnected(Yii::$app->user->id, $user_id);
}

function requestCount()
{
return Connection::requestCount(Yii::$app->user->id);
}

function requestPends()
{
$query = $this->getRequests();

$result = new ActiveDataProvider([
'query' => $query
]);

$query->joinWith(['requests']);
$query->from(User::tableName() . ' u1');
$query->where = "";
$query->andFilterWhere(['connection.status' => Connection::STATUS_PENDED]);

return $result;
}
#endregion
}

Here is my connection model:

namespace common\models;

use Yii;
use yii\data\ActiveDataProvider;
use yii\db\Query;

/**
* This is the model class for table "connection".
*
* @property integer $id
* @property integer $user_one
* @property integer $user_two
* @property integer $status
*
* @property User $userOne
* @property User $userTwo
*/
class Connection extends \yii\db\ActiveRecord
{
const STATUS_PENDED = 0;
const STATUS_ACCEPTED = 1;
const STATUS_DENIED = 2;

public static function primaryKey()
{
return array('id');
}

/**
* @inheritdoc
*/
public static function tableName()
{
return 'connection';
}

/**
* @inheritdoc
*/
public function rules()
{
return [
[['user_one', 'user_two', 'status'], 'integer'],
[['user_one'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['user_one' => 'id']],
[['user_two'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['user_two' => 'id']],
];
}

/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'id' => Yii::t('app', 'ID'),
'user_one' => Yii::t('app', 'User One'),
'user_two' => Yii::t('app', 'User Two'),
'status' => Yii::t('app', 'Status'),
];
}

/**
* @return \yii\db\ActiveQuery
*/
public function getUserOne()
{
return $this->hasOne(User::className(), ['id' => 'user_one']);
}

/**
* @return \yii\db\ActiveQuery
*/
public function getUserTwo()
{
return $this->hasOne(User::className(), ['id' => 'user_two']);
}

function search($params)
{
$query = self::find();
$result = new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);
//        if (!$this->validate()) {
//            $query->where('0=1');
//            return $result;
//        }
return $result;
}

static function isConnected($user_one, $user_two)
{
return self::find()->where('(user_one=:one AND user_two=:two) OR ((user_one=:two AND user_two=:one))', [':one' => $user_one, ':two' => $user_two])->andFilterWhere(['status' => Connection::STATUS_ACCEPTED])->count();
}

static function requestCount($user_id)
{
return (int)self::find()->where(['user_two' => $user_id])->andFilterWhere(['status' => Connection::STATUS_PENDED])->count();
}
}

request view:

<div class="panel panel-default">
<div class="panel-body">
<?php
//        if ($mdlUser->requestCount()) {
echo ListView::widget([
'dataProvider' => $mdlUser->requestPends(),
'itemView' => '_list',
]);
//        }
?>
</div>
</div>

Controller:

function actionFriendRequest()
{
/** @var User $mdlUser */
$mdlUser = Helper::findModel('\common\models\User', Yii::$app->user->id);
return $this->render('request', [
'mdlUser' => $mdlUser
]);
}

But yii2 make query like

`SELECT `u1`.* FROM `user` `u1` LEFT JOIN `connection` ON `u1`.`id` = `connection`.`user_one` LEFT JOIN `user` ON `connection`.`user_two` = `user`.`id` WHERE ((`connection`.`status`=0)) AND (0=1) LIMIT 20`

Question: Where did the (0=1) came from???


Solution

  • Take a look at your expression:

    $mdlUser = Helper::findModel('\common\models\User', Yii::$app->user->id);
    

    Most likely your 0=1 comes from Helper class, findModel method, which you haven't shown here.

    UPDATE: There are two more places where 0=1 can come from: QueryBuilder.php line 1077 and line 1226, which is the way yii2 handles empty strings/arrays for LIKE and IN conditions.