SelectQuery row_number won't work with PDO

Question

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?

Answer 1

In order to count the number of occurence you can use groupBy and having cluases but as you mention above your format is completely different. So it is not feasible to you. Try this one may be it'll helpful to you.

$query = db_select('temp', 't');
$query->fields('t',array('a','b'));
$query->orderBy('t.a');
$result = $query->execute();

$field_sum = 1;
$prev = NULL;
foreach ($result as $record) {
   if ($prev ==  $record->a) {
      $field_sum++;
   } 
   else {
   $field_sum = 1;
   }

   echo "\n a " . $record->a . " | b " . $record->b . " | Counter ". $field_sum; 
   $prev = $record->a;
}

Also ensure that these list of function can't be chained:

addExpression()
addField()
addJoin()
extend()
innerJoin()
join()
leftJoin()
rightJoin()