want to get data from mysql as string

prashant 0
Viewed 29
DB::table('follow_ups')
->select(
  'created_at as start',
   DB::raw('count(*) as title')
)->groupBy('created_at')->get()->toArray();

above query is returning title as integer, i want this value as string

mysql laravel
1 Answers

COUNT(*) is an aggregation function that will count distinct titles in a group. If you want to get all titles you need a different aggregation function that can (somehow) return all members of the group. There is one option that is generally available (i.e. even in older MySQL versions):

GROUP_CONCAT

DB::table('follow_ups')
->select(
  'created_at as start',
   DB::raw('GROUP_CONCAT(title) as title')
)->groupBy('created_at')->get()->toArray();

This will return all group members concatenated via the delimiter you specify (default is , if you don't specify one)

Newer MySQL versions also have JSON_ARRAYAGG

DB::table('follow_ups')
->select(
  'created_at as start',
   DB::raw('JSON_ARRAYAGG(title) as title')
)->groupBy('created_at')->get()->toArray();

This will return the result as a json array which you can then decode using json_decode($row['title'],true)

The advantage of the 2nd method is that it's easier to get individual values of the group in the cases where the group members contain the delimiter e.g. there's some title that contains a comma and would therefore make GROUP_CONCAT a bit useless.

Note: Both these functions are MySQL/MariaDB specific as far as I know so if you plan to make your database portable you might want to avoid them and look for alternatives that are ANSI SQL compliant.

apokryfos0
Related