Yii2 single search box to search fields from two different tables

Question

I have products table and productlines table. Product has relationship with the productlines. I want to have a single search box and search the fields from product table and fields from productlines table.

My ProductSearch Model

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

When I do this I get the error:

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%')

Can anyone assist me where am I going wrong and what can be the possible solution.

Thanks

prefix table name `WHERE ((tableName.product_id`. Mysql could not resolve column name because both tables have same name. — Insane Skull, Jul 27 '16 at 11:03

score 0 · Answer 1 · answered Jul 27 '16 at 15:35

Yii2 allows you to prefix column names within ActiveQuery such as:

$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]);

Source: http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#relational-data

See the section "Joining with relations"

Yii2 single search box to search fields from two different tables

1 Answers1