how to group data after doing a UNION ALL in laravel with eloquent?

David Chavarria 0
Viewed 26

I need to group data from two queries to the same table with two different conditions, for them I use UNIONALL() but it shows me an error.

my code

$query = DB::table($sub)
    ->select('categories.id as categoryID','categories.name As nombre_categoria','type'
    ,DB::raw('TRUNCATE( SUM( (points/'.$valor_poinst_value->puntos_por_dolar.') - ((points/'.$valor_poinst_value->puntos_por_dolar.')*'.$comision.') ),2) as total_sales'))
    ->join('consultancy_requests','session_id','=','consultancy_requests.consultancy_id')
    ->join('advisors','consultancy_requests.advisor_id','=','advisors.id')
    ->join('advisor_assignments','advisors.id','=','advisor_assignments.advisor_id')
    ->join('category_region','advisor_assignments.category_region_id','=','category_region.id')
    ->join('categories','category_region.category_id','categories.id')
    ->where('advisors.parent_id','!=',NULL)
    ->where('categories.parent_id','!=',NULL)
    ->groupBy('categories.id','categories.name','type','points');

    $f_chat = DB::table($query)
    ->select('categoryID','nombre_categoria',DB::raw('SUM(total_sales) as total_chat'))
    ->where('type','chat')
    ->groupBy('categoryID','nombre_categoria');

    $f_call = DB::table($query)
    ->select('categoryID','nombre_categoria',DB::raw('SUM(total_sales) as total_call'))
    ->where('type','call')
    ->groupBy('categoryID','nombre_categoria');

    $algo = $f_call->union($f_chat);
    $so = DB::table($algo)
    ->select('categoryID','nombre_categoria','total_call','total_chat')
    ->groupBy('categoryID','nombre_categoria')
    ->get();

error

Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_chat'
 in 'field list' (SQL: select `categoryID`, `nombre_categoria`, `total_call`, `total_chat` 
from ((select `categoryID`, `nombre_categoria`, SUM(total_sales) as total_call from (select `categories`.`id` as 
`categoryID`, `categories`.`name` as `nombre_categoria`, `type`, 
TRUNCATE( SUM( (points/20) - ((points/20)*0.1) ),2) as total_sales 
from ((select `session_id`, `spent_points` as `points`, `type` from

the result i hope to get

enter image description here

orm eloquent php laravel
1 Answers

I dont know what to answer but, maybe tracing the queries can help you.

  1. try your expected query db first at database console. then execute.
  2. then compare the expected query (number 1) to your query builder, by change ->get() to ->toSql()

CASE WHEN inside SUM could help you to get value by type without union.

$so = DB::table($query)
            ->select(
                'categoryID','nombre_categoria',
                DB::raw("SUM(CASE WHEN type = 'chat' THEN total_sales END) as total_chat"),
                DB::raw("SUM(CASE WHEN type = 'call' THEN total_sales END) as total_call"),
            )
            ->groupBy('categoryID','nombre_categoria')
            ->get();

I got reference from this thread How to get vertical value in horizontal format in MySql

Rindi B0
Related