public function search($params) { $query = Member::find()->indexBy('id'); $query->poll_id($this->poll_id); $query->distinct(); // also possible is to group by member.id to get only the unique member entries. //$query->addGroupBy($this->tableName().'.id'); // $query->with('codes.vote'); // old join //$query->joinWith('codes', false, 'LEFT JOIN'); //$query->joinWith('codes.vote', true, 'LEFT JOIN'); // $query->joinWith(['relationname' => function ($query) { // $query->from(['alias' => 'tablename']); // }]); // $query->joinWith(['codes' => function ($query) { // $query->from(['codes' => 'code']); // }]); $query->joinWith(['codes.vote']); $dataProvider = new ActiveDataProvider(['query' => $query, 'sort' => ['defaultOrder' => ['name' => SORT_ASC]], 'pagination' => ['pageSize' => 20]]); if (!($this->load($params) && $this->validate())) { return $dataProvider; } $query->andFilterWhere(['id' => $this->id, 'created_at' => $this->created_at, 'updated_at' => $this->updated_at, 'created_by' => $this->created_by, 'updated_by' => $this->updated_by]); $query->andFilterWhere(['like', 'name', $this->name])->andFilterWhere(['like', 'group', $this->group]); //if code CODE_STATUS_INVALID_UNUSED is searched also search for CODE_STATUS_INVALID_USED if ($this->getAttribute('codes.code_status') == Code::CODE_STATUS_INVALID_UNUSED) { $query->andWhere([Code::tableName() . '.code_status' => [Code::CODE_STATUS_INVALID_UNUSED, Code::CODE_STATUS_INVALID_USED]]); } else { $query->andFilterWhere(['=', Code::tableName() . '.code_status', $this->getAttribute('codes.code_status')]); } return $dataProvider; }
public function search() { $count = Yii::$app->db->createCommand("SELECT\n\t\t\t\tCOUNT(DISTINCT (z_p_id))\n\t\t\t\tfrom " . Code::tableName() . "\n\t\t\t\twhere LOWER(z_b_id) = :bank\n\t\t\t\t", [':bank' => strtolower($this->z_b_id)])->queryScalar(); $dataProvider = new SqlDataProvider(['sql' => "select\n\t\t\t\tbezeichnung,\n\t\t\t\tcount(z_b_id) as tic_count,\n\t\t\t\tSUM(if(used = 1, 1, 0)) AS bused,\n\t\t\t\tSUM(if(status > 49 and used=0, 1, 0)) AS bused50,\n\t\t\t\tSUM(if(status > 49, 1, 0)) AS busedtot\n\t\t\t\tfrom " . Code::tableName() . ", " . Group::tableName() . "\n\t\t\t\twhere LOWER(z_b_id) = :bank and z_p_id = p_id\n\t\t\t\tgroup by z_p_id", "params" => [':bank' => strtolower($this->z_b_id)], 'totalCount' => $count]); return $dataProvider; }