0

I have a query with a bunch of joins, conditions, etc that uses ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) to group the results so that I can filter them further on the resulting row number:

SELECT * FROM (
  SELECT
    X.id,
    Y.whatever,
    Z.blah,
    ...,
    ROW_NUMBER() OVER(PARTITION BY X.id ORDER BY Y.whatever) roWNum
  FROM
    X JOIN Y ON ... JOIN Z ON ...
) Q
WHERE Q.rowNum = 1

This works perfectly in T-SQL, but when I try to implement it with a Drupal 7 dynamic query, it's no good. I do the following:

$query = db_select('Table1', 'X')->condition(...);
$query->innerJoin('Table2', 'Y', ...); 
$query->innerJoin('Table3', 'Z', ...);
$query->addField('X', 'id', 'myID');
$query->addField('Y', 'whatever', 'foo');
...
$query->addExpression('ROW_NUMBER() OVER(PARTITION BY X.id ORDER BY Y.whatever)', 'rowNum');

$outerQuery =
db_select($query, 'Q')
->fields('Q', array('rowNum', ...))
->condition('Q.rowNum', 1);

Drupal changes it to THIS in the actual query, which I print to the screen using dpq():

SELECT
  Q.id, ..., cross_rowNum.cross_sqlsrv AS rowNum 
FROM (
  SELECT
    X.id,
    Y.whatever,
    Z.blah
  FROM
    X JOIN Y ON ... JOIN Z ON ...
    CROSS APPLY (SELECT ROW_NUMBER() OVER(PARTITION BY X.id ORDER BY Y.whatever) cross_sqlsrv) cross_rowNum
) Q
WHERE rowNum = 1

which of course is totally useless to me, because SELECT ROW_NUMBER() by itself, without the FROM clause, always just returns 1!

Is there a way I can force Drupal to do what I want, i.e. select the ROW_NUMBER() from the tables specified in my FROM clause, instead of creating its own useless SELECT out of nothing - or should I forget about using a dynamic query and just hardcode the entire thing with db_query()?!

  • I'm confused - your code doesn't contain a FROM anywhere - where are you expecting that to come from? Drupal, or more accurately the module you're using for MSSQL integration, isn't likely to try to fill in parts of the query for you – Clive May 2 at 8:06
  • @Clive I added more info - hope it's clear now. – Yury May 2 at 12:52
  • Oh ok that makes more sense - it sounds like you've hit a limitation/bug in the sqlsrv module. If you look at the replaceExpressionAliases() method in the SelectQuery_sqlsrv class, you'll see this comment: This function needs some work.... That's the function which the __toString() method is using to operate on the expressions you've added. – Clive May 2 at 13:01
  • @Clive Ah, I see... So it looks like there is no way to implement this using a dynamic select? I was really hoping to avoid using db_query()... – Yury May 2 at 13:04
  • There might be a way to get around it but I'm afraid I don't know of one - personally I'd be grudgingly going back to db_query too – Clive May 2 at 13:09

Your Answer

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

Browse other questions tagged or ask your own question.