How to translate a database query with a subquery to a db_select statement?

Question

Assume any variable that doesn't have a definition in the sample code is defined elsewhere. I need to get the results of the following query:

select type, eid, vid, language, keyword, custom_value from (select * from custom_keywords_entity_values where language = 'en' order by eid asc, vid desc) as ckev group by keyword;

However, I'm not entirely sure how to translate this into a query that leverages the Database API.

Fatal error: Unsupported operand types in /Users/lpeabody/Sites/jandj/docroot/includes/database/query.inc on line 1809

This is my working structure right now:

$subquery = db_select('custom_keywords_entity_values', 'a');
$subquery->fields('a')
  ->where('language', $language)
  ->orderBy('eid')
  ->orderBy('vid', 'DESC');

$query = db_select($subquery, 'ev');
$query->fields('ev')
  ->groupBy('keyword')
  ->execute();

$keywords = $query->fetchAllAssoc('keyword', PDO::FETCH_ASSOC);

I feel like that's the most literal translation, but I'm probably missing something since I'm getting this sweet error:

Fatal error: Unsupported operand types in /Users/lpeabody/Sites/jandj/docroot/includes/database/query.inc on line 1809

Let me know if there's any other information you need me to provide you.

Answer 1

SelectQuery::where() takes a string (not an object).

You probably meant to use SelectQuery::condition():

$subquery->fields('a')
  ->condition('language', $language)
  ->orderBy('eid')
  ->orderBy('vid', 'DESC');

Your execution is slightly off too; at the moment you're discarding the results of the execution, and instead invoking fetchAllAssoc() on the wrong type of object.

What you need is

$keywords = $query->fields('ev')
  ->groupBy('keyword')
  ->execute()
  ->fetchAllAssoc('keyword', PDO::FETCH_ASSOC);

or

$result = $query->fields('ev')->groupBy('keyword')->execute();
$keywords = $result->fetchAllAssoc('keyword', PDO::FETCH_ASSOC);

Or some other derivative of the same logic.

After those changes you should wind up with

SELECT  ev.*
FROM 
(SELECT  a.*
FROM 
{custom_keywords_entity_values} a
WHERE  (language = 'en')
ORDER BY eid ASC, vid DESC) ev
GROUP BY keyword

Pretty close to your original, and functionality identical.