0

I have a probably trivial question, but I could not find an answer. I have this query, which works fine:

$query = db_select('books, 't');
$query
  ->fields('t', ['publication_year'])
  ->groupBy('t.publication_year')
  ->orderBy('publication_year', 'ASC')
  ->addExpression('COUNT(t.publication_year)', 'n');
$data = $query->execute();
$result = $data->fetchAll();

I want to add a second expression:

(SELECT COUNT(*) FROM books WHERE open_access=1) AS n_oa

But I have two problems: When adding a second expression I get the error Method 'addExpression' not found in null|string regardless of the content.

When only adding the single expression ->addExpression('SELECT COUNT(*) from books WHERE open_access=1', 'n_oa'); I get a syntax error.

Edit:

I tried this, and it generally works except, that the seond expression can't find the 'year' alias.

 $query
    ->addField('t', 'publication_year', 'year');
  $query
    ->condition('wg', 'ag_zzexternal', '=')
    ->groupBy('t.publication_year')
    ->orderBy('publication_year', 'ASC')
    ->addExpression('COUNT(t.publication_year)', 'n');
  $query
    ->addExpression('SUM(IF(open_access=1,1,0) AND IF(publication_year=year,1,0))', 'n_oa');
}
  • 1
    The return from addExpression() is an alias, so it can't be chained. You can add a second expression using $query->addExpression(). I have never tried adding a nested select this way but perhaps someone else can help with that. – Alfred Armstrong Aug 19 at 15:33
  • Maybe try SUM(IF(open_access=1,1,0)) or SUM(open_access) instead? – Alfred Armstrong Aug 19 at 15:36
  • Thanks, this generally worked. I now want to expand the expression, but the expression cant find the alias i declared before. Is there a way to make it work? (see my edit part in the question). – Xyarvius Aug 20 at 7:53
  • 1
    Nevermind, i do not need the alias, i can just use t.publication_year, just like in the expression before. – Xyarvius Aug 20 at 8:03
0

I think the easiest solution will be to use db_query, where you can write raw SQL, like this:

$query = db_query('SELECT publication_year FROM books etc.');

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.