Using COUNT() function in Eloquent ORM (outside La

Question

I'm trying to replicate an SQL query with Eloquent ORM:

SELECT DISTINCT name, COUNT(name) AS total
FROM tags
WHERE question_id IS NOT NULL
GROUP BY name
ORDER BY name ASC;

Here is my attempt in Eloquent ORM:

$query = Tag::query();
$query->orderBy('name', 'asc');
    ->groupBy('name');
    ->select( array('count(name) as total') );
    ->whereNotNull('question_id');

However, this gives me the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count(name)' in 'field list' (SQL: SELECTCOUNT(name)AStotalFROMtagsWHEREtags.deleted_atIS NULL ANDquestion_idIS NOT NULL GROUP BYnameORDER BYnameASC)

... so clearly I'm not using it properly.

I found a post saying that I should use DB::raw('COUNT(name) as total') but I don't know where DB class is as I'm not working within Laravel. If I need to use this, how can I use it outside Laravel. I tried use for both:

use Illuminate\Database\Connection as DB;
use Illuminate\Database\Query\Builder as DB;

..., as these were the only classes, using grep, I could find to have a public 'function raw' within them. Query/Builder gave me an error, and Connection run OK but perhaps I didn't build the query object correctly as it gives me inconsistent results from running SQL in MySQL shell. Kinda hit a dead end, can anyone offer any help?

Thanks

Answer 1

This works, not sure if it's the cleanest way though: $query->selectRaw('DISTINCT name, COUNT(name) as total')

Answer 2

To achieve what you want you can use both Eloquent and DB:: query by use this class use Illuminate\Database\DatabaseManager; if you are using Laravel 4.1.x

On top of your file

use Illuminate\Database\DatabaseManager;

Is a good practise to inject the dependency in you class (controller or whatever it is)

/**
 * @var DatabaseManager
 */
private $databaseManager;

/**
 * @param DatabaseManager        $databaseManager
 */
public function __construct(DatabaseManager $databaseManager)
{
    $this->databaseManager = $databaseManager;
}

Then in your method, I'm just assuming your table name and fields here...

 public function youAwesomeMethod(){
    $query = Tag::select([
        'tags.id',
        'tags.name',
        $this->databaseManager->raw("count(tags.id) AS count_tags")
    ])->whereNotNull('tags.question_id')
      ->orderBy('tags.name', 'ASC')
      ->groupBy('count_tags');
    // Do something else if needed
    return $query
}

This should do the trick. Also you will access to count_tags as a normal Eloquent attribute tag->count_tags