Laravel select from derived table

Question

is there a way to translate this query into Laravel eloquent format?

SELECT TEMP.A, COUNT(*) AS Total FROM 
      (SELECT A FROM TABLE WHERE B='something' GROUP BY C) 
AS TEMP GROUP BY TEMP.A

What I am trying to do here is get the count of A based on the distinct record of C that falls in certain where clause.

The subselect statement is also and eloquent object that is built up by user input on a filter to get the distinct record of C out. I tried to do this, assuming the subselect eloquent object is $query

$query2->DB::connection()->table(function($_query) use ($query){
  $_query = $query;
  $_query->addSelect('A')->where('B','=','something')->groupBy('C')->get();
})->addSelect('A')->addSelect(DB::raw('COUNT(*) as Total'))->groupBy('A')->get(); 

But it doesn't seem to be working, someone suggested used DB::raw in ->table(), but the problem is the subselect statement is actually a dynamic build up eloquent statement also.

Thank you very much for the help in advance

Answer 1

Subqueries do not play nicely with Eloquent. You're just giving yourself a headache daring to try that. I would recommend using DB:select(), and then adding hydrate() to your model, and using that to convert it to Eloquent. This is how I handle complex queries:

File containing query:

<?php
    $query = "SELECT TEMP.A, COUNT(*) AS Total FROM (SELECT A FROM TABLE WHERE B='?' GROUP BY C) AS TEMP GROUP BY TEMP.A";
    $thing_query = DB::select($query, ['something']);
    $thing = Thing::hydrate($thing_query);
    // hydrate converted it into Eloquent so we can use things like first()
    return $thing->first()->total;

models/Thing.php

<?php
class Thing extends Eloquent {
    /**
     * Hydrate method
     * 
     * @param array $data
     * @return Illuminate\Database\Eloquent\Collection
     */
    static public function hydrate(array $data, $connection = NULL)
    {
        // get calling class so we can hydrate using that type
        $klass = get_called_class();

        // psuedo hydrate
        $collection = new Illuminate\Database\Eloquent\Collection();
        foreach ($data as $raw_obj)
        {
            $model = new $klass;
            $model = $model->newFromBuilder($raw_obj);
            if (!is_null($connection))
                $model = $model->setConnection($connection);
            $collection->add($model);
        }
        return $collection;

    }
}