Crazylife's avatar

How to select from subquery using eloquent?

I am using laravel 5.3. I want to achieve something like this using eloquent.

select field1, field3, total
from (select field1, field3, sum(field2) as total
      from sales
      group by field1, field2
      having total <> 0
     ) s
group by field1;

How can i do this using eloquent?

0 likes
4 replies
jlrdw's avatar

If it's a good working query use as is. Here are some articles

https://laracasts.com/discuss/channels/guides/getpdo-usage

https://laracasts.com/discuss/channels/eloquent/writing-all-queries-directly-vs-model-relations

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

https://laracasts.com/discuss/channels/laravel/coverting-ms-access-queries-to-laravel-query-builder

https://laracasts.com/discuss/channels/general-discussion/sql-injection-2

But the docs have group by examples.

Crazylife's avatar

This is what i had done

$sub = SalesTable::select(DB::raw('field1,field3,SUM(field2)as total'))         
              ->groupBy('field1',DB::raw('field3 having total<> 0'));   

 $main = DB::table(DB::raw("({$sub->toSql()}) as sub") )
            ->select('field1', 'field3','total');
            $main ->mergeBindings($sub_breakdown->getQuery());
            $main ->groupBy('field1');       
            $main ->get();

And dd($main) return me this

Builder {#2361 ▼
  #connection: MySqlConnection {#2148 ▶}
  #grammar: MySqlGrammar {#2149 ▶}
  #processor: MySqlProcessor {#2150}
  #bindings: array:6 [▶]
  +aggregate: null
  +columns: array:2 [▶]
  +distinct: false
  +from: Expression {#2364 ▶}
  +joins: null
  +wheres: null
  +groups: array:1 [▶]
  +havings: null
  +orders: null
  +limit: null
  +offset: null
  +unions: null
  +unionLimit: null
  +unionOffset: null
  +unionOrders: null
  +lock: null
  #backups: []
  #bindingBackups: []
  #operators: array:29 [▶]
  #useWritePdo: false
}

I am not sure it is working or not, but i cant get my result although there is no error.

jlrdw's avatar

Study and work examples from the QB docs

https://laravel.com/docs/5.6/queries

But my own personal rule is:

If I have to start using raw

I may as well just use PDO with getPdo()

Or use normal DB facade.

Look over and view those links, many examples in there.

staudenmeir's avatar

Did you save the result of $main ->get() into a variable? What's the result of dd($main ->get());?

Please or to participate in this conversation.