I'm working on a project that needs to query a MySQL database and select the row number (among other columns) for each row. To be more specific, I have the table test
and want to get the table result
:
test result a | b a | b |counter 1 | 1 1 | 1 | 1 1 | 3 1 | 3 | 2 1 | 5 1 | 5 | 3 2 | 1 2 | 1 | 1 2 | 2 2 | 2 | 2 3 | 1 3 | 1 | 1 3 | 3 3 | 3 | 2
where column counter
counts the number of occurences of a
with different b
I have found the solution for MySQL in a post, using variables inside the query:
SELECT
o.a AS a,
o.b AS b,
@num := IF(@a = a, @num + 1, 1) AS counter,
@a := a AS dummy
FROM
(
SELECT
t.a AS a,
t.b AS b
FROM test t
ORDER BY a ASC
) o
which gives me the exact result (with the addition of one dummy column).
The problem arises when I try to implement it in Drupal 7 using the following code:
$tq = db_select('test', 't')
->fields('t', array('a', 'b'));
// Order rows for correct numbering
$tq->orderBy('a');
$t2q = db_select($tq, 'o')
->fields('o', array('a', 'b'));
// Add counter
$t2q->addExpression('@num := IF(@a = a, @num + 1, 1)', 'counter');
// Add dummy column
$t2q->addExpression('@a := a', 'dummy');
// Store results
$r = array();
foreach($t2q->execute() as $row)
$r[] = (array)$row;
dpm($r, $t2q->__toString());
The prepared query is correct and exactly the same as the above query:
SELECT o.a AS a, o.b AS b, @num := IF(@a = a, @num + 1, 1) AS counter, @a := a AS dummy FROM (SELECT t.a AS a, t.b AS b FROM {test} t ORDER BY a ASC) o
but the result is:
test result a | b a | b |counter 1 | 1 1 | 1 | 1 1 | 3 1 | 3 | 1 1 | 5 1 | 5 | 1 2 | 1 2 | 1 | 1 2 | 2 2 | 2 | 1 3 | 1 3 | 1 | 1 3 | 3 3 | 3 | 1
I have no idea of the internals of PDO and the reason why variables inside SELECT
statements won't work.
If I try with db_query feeding it the prepared query (as in the following code) I get the same results.
$tq3 = db_query($t2q->__toString());
while($row = $tq3->fetchAssoc())
$r[] = $row;
dpm($r);
But if I first call execute
on the query I get the correct results (but can not use extend
on DatabaseStatementInterface
).
$tq3 = db_query($t2q->__toString());
$tq3->execute();
while($row = $tq3->fetchAssoc())
$r[] = $row;
dpm($r);
Does anyone have any helpfull idea?