gridview - yii2相关数据搜索中与id冲突

Question

好吧，这是我的问题

我正在 GridView 中显示 2 个相关列都来自同一个外部表我将名字和姓氏显示为全名和电子邮件作为另一列所有这 3 个数据都来自同一个表

 'columns' => [
        ['class' => 'yii\grid\CheckboxColumn'],
        [

            'attribute' => 'event_id',
            'label' => 'Event Title',
            'value' => 'event.title'
        ],
        [
            'attribute' => 'user_id',
            'label' => 'Name',
            'value' => 'users.fullname',
        ],
        [
            'attribute' => 'user_id',
            'label' => 'Email',
            'value' => 'users.email',
        ],

如您所见，如果我想使其可搜索，我必须将属性指定为 user_id这是我的搜索模型

$query->joinWith(['event', 'users']);
$query->andFilterWhere(['like', 'event.title', $this->event_id]);
    $query->andFilterWhere(['like', 'user.firstname', $this->user_id]);
    $query->andFilterWhere(['like', 'user.email', $this->user_id]);

现在的问题是电子邮件搜索工作正常，我的意思是当我在电子邮件列的搜索框中输入任何数据时，它会呈现 user_id 正常，但它会自动创建像 where email=blah blah 这样的查询和名字=等等等等事实上，我还没有在fullname列搜索框中输入任何数据

当我在fullname列搜索中输入任何数据时，它不会找到该表的user_id

我应该如何解决这个冲突？？？哦，我的数据库结构是这样的这个 gridview 是 checkin 表，我在其中显示 checkin 特定事件的所有用户所以还有 2 个表 event 和 users

 CREATE TABLE IF NOT EXISTS `event` (
  `id` int(11) NOT NULL,
  `organiser_id` int(11) NOT NULL,
  `interest_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(500) NOT NULL,
  `location` varchar(255) NOT NULL,
  `is_active` bit(1) NOT NULL,

) 

 CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `dob` datetime DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `area_intrest` varchar(250) NOT NULL,
)

CREATE TABLE IF NOT EXISTS `checkin` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `user_type` enum('competitor','fan') NOT NULL,
)

这是我的搜索功能代码

class CheckinSearch extends Checkin

{

public $fullName;
/**
 * @inheritdoc
 */
public function rules()
{
    return [
        [['id', 'created_by', 'updated_by'], 'integer'],
        [['user_type', 'user_id', 'event_id', 'created_date', 'updated_date','fullName'], 'safe'],
    ];
}

/**
 * @inheritdoc
 */
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search($params)
{

    if(!isset($_GET['id'])){
        $id='';
    }
    else{
        $id=$_GET['id'];
    }

    $query = Checkin::find()->where(['event_id'=> $id]);

    $query->joinWith(['event', 'users']);


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

    $this->load($params);

    if (!$this->validate()) {
        // uncomment the following line if you do not want to return any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }

    $query->andFilterWhere([
        'id' => $this->id,
        'created_date' => $this->created_date,
        'created_by' => $this->created_by,
        'updated_date' => $this->updated_date,
        'updated_by' => $this->updated_by,
    ]);

    $query->andFilterWhere(['like', 'event.title', $this->event_id]);
    $query->andFilterWhere(['like', 'user.firstname', $this->fullName]);
    $query->andFilterWhere(['like', 'user.lastname', $this->fullName]);
    $query->andFilterWhere(['like', 'user.email', $this->user_id]);
    $query->andFilterWhere(['like', 'user_type', $this->user_type]);

    return $dataProvider;
    }
}

如果我添加名字和姓氏过滤器，它会生成这样的查询

   SELECT COUNT(*) FROM `checkin` LEFT JOIN `event` ON `checkin`.`event_id` = `event`.`id` 
LEFT JOIN `user` ON `checkin`.`user_id` = `user`.`id` WHERE 
((`event_id`='11') AND (`user`.`firstname` LIKE '%text%')) AND 
(`user`.`lastname` LIKE '%text%')

我想使用 AND 或 OR 过滤器生成这样的查询

SELECT * FROM `checkin` LEFT JOIN `event` ON `checkin`.`event_id` = `event`.`id` 
LEFT JOIN `user` ON `checkin`.`user_id` = `user`.`id` WHERE 
((`event_id`='11') AND (`user`.`firstname` LIKE '%text%')) OR 
((`event_id`='11') AND (`user`.`lastname` LIKE '%text%'))

谢谢

Answer 1

谢谢各位的帮助但我在 stackoverflow 上遇到了一个问题并解决了它这就是我所做的

$query->andFilterWhere(['or',
        ['like','user.firstname',$this->fullName],
        ['like','user.lastname',$this->fullName]]);

此查询将同时查找 firstname 或 lastname看来现在解决了我的问题