mysql存储过程laravel,获取存储过程Laravel的结果

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);

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);

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 ;

Note:

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

解决方案

You should pass CALL your_procedure via DB::raw to 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)"));

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 :))