0

I am looking to achieve GROUP BY CAST(date_time AS DATE) in a db_select function in Drupal 7 but with no idea how.

The function up until now is:

 foreach ($distinct_users as $user){
    $day_record = db_select('mytable', 'p')
      ->condition('p.processed', 0, '=')
      ->condition('p.first_name', $user['first_name'], '=')
      ->condition('p.last_name', $user['last_name'], '=')

and I need to group the results by day which is in Y-m-d H:i:s in my table column.

Please advise.

Improve this question
4
  • Try with $connection = Database::getConnection(); $sth = $connection->select('mytable', 'p'); $sth->condition('p.processed', 0, '=') ->condition('p.first_name', $user['first_name'], '=') ->condition('p.last_name', $user['last_name'], '='); $sth->addExpression('CAST(p.date_time)', 'cast'); $sth->groupBy('p.date_time'); // Execute the statement $data = $sth->execute(); // Get all the results $results = $data->fetchAll(\PDO::FETCH_ASSOC); see here drupal.org/forum/support/module-development-and-code-questions/…
    – berramou
    Commented Dec 3, 2019 at 14:15
  • Well, pretty close to be honest. Now I get an array. Seems to return the right number of elements but it only returns the casted date. Something like $results{array} 0 = {StdClass}[1] cast = "2019-11-01" And so on.
    – Lia
    Commented Dec 3, 2019 at 14:27
  • Can you update your question with some examples of the rows in the database table, and what the array returned by a db_select() function on those rows should look like? If you know the SQL statement that gives the results you are looking for, then including that may be helpful as well.
    – runswithscissors
    Commented Dec 5, 2019 at 3:24
  • I found a workaround, I am currently grouping them in PHP instead of getting them grouped from SQL. What I was looking for was to get this into Drupal 7. SELECT * FROM mytable AS p WHERE p.processed = 0 AND p.first_name = my users's first name AND p.last_name = my user's last name GROUP BY CAST(p.date_time AS DATE).
    – Lia
    Commented Dec 5, 2019 at 6:40

1 Answer 1

Reset to default
0

The solution that worked for me was to group results via php.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.