Untitled

$query->leftJoin('iks', 'iks.id', '=', 'ik_finishings.ik_id');
            $query->leftJoin('locations', 'locations.id', '=', 'ik_finishings.location_id');
            $query->leftJoin('ik_finishing_groups', 'ik_finishings.id', '=', 'ik_finishing_groups.ik_finishing_id');
            $query->leftJoin('notes', 'notes.id', '=', 'ik_finishing_groups.note_id');
            $query->leftJoin('bom_articles', 'bom_articles.id', '=', 'iks.bom_article_id');
            $query->leftJoin('items as finished_article', 'finished_article.id', '=', 'bom_articles.finished_article_id');
            $query->leftJoin('category_sizes', 'category_sizes.id', '=', 'iks.category_size_id');
            $query->leftJoin('items as sole_article', 'sole_article.id', '=', 'iks.sole_article_id');
            $query->leftJoin('items as upper_article', 'upper_article.id', '=', 'iks.upper_article_id');
            $query->leftJoin('users as created_user', 'created_user.id', '=', 'ik_finishings.created_by');
            $query->leftJoin('users as updated_user', 'updated_user.id', '=', 'ik_finishings.updated_by');
            $query->leftJoin('users as deleted_user', 'deleted_user.id', '=', 'ik_finishings.deleted_by');
            $query->leftJoin('users as approved_user', 'approved_user.id', '=', 'ik_finishings.approved_by');
            $query->leftJoin('ik_print_slipper_details', 'ik_print_slipper_details.ik_id', '=', 'iks.id');
            $query->leftJoin('ik_print_upper_details', 'ik_print_upper_details.ik_id', '=', 'iks.id');
            $query->leftJoin('ik_transactions', 'ik_transactions.ik_id', '=', 'iks.id');
            $query->leftJoin(DB::raw("(SELECT ik_id, STRING_AGG(DISTINCT transaction_code, ', ') AS sales_order_code
                        FROM ik_transactions
                        WHERE table_name = 'sales_orders'
                        GROUP BY ik_id) as sales_orders"), 'sales_orders.ik_id', '=', 'iks.id');
            $query->leftJoin(DB::raw("(SELECT ik_id, STRING_AGG(DISTINCT transaction_code, ', ') AS marketing_order_code
                        FROM ik_transactions
                        WHERE table_name = 'marketing_orders'
                        GROUP BY ik_id) as marketing_orders"), 'marketing_orders.ik_id', '=', 'iks.id');
            $query->select(
                'ik_finishings.*',
                'iks.code as ik_code',
                'locations.name as location_name',
                'bom_articles.code as bom_article_code',
                'bom_articles.id as bom_article_id',
                'finished_article.id as item_id',
                'finished_article.code as finished_article_code',
                'finished_article.conversion2 as conversion2',
                'finished_article.conversion3 as conversion3',
                'category_sizes.name as category_size_name',
                'category_sizes.code as category_size_code',
                'sole_article.code as sole_article_code',
                'upper_article.code as upper_article_code',
                DB::raw("STRING_AGG(DISTINCT notes.notes, ',') as group_name"),
                DB::raw("STRING_AGG(DISTINCT notes.id::text, ',') as group_id"),
                DB::raw("STRING_AGG(DISTINCT ik_print_slipper_details.size, '|') as ik_print_slipper_detail_size"),
                DB::raw("STRING_AGG(DISTINCT ik_print_upper_details.size, '|') as ik_print_upper_detail_size"),
                DB::raw("STRING_AGG(DISTINCT ik_print_slipper_details.size, '|') as ik_take_slipper_stock_detail_size"),
                DB::raw("STRING_AGG(DISTINCT ik_print_upper_details.size, '|') as ik_take_upper_stock_detail_size"),
                'sales_orders.sales_order_code',
                'marketing_orders.marketing_order_code',
                'created_user.name AS created_by',
                'updated_user.name AS updated_by',
                'deleted_user.name AS deleted_by',
                DB::raw("CASE
                WHEN ik_finishings.is_approve = TRUE THEN approved_user.name
                ELSE ''
                END AS receiver")
            );
            $query->groupBy(
                'ik_finishings.id',
                'iks.code',
                'locations.name',
                'bom_articles.code',
                'bom_articles.id',
                'finished_article.id',
                'finished_article.code',
                'finished_article.conversion2',
                'finished_article.conversion3',
                'sole_article.code',
                'upper_article.code',
                'category_sizes.name',
                'category_sizes.code',
                'sales_orders.sales_order_code',
                'marketing_orders.marketing_order_code',
                'created_user.name',
                'updated_user.name',
                'deleted_user.name',
                'approved_user.name'
            );