0

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?

0

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()
  • Thank you for the proposition, but I need the row_number to be included in the select query, because it is being used in the FORM clause of another query (to filter the 'first' x occurencies of each a). Something like this: SELECT o.a AS a, SUM(o.b) AS total FROM (....) o WHERE o.counter <= 2 GROUB BY o.a where .... stands for the query in question. – Pavlos Katsonis Feb 18 '15 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.