CODEWITHSUNDEEP
phplaravel
Shah Rukh
Shah Rukh

Reputation: 300

Select statement with group_concat not working when other columns are called in laravel

Hello my laravel code is

$productDetails = DB::table('products')
        ->select(DB::raw('products.name, GROUP_CONCAT(sizes.name) as sizesName'))
        ->join('subcategories', 'products.subcategories_id', '=', 'subcategories.id')
        ->join('size_categories', 'subcategories.categories_id', '=', 'size_categories.categories_id')
        ->join('sizes',function($join){
            $join->on(DB::raw("FIND_IN_SET(sizes.id, size_categories.size_id)"),">",DB::raw("'0'"));
         })
        ->where('products.id', $request->id)
        ->get();

This doesnt work, when i useproducts.name or any other column name in select statement but when i use only group_concat inside Db::raw and nothing else, the query works. So how do i fetch other columns? Please help. I am stuck on it for quite a while The query i want is

select GROUP_CONCAT(sizes.name),`products`.`name`, `products`.`image`, `products`.`id`, `products`.`image_second`, `products`.`description`, `products`.`min_order`, `size_categories`.`size_id` from `products` 
inner join `subcategories` on `products`.`subcategories_id` = `subcategories`.`id`
 inner join `size_categories` on `subcategories`.`categories_id` = `size_categories`.`categories_id`
 join sizes on (FIND_IN_SET(sizes.id,size_categories.size_id)>0) where `products`.`id` = '7'

Please note that the above query is working fine. I just cant make it in laravel to work. Only the group_concat part.

This is the screenshot from my database, when i dont use group_concat enter image description here

Also the DISTINCT part is doing nothing there, please ignore it. I was just trying that out

This is the migration of create_products_table

public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('units_id');
            $table->unsignedBigInteger('selections_id');
            $table->unsignedBigInteger('subcategories_id');
            $table->string('name');
            $table->string('image');
            $table->text('description');
            $table->string('min_order');
            $table->timestamps();

            $table->index('units_id');
            $table->index('selections_id');
            $table->index('subcategories_id');
        });
    }

migration of create_subcategories_table

public function up()
    {
        Schema::create('subcategories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->unsignedBigInteger('categories_id');
            $table->timestamps();

            $table->index('categories_id');
        });
    }

data of size_categories table

public function up()
    {
        Schema::create('size_categories', function (Blueprint $table) {
            $table->id();
            $table->string('size_id');
            $table->unsignedBigInteger('categories_id');
            $table->timestamps();

            $table->index('categories_id');
        });
    }

migration of categories table

public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

migration of sizes table

public function up()
    {
        Schema::create('sizes', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

In sizes table data is in this form

id   name   
1    2m   
2    3m
3    4m

Upvotes: 2

Views: 628

Answers (1)

user3532758
user3532758

Reputation: 2271

First, you need to specify select columns separately. Like so:

->select(DB::raw('products.name'), DB::raw('GROUP_CONCAT(sizes.name) as sizesName'))

Next, since group concat is an aggregate column, you need to group the sizes, and product name since it's in the select list and it is not related to size.

->groupBy('size_categories.size_id', 'products.id') //edit after your comment. group by prodcuts.id to be able to select columns from products table.

So your final query should look like this:

$productDetails = DB::table('products')
        ->select(DB::raw('products.name'), DB::raw('GROUP_CONCAT(sizes.name) as sizesName'))
        ->join('subcategories', 'products.subcategories_id', '=', 'subcategories.id')
        ->join('size_categories', 'subcategories.categories_id', '=', 'size_categories.categories_id')
        ->join('sizes',function($join){
            $join->on(DB::raw("FIND_IN_SET(sizes.id, size_categories.size_id)"),">",DB::raw("'0'"));
         })
        ->where('products.id', 7)
        ->groupBy('size_categories.size_id', 'products.id')
        ->get();

Upvotes: 1

Related Questions