php - Laravel 4 准备好的语句

Question

我想知道如何在 laravel 4 中放入准备好的语句。

这是我在 MySQL Workbench 中编写的准备好的语句:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`payroll_item_id` = ', `payroll_item_id`, ', amount, NULL)) i', `payroll_item_id`)
  ) INTO @sql
FROM payroll_item_settings;

SET @sql = CONCAT('SELECT  employee_id, ', @sql, ' FROM payroll_item_settings GROUP BY employee_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

我找到了一个解决方法，我使用了这样的存储过程:

DELIMITER $$

CREATE PROCEDURE employees_payroll_settings()

BEGIN

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`payroll_item_id` = ', `payroll_item_id`, ', amount, NULL)) AS `', payroll_items.payroll_item, '`')
  ) INTO @sql
FROM payroll_item_settings 
JOIN payroll_items 
ON payroll_items.id = payroll_item_settings.payroll_item_id;

SET @sql = CONCAT('SELECT  employees.employee_name, ', @sql, ' FROM payroll_item_settings JOIN employees ON employees.id = payroll_item_settings.employee_id GROUP BY employee_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

然后在我的 Controller 中，我放了这样的东西:

$payroll_items = DB::table('payroll_items')
                ->distinct()
                ->join('payroll_item_settings', 'payroll_items.id', '=', 'payroll_item_settings.payroll_item_id')
                ->select('payroll_items.*', 'payroll_item_settings.payroll_item_id')
                ->get();
        $payroll_item_settings = DB::select('CALL employees_payroll_settings()');
        return View::make('payroll_item_setting.index', array(
                    'payroll_item_settings' => $payroll_item_settings,
                    'payroll_items' => $payroll_items
        ));