Yii2单个搜索框,用于搜索两个不同表中的字段

eufgjt7s  于 2022-11-09  发布在  其他
关注(0)|答案(1)|浏览(135)

我有产品表和产品线表。产品与产品线有关系。我希望有一个搜索框,并搜索产品表中的字段和产品线表中的字段。

我的产品搜索模型

public function search($params)
    {
        $query = Product::find()->where(['product_id' => $this->getProductID()]); 

        // add conditions that should always apply here
         $query->joinWith('productlines');

        $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;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'product_id' => $this->product_id,
            'product_region' => $this->product_region,
            'product_created' => $this->product_created,
            'product_lastchecked' => $this->product_lastchecked,
            'sdsref_id' => $this->sdsref_id,

        ]);

       // var_dump($this->getProductID()); exit();
        $query->andFilterWhere(['like', 'product_name', $this->product_name])
            ->andFilterWhere(['like','product_id', $this->product_id])
            ->orFilterWhere(['like', 'product_catalog', $this->code])
            ->andFilterWhere(['=', 'product_aka', $this->product_aka])
            ->orFilterWhere(['like', 'internal_code' , $this->code]);

        return $dataProvider;
    }

当我这样做时,我得到错误:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'product_id' in where clause is ambiguous
The SQL being executed was: SELECT COUNT(*) FROM `sim_product` LEFT JOIN `sim_productlines` ON `sim_product`.`product_id` = `sim_productlines`.`product_id` WHERE ((`product_id` IN ('2', '3')) OR (`product_catalog` LIKE '%A%')) OR (`internal_code` LIKE '%A%')

有谁能帮助我,我哪里出错了,什么可以是可能的解决方案。
谢谢

o3imoua4

o3imoua41#

Yii2允许您在ActiveQuery中为列名添加前缀,例如:

$query->andFilterWhere(['like', 'product.product_name', $this->product_name])
    ->andFilterWhere(['like','productline.product_id', $this->product_id])
    ->orFilterWhere(['like', 'productline.product_catalog', $this->code])
    ->andFilterWhere(['=', 'product.product_aka', $this->product_aka])
    ->orFilterWhere(['like', 'product.internal_code' , $this->code]);

来源:http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#relational-data
请参见“使用关系连接”一节

相关问题