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

Answer 1

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.