hi ,
i am trying to add more columns by adding a
addSelect()
statement with a relation defined in my model like below
public function getPoolToTeams(){
return $this->hasMany(PoolToTeams::className(), ['pool_id'=>'id'])
->select([new \yii\db\Expression('*')])
->addSelect([new \yii\db\Expression('(SELECT COUNT(*) FROM {{%matches}} m where [[m.winner_id]]=[[team_id]] and [[m.pool_id]]=:pool_id) as won',[':pool_id'=>$this->id])])
->addSelect([new \yii\db\Expression('(SELECT COUNT(*) FROM {{%matches}} M WHERE (([[M.team_one]] =[[team_id]] or [[M.team_two]] = [[team_id]]) AND ([[M.winner_id]]!=[[team_id]]) AND ([[M.pool_id]]=:pool_id) AND [[M.winner_id]] IS NOT NULL)) as lost',[':pool_id'=>$this->id])])
->addSelect([new \yii\db\Expression('(SELECT COUNT(*) FROM {{%matches}} M WHERE (([[M.team_one]] =[[team_id]] or [[M.team_two]] = [[team_id]]) AND [[M.pool_id]]=:pool_id and [[M.winner_id]] IS NOT NULL)) as played',[':pool_id'=>$this->id])])
;
}
what i am doing is that i have the following table structure and i have to show the points table for the current running tournament accroding to the number of pools inside that tournament, and i want to show the listing sorted automatically as soon as the team scores are updated for that i wanted to define a relation in side the pools model that points to the pool_to_teams model and adds the count for the won lost and played matches for all th teams and show them sorted in there relative pool tables as soon as the match winner is decided but even after adding the addSelect statements the result set has only the original pool_to_teams columns only selected when i print the relation,
Can i dot it? ,
Or Am i doing it right?
TABLES
tournaments,
+-----------------+--------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| season | enum('summer','winter') | YES | | NULL | |
| Year | int(4) | YES | | NULL | |
| tournament_type | enum('league','annual-tournament') | NO | | league | |
| status | enum('waiting','finished','started') | NO | | waiting | |
+-----------------+--------------------------------------+------+-----+---------+----------------+
pools,
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | UNI | NULL | |
| tournament_id | int(11) | NO | MUL | NULL | |
+---------------+--------------+------+-----+---------+----------------+
matches,
+---------------+------------------------------------------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------------------------------------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| team_one | int(11) | NO | MUL | NULL | |
| team_two | int(11) | NO | MUL | NULL | |
| tournament_id | int(11) | NO | MUL | NULL | |
| pool_id | int(11) | YES | MUL | NULL | |
| winner_id | int(11) | YES | | NULL | |
| added_on | datetime | NO | | CURRENT_TIMESTAMP | |
| match_type | enum('pool','quarter','semi','final','league','challenge') | NO | | pool | |
| sets | smallint(2) | NO | | NULL | |
| match_time | datetime | NO | | NULL | |
+---------------+------------------------------------------------------------+------+-----+-------------------+----------------+
teams,
+------------+----------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | UNI | NULL | |
| image | text | YES | | NULL | |
| status | enum('active','in-active') | YES | | NULL | |
| added_on | datetime | YES | | CURRENT_TIMESTAMP | |
| updated_on | datetime | YES | | NULL | |
+------------+----------------------------+------+-----+-------------------+----------------+
pool_to_teams,
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| pool_id | int(11) | NO | MUL | NULL | |
| team_id | int(11) | NO | MUL | NULL | |
| tournament_id | int(11) | NO | MUL | NULL | |
+---------------+---------+------+-----+---------+----------------+