Сложный запрос для GridView (Join'ы)

[zelenin]

Пытаюсь составить сложный запрос для отображения GridView.
Вот оригинальный запрос:

Код: Выделить всё

select
`id`,
`name`,
round( ( 1000 + ifnull( `temp1`.`rate`, 0 ) - ifnull( `temp2`.`rate`, 0 ) ) ) as `rating`,
ifnull( `temp1`.`count`, 0 ) + ifnull( `temp2`.`count`, 0 ) as `matches`
from `team`

left join (
    select `team1_id`, sum( `rate` ) as `rate`, count( `team1_id` ) as `count`
    from `match` group by `team1_id`
) as `temp1` on `id` = `temp1`.`team1_id`

left join (
    select `team2_id`, sum( `rate` ) as `rate`, count( `team2_id` ) as `count`
    from `match` group by `team2_id`
) as `temp2` on `id` = `temp2`.`team2_id`

group by `id`
having `matches` <> "0"
order by `rating` desc, `name` asc

Вот, что получилось пока:

Код: Выделить всё

echo GridView::widget( [
    'filterModel' => null,
    'dataProvider' => new ActiveDataProvider( [
        'query' => Team::find()
                ->addGroupBy( 'id' )
                ->orderBy( [
                    'rating' => SORT_DESC,
                    'name' => SORT_ASC
                ] ),            
        'pagination' => [ 'pageSize' => 100 ]
    ] )
] );
 

либо такой вариант:

Код: Выделить всё

$query = new Query();
echo GridView::widget( [
    'filterModel' => null,
    'dataProvider' => new ActiveDataProvider( [
        'query' => $query->select( 'id, name' )
                    ->from( '{{%team}}' )
                    ->addGroupBy( 'id' )
                    ->orderBy( [
                        'rating' => SORT_DESC,
                        'name' => SORT_ASC
                    ] ),
        'pagination' => [ 'pageSize' => 100 ]
    ] )
] );
 

не знаю как быть с join'ами

[kate kate]

я использовала SqlDataProvider. Но это был случай, когда у меня не было модели для таблицы, с которой был join.

[zelenin]

я использовала SqlDataProvider. Но это был случай, когда у меня не было модели для таблицы, с которой был join.

Благодарю за подсказку. Получившийся код:

Код: Выделить всё

$dataProvider =  new SqlDataProvider( [
    'sql' => 'select id, name,
    round( ( 1000 + ifnull( temp1.rate, 0 ) - ifnull( temp2.rate, 0 ) ) ) as rating,
    ifnull( temp1.count, 0 ) + ifnull( temp2.count, 0 ) as matches
    from {{%team}}

    left join (
        select team1_id, sum( rate ) as rate, count( team1_id ) as count
        from {{%match}} group by team1_id
    ) as temp1 on id = temp1.team1_id

    left join (
        select team2_id, sum( rate ) as rate, count( team2_id ) as count
        from {{%match}} group by team2_id
    ) as temp2 on id = temp2.team2_id

    group by id
    having matches <> 0',
    'sort' => [
        'defaultOrder' =>  [
            'rating' => SORT_DESC,
            'name' => SORT_ASC
        ],
        'attributes' => [
            'rating',
            'name',
            'matches' => [ 'default' => SORT_DESC ],
        ],
    ],
    'pagination' => [ 'pageSize' => 100 ],
] );

$grid_id = 'team-grid';
echo GridView::widget( [
    'filterModel' => null,
    'dataProvider' => $dataProvider,
    'id' => $grid_id,
    'tableOptions' => [
        'class' => 'table table-borderless',
        'id' => 'ranking-table'

    ],
    'layout' => '{items}',
    'columns' => [
        [ 'class' => yii\grid\SerialColumn::className() ],
        'name',
        'rating',
        'matches',
    ]
] );
 

[zelenin]

в догонку следующий вопрос: как понятно, у меня таблица следующего содержания

Код: Выделить всё

#   Name     Rating   Matches
1   Team 4   1200     200
2   Team 2   1100     300
3   Team 1   1000     700
4   Team 3   900      150

Сортировка по умолчанию идет по колонке Rating. В колонке # должны проставляться места команд, данные по рейтингу. На данный момент там в качестве заглушки [ 'class' => yii\grid\SerialColumn::className() ], то есть в независимости от сортировки всегда 1,2,3,4. Мне же нужно, чтобы данные места сохранялись, т.е. при сортировке по Matches:

Код: Выделить всё

#   Name     Rating   Matches
3   Team 1   1000     700
2   Team 2   1100     300
1   Team 4   1200     200
4   Team 3   900      150

Вижу два варианта: дописать что-то в sql-запрос либо написать свой класс колонки, например PlaceColumn, но конкретных мыслей по реализации нет совершенно.

[kate kate]

как я себе представляю, проблема решится, если хранить places в базе (если они там не хранятся) и использовать dataColumn вместо serial

[zelenin]

как я себе представляю, проблема решится, если хранить places в базе (если они там не хранятся) и использовать dataColumn вместо serial

да, но это не статичная таблица, будут добавляться матчи, пересчитываться рейтинги и соответственно места

сейчас написал такой запрос:

Код: Выделить всё

select
@n := @n + 1 as n,
id,  name, rating, matches
from ( select
`id`,
`name`,
round( ( 1000 + ifnull( `temp1`.`rate`, 0 ) - ifnull( `temp2`.`rate`, 0 ) ) ) as `rating`,
ifnull( `temp1`.`count`, 0 ) + ifnull( `temp2`.`count`, 0 ) as `matches`
from `team`

left join (
    select `team1_id`, sum( `rate` ) as `rate`, count( `team1_id` ) as `count`
    from `match` group by `team1_id`
) as `temp1` on `id` = `temp1`.`team1_id`

left join (
    select `team2_id`, sum( `rate` ) as `rate`, count( `team2_id` ) as `count`
    from `match` group by `team2_id`
) as `temp2` on `id` = `temp2`.`team2_id`

group by `id`
having `matches` <> "0"

) as n_temp
join (
    select @n := 0
) as temp

но при сортировке например по matches order by добавляется в конец запроса, а для правильной нумерации нужно добавлять в конец запроса, формирующего промежуточную таблицу n_temp

[kate kate]

а если в таком случае использовать arrayDataProvider? тогда сначала делаем Query, потом проходимся по нему в foreach, записываем дополнительно в массив что-то типа place (который по сути будет представлять из себя $key из форича), а потом отдаем конечный массив arrayDataProvider-у?

[zelenin]

а если в таком случае использовать arrayDataProvider? тогда сначала делаем Query, потом проходимся по нему в foreach, записываем дополнительно в массив что-то типа place (который по сути будет представлять из себя $key из форича), а потом отдаем конечный массив arrayDataProvider-у?

еще раз спасибо, так и сделал. Итоговый код:

Код: Выделить всё

$command = Yii::$app->db->createCommand( '
select
    id, name,
    round( ( 1000 + ifnull( temp1.rate, 0 ) - ifnull( temp2.rate, 0 ) ) ) as rating,
    ifnull( temp1.count, 0 ) + ifnull( temp2.count, 0 ) as matches
    from {{%team}}

    left join (
        select team1_id, sum( rate ) as rate, count( team1_id ) as count
        from {{%match}} group by team1_id
    ) as temp1 on id = temp1.team1_id

    left join (
        select team2_id, sum( rate ) as rate, count( team2_id ) as count
        from {{%match}} group by team2_id
    ) as temp2 on id = temp2.team2_id

    group by id
    having matches <> 0
    order by `rating` desc, `name` asc
' );
$models = $command->queryAll();
for ( $i = 0; $i < count( $models ); $i++ ) {
    $models[$i]['place'] = $i + 1;
}

$dataProvider = new ArrayDataProvider([
     'allModels' => $models,
    'sort' => [
        'defaultOrder' =>  [
            'rating' => SORT_DESC,
            'name' => SORT_ASC
        ],
        'attributes' => [
            'rating',
            'name',
            'matches' => [ 'default' => SORT_DESC ],
        ],
    ],
    'pagination' => [ 'pageSize' => 100 ],
 ]);

$grid_id = 'team-grid';
echo GridView::widget( [
    'filterModel' => null,
    'dataProvider' => $dataProvider,
    'id' => $grid_id,
    'tableOptions' => [
        'class' => 'table table-borderless',
        'id' => 'ranking-table'
    ],
    'layout' => '{items}',
    'columns' => [
        'place',
        'name',
        'rating',
        'matches',
    ]
] );