How to can I multiple column same id group by ? - Laravel

Question

My database as below:

id |group_id |property_id |type     |
1  |   9     |     13     |property |
2  |   9     |     14     |property |
3  |   9     |     15     |property | 
4  |   9     |     14     |variant  |
5  |   8     |     14     |property |
6  |   8     |     15     |variant  |
7  |   8     |     13     |property |

My code as below:

 $stock_get_property = StockPropertyTemplate::groupBy('group_id')->selectRaw('count(type) as quantity , group_id')->where('type' , 'property')->get();
        $stock_get_variant = StockPropertyTemplate::groupBy('group_id')->selectRaw('count(type) as quantity , group_id')->where('type' , 'variant')->get();

I want the total grouping them. groupBy('group_id') by type="property" and "variant". How can I combine two queries?

the array output I want to create as below;

id |group_id |count_property|count_variant|
1  |   9     |       3      |     1       |
2  |   8     |       2      |     1       |

How can I do it with Laravel? Please help me :(

Slava Rozhnev · Accepted Answer · 2020-12-09 16:05:11Z

1

This problem can be solved by single query:

SELECT
    group_id,
    COUNT(NULLIF(type, 'variant')) as count_property,
    COUNT(NULLIF(type, 'property')) as count_variant
FROM StockPropertyTemplate
GROUP BY group_id;

or in Laravel style:

$stock_get_property_and_variant = 
    StockPropertyTemplate::groupBy('group_id')
        ->selectRaw(
            "group_id,
             COUNT(NULLIF(type, 'variant')) as count_property,
             COUNT(NULLIF(type, 'property')) as count_variant"
        )
        ->orderBy('id', 'asc')
        ->get();

SQL live code here

edited Dec 9, 2020 at 16:05

answered Dec 9, 2020 at 15:59

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ufuk · Accepted Answer · 2018-01-29 09:15:32Z

My solution as below;

    $stock_get_property = StockPropertyTemplate::groupBy('group_id')->selectRaw('count(type) as total_property , group_id ')->where('type' , 'property')->orderBy('id', 'asc')->get();
    $stock_get_variant = StockPropertyTemplate::groupBy('group_id')->selectRaw('count(type) as total_variant , group_id')->where('type' , 'variant')->orderBy('id', 'asc')->get();

   //assigning $variant array -> total_variant
   $variant = [];
   foreach ($stock_get_variant as  $row) {
       $variant[] = $row->total_variant;
   }

   //array push made
   $i = 0;
   $all_values = [];
   foreach ($stock_get_property as $row) {
       $all_values[] = array('group_id' => $row->group_id , 'total_property' => $row->total_property , 'total_variant' => $variant[$i]);
       $i++;
   }

This code is taking care of my work.

Collectives™ on Stack Overflow

How to can I multiple column same id group by ? - Laravel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related