sql to laravel query buider

Im trying to convert the below sql to a laravel query buider code but i dont know how to go about it. i have tried to use automated tool but none is giving me the correct output.

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM( CASE WHEN business_locations.location_id = "', business_locations.location_id, '" THEN variation_location_details.qty_available ELSE 0 END) AS ', business_locations.location_id) )

INTO @sql
FROM business_locations;  

SET @sql = CONCAT('SELECT products.name, ', @sql, 
  ' FROM business_locations LEFT JOIN product_locations on product_locations.location_id = business_locations.id JOIN products on products.id = product_locations.product_id left Join categories as c1 on products.category_id = c1.id left Join categories as c2 on products.sub_category_id = c2.id LEFT join variations as variations on variations.product_id = products.id left Join variation_location_details on variation_location_details.variation_id = variations.id GROUP BY products.id');
SELECT @sql;
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

rodrigo.pedra

3 years ago

Best Answer

Level 56

// add this to top of the file
use Illuminate\Support\Facades\DB;

$locationIds = DB::table('business_locations')->distinct()->pluck('location_id');

$builder = DB::table('business_locations')
    ->leftJoin('product_locations', 'business_locations.id', '=', 'product_locations.location_id')
    ->join('products', 'product_locations.product_id', '=', 'products.id')
    ->leftJoin('categories as c1', 'products.category_id', '=', 'c1.id')
    ->leftJoin('categories as c2', 'products.sub_category_id', '=', 'c2.id')
    ->leftJoin('variations', 'products.id', '=', 'variations.product_id')
    ->leftJoin('variation_location_details', 'variations.id', '=', 'variation_location_details.variation_id')
    ->select('products.name')
    ->groupBy('products.id');

foreach ($locationIds as $locationId) {
    $builder->selectRaw(
        "SUM(CASE WHEN business_locations.location_id = ? THEN variation_location_details.qty_available ELSE 0 END) AS `{$locationId}`",
        [$locationId],
    );
}

$records = $builder->get();

robertkimaru1998

3 years ago

Level 1

@rodrigo.pedra Thanks a lot. It works for me

Please or to participate in this conversation.