2

I am creating an appointment booking module for my drupal 8 site, which is 99% complete, but I am one or two lines of code away from completing my module. The problem is using a database condition on an Count Expression that has been produced after the database has executed.

This is the code I have so far:

public static function dateavailabilty() {        
    $select = db_select('bookingdateandtime', 'e');
    $select->addField('e', 'datecode');
    $select->groupBy("e.datecode");
    $select->addExpression("COUNT(e.datecode)", 'count');
    $select->condition('count', 2, '>');

    $entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);

return $entries;}

But the page returns "The website encountered an unexpected error. Please try again later."

The following error report is produce in the Drupal Admin Error Log Messages:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Champ 'count' inconnu dans where clause: SELECT e.datecode AS datecode, COUNT(e.datecode) AS count FROM {bookingdateandtime} e WHERE (count > :db_condition_placeholder_0) GROUP BY e.datecode; Array ( [:db_condition_placeholder_0] => 2 ) in Drupal\booking\bookingDTdatabase::dateavail() (line 51 of C:\wamp\www\modules\booking\src\bookingDTdatabase.php).

I need to use a condition to only produce results that has a count of more than 2. Obviously, if I cancel out this line "$select->condition('count', 2, '>');", drupal returns everything perfectly. So, using the condition doesn't work on data that has been added after execution.

How do I go about using the correct condition to apply to the Expression?

Many Thanks for reading and helping.

4

Conditions on aggregates are addressed with HAVING in MySQL, so something similar to:

$select->having('count > :cnt', [':cnt' => 2]);
  • Hi Clive, thanks for getting back. I tried using that line and I get the following error message produced: Syntax error or access violation: 1463 Non-grouping field 'count' is used in HAVING clause: SELECT e.datecode AS datecode, COUNT(e.datecode) AS count FROM {bookingdateandtime} e GROUP BY e.datecode HAVING (count > :cnt); Array ( [:cnt] => 2 ) in Drupal\booking\bookingDTdatabase::dateavail() (line 51 of C:\wamp\www\modules\booking\src\bookingDTdatabase.php). – Jamie Jul 8 '16 at 11:30
  • You might need to be explicit, eg $select->having('COUNT(e.datecode) > :cnt', [':cnt' => 2]); – Clive Jul 8 '16 at 11:34
  • Excellent Clive. That worked, so I tested if the following works $select->having('COUNT(e.datecode) > 2'); And it does. I tried the "having" type last night using $select->having('count' > 2'); but it didn't work. It seems like that I have to re-COUNT the data value, and cannot rely on the count variable in the expression. Many Thanks. – Jamie Jul 8 '16 at 11:58

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.