获取存储过程Laravel的结果 [英] Get Result of Stored Procedure Laravel

问题描述

我正在使用Laravel 5.4和MySql 5.7，PHP 5.6. 我试图调用存储过程(执行SELECT查询，联接多个表)，并将结果返回到我的PHP.

I am using Laravel 5.4 and MySql 5.7, PHP 5.6. I am trying to call a stored procedure (that does a SELECT query, joining multiple tables), and to get the result returned to my PHP.

以下是我尝试失败的内容:

Here are the things I've unsuccessfully tried:

PHP 1:

$result =  DB::select('CALL rentalsAvailables_get(?, ?, ?, ?)',
                array(
                    'p0' => Carbon::now(),
                    'p1' => Carbon::now()->addDays(7),
                    'p2' => 100,
                    'p3' => 2
                )
    );
return var_dump($result);

结果1:

{
   "error": {
        "message": "SQLSTATE[HY000]: General error: 2031  (SQL: CALL rentalsAvailables_get(2017-03-11 16:00:42, 2017-03-18 16:00:42, 100, 2))",
        "code": "HY000",
        "status_code": 500
   }
}

PHP 2:

$result =  DB::select('CALL rentalsAvailables_get(:p0, :p1, :p2, :p3)',
                array(
                    'p0' => Carbon::now(),
                    'p1' => Carbon::now()->addDays(7),
                    'p2' => 100,
                    'p3' => 2
                )
    );
return var_dump($result);

结果2:

array(0) {
}

**存储过程:**

** Stored Procedure: **

    DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `rentalsAvailables_get`(IN `startDate` DATETIME, IN `endDate` DATETIME, IN `maxPrice` INT(11) ZEROFILL, IN `capacity` INT(2))
    NO SQL
SELECT a.rentalId, a.rentalName, a.rentalBathroom, a.rentalBedroom, a.rentalCapacity, a.rentalCover, a.rentalLat, a.rentalLong, a.rentalLocation, a.rentalStatus, a.rentalSummary, a.rentalText, a.rentalOwnerId,
b.rentalTypeId, b.rentalTypeName,
c.id as 'userId', c.name,
d.rentalPriceId, d.rentalPrice

FROM rentals as a
INNER JOIN rentalTypes as b
ON a.rentalTypeId = b.rentalTypeId
INNER JOIN users as c
ON a.rentalOwnerId = c.id
INNER JOIN rentalprice as d
ON a.rentalId = d.rentalId

WHERE a.rentalCapacity > @p3
AND a.rentalStatus = true
AND d.rentalPrice < @p2
AND d.rentalPriceStartDate <= @p0
AND d.rentalPriceEndDate >= @p1
AND a.rentalId NOT IN (
        SELECT eSub.rentalId FROM unavailabilities as eSub WHERE eSub.unavailabilityStartDate >= @p0 AND eSub.unavailabilityEndDate <= @p1
    )
AND a.rentalId NOT IN (
        SELECT fSub.rentalId FROM rent as fSub WHERE fSub.rentStartDate >= @p0 AND fSub.rentEndDate <= @p1
    )$$
DELIMITER ;

注意:

我当然已经检查过了，当我这样调用它时，我从查询中得到结果:

I have of course checked and i am getting results from my query when calling it like that:

SET @p0='2017-03-11 04:26:09.000000';
SET @p1='2017-03-17 04:26:09.000000';
SET @p2='1000';
SET @p3='2';
CALL `rentalsAvailables_get`(@p0, @p1, @p2, @p3);

有人有这个问题吗? 感谢您的阅读

Did anyone already got this issue? Thanks for reading

推荐答案

您应将CALL your_procedure通过DB::raw传递给DB::SELECT.

因此这将起作用并返回结果集:

So this will work and it returns result set:

$p0 = Carbon::now();
$p1 = Carbon::now()->addDays(7);
$p2 = 100;
$p3 = 2;
DB::select(DB::raw("CALL rentalsAvailables_get($p0, $p1, $p2, $p3)"));

您还可以使用hydrate方法将结果集转换为口才模型.

Also you can convert the result set to Eloquent model using hydrate method.

Foo::hydrate($result_from_db_select);

希望六个月后能为您提供帮助:))

Hope this help you after 6 months :))

这篇关于获取存储过程Laravel的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！