0

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.

2

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.

  • Thanks Clive! Yeah that was quite sloppy, I should've re-read before posting. It's working now. – Lester Peabody Aug 5 '14 at 16:53

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.