yii2 how to make a 1:1:1 relationship sortable and searchable in GridView

Question

Given the 3 entities attendee, event, location, where every attendee hasOne event and every event hasOne location - How do I show a sortable and searchable name of location column in the GridView of attendee?

Schema

CREATE TABLE `attendee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`eventId` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_attendee_event` FOREIGN KEY (`eventId`) REFERENCES `event` (`id`))

 CREATE TABLE `event` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
 `locationId` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `fk_e_l` FOREIGN KEY (`locationId`) REFERENCES `location` (`id`))

 CREATE TABLE `location` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL)

I generated the models and crud with gii.

I want the location name in the GridView of attendee to be sort- and searchable.

I figured I have to define a relationship between attendee and location via event:

public function getLocation()
 {
     return $this->hasOne(Location::className(), ['id' => 'locationId'])->via('event');
}

In _columns.php I add location.name as column:

[
    'class'=>'\kartik\grid\DataColumn',
    'attribute'=>'location',
    'value' => 'location.name'

],

location.name appears as a column, but it is not sortable nor searchable. How do I achieve this?

ScaisEdge · Accepted Answer · 2015-11-21T18:01:55.597

For Sort in your ModelSearch

add the sort condition eg:

      $dataProvider->setSort([
        'attributes' => [
            'location.name' => [
                            'asc' =>    [ 'location.name' => SORT_ASC],
                            'desc' =>   [ 'location.name' => SORT_DESC],
                            'label' =>  'location.name'
                        ],

for query add your query condition the the other query eg:

      $query->joinWith(['location' => function ($q) {
         $q->where( 'name LIKE "%' . $this->location_name . '%"');
      }]);

You can find useful sample in this tutorial

yii2 how to make a 1:1:1 relationship sortable and searchable in GridView

1 Answers1