-2
Based on documentation of Zend 1.12, using the Builder query:
How I select two tables with comma only without joinInner
:
Example:
SELECT TABELA1 FROM
(
BLABLABLA...
) A,
(SELECT TABELA2) B
To understand my problem, this is the code:
$selectRankQuery = $this->dbTable->select()
->setIntegrityCheck(false)
->from(array('opn' => 'olympic_players_niveis'),
array(
'opn.user_id',
'SUM(opn.points) as total_points'
));
if ($olympic_id != null) {
$selectRankQuery->where('opn.olympic_id = ? ', $olympic_id);
} else {
$selectRankQuery->joinInner(array('ol' => 'olympics'),
'opn.olympic_id = ol.ID and ol.active = 1',
array(
));
}
$selectRankQuery->group('opn.user_id');
$init = new Zend_Db_Expr("SELECT @rank := 0");
$select = $this->dbTable->select()
->setIntegrityCheck(false)
->from(array('rank_res' => $selectRankQuery),
array(
'rank_res.*',
new Zend_Db_Expr('@rank := @rank + 1 rank')
))
//o que eu uso aqui para ficar uma vírgula ????
->join(array('init' => $init));
$r = $select->assemble();
I just need to start the variable @rank := 0
in select;
I tried this, but the query did not show the ranking value:
->from(array('rank_res' => $selectRankQuery, 'init' => $init)
;
Basically, this should be the way out:
SELECT
`rank_res`.*,
@rank:=@rank + 1 rank
FROM
(SELECT
`opn`.`user_id`, SUM(opn.points) AS `total_points`
FROM
`olympic_players_niveis` AS `opn`
WHERE
(opn.olympic_id = 9)
GROUP BY `opn`.`user_id`) AS `rank_res`,
(SELECT @rank:=0) AS `init`