Yii2: joinWith andFilterWhere with find_in_set

Question

I am trying to convert an SQL query like this

select 
t1.id,t1.form,t1.type,
group_concat(t2.department) as departments
from appraisal t1 
join department t2 on find_in_set(t2.id,t1.dept) 
group by t1.id

into yii2 AppraisalSearch model.

I tried

$query->joinWith(['departments' ,  'on find_in_set(department.id,appraisal.dept)   ']);

and

$query->andFilterWhere(
                 ['group_concat','appraisal','departments.department'])

, but it does not give correct results.

I also have added relation in appraisal model like

public function getDepartments()
    {
        return $this->hasMany(Department::className(), ['id' => 'dept']);
    }

What is the correct format to make this query work?

The table structure for appraisal table is

--
-- Table structure for table `appraisal`
--

CREATE TABLE `appraisal` (
  `id` int(11) NOT NULL,
  `type` varchar(250) DEFAULT NULL,
  `form` varchar(250) DEFAULT NULL,
  `start_time` date DEFAULT NULL,
  `end_time` date DEFAULT NULL,
  `dept` varchar(250) DEFAULT NULL,
  `admin_id` varchar(250) DEFAULT NULL,
  `appr` varchar(1000) DEFAULT NULL,
  `apps` varchar(1000) DEFAULT NULL,
  `desg_appr` varchar(250) DEFAULT NULL,
  `desg_apps` varchar(250) DEFAULT NULL,
  `remark` varchar(250) DEFAULT NULL,
  `sem` varchar(250) DEFAULT NULL,
  `acad_yr` varchar(250) DEFAULT NULL,
  `date` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `appraisal`
--
ALTER TABLE `appraisal`
  ADD PRIMARY KEY (`id`);

and table structure for department is

--
-- Table structure for table `department`
--

CREATE TABLE `department` (
  `id` int(11) NOT NULL,
  `department` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `department`
--
ALTER TABLE `department`
  ADD PRIMARY KEY (`id`);

In appraisal table, appraisal.dept , the values are stored in comma separated form, like 1,2,33,4 . Also how to apply andFilterWhere conditions for departments field

1. What does `not working` mean? Do you get an exception? If yes, pls share the exception with us. Do you get unexpected results? Then pls describe the results. 2. The SQL query has a group by clause, but no where clause. The yii code seems to do this the other way around. — Shadow, Feb 22 '18 at 11:34
Both `joinWith` and `andFilterWhere` are not implemented correctly. Also, you're using `andFilterWhere` (which adds conditions to the `WHERE` clause) in order to insert your `group_concat` expression, which clearly needs to go in the `SELECT` clause. I recommend you refamiliarize yourself with Yii2's documentation, and then try to come at this problem again. — ethan, Feb 22 '18 at 15:41

Reborn · Accepted Answer · 2018-02-26T17:01:01.717

You should be careful while using aliases they should not be any of the MySQL reserved keywords. So it is better not to use value but any other like myVal or more descriptive name that defines the value in it. I will use myVal in the example below.

You should use the following way to transform the query into the search model but before you add it you should declare a custom field named $myVal on top of your search model and rather than using the andFilterWhere for the related model you should specify it within joinWith() function using the relation departments when selecting the related model or leftJoin with the table department using FIND_IN_SET.

Define a custom field

public $myVal;

Then add the following inside your search model.

public function search(){
        $query=Appraisel::find();
        $query->select(new \yii\db\Expression('[[id]],group_concat(d.[[department]]) as myVal'));
        $query->leftJoin('{{%department}} d', 'find_in_set(d.[[id]],{{%appraisal}}.[[dept]])');

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

        $query->groupBy(['{{%appraisal}}.[[id]]']);
        return $dataProvider;
}

Hoipe it helps you out.

It did not work as intended. in t1.dept. values are stored as comma separated like 1,22,3 . — user7282, Feb 26 '18 at 05:41
you said you have the relation defined `departments`? anyhow I have changed the code check if this helps you out and do one thing if it does not work, add the `echo $query->createCommand()->rawSql;` in the end and comment out the `return $dataProvider`. and paste the SQL that is generated. @user7282 — Reborn, Feb 26 '18 at 16:14

Yii2: joinWith andFilterWhere with find_in_set

1 Answers1