mysql - 将原始 MySQL 转换为 Eloquent

Question

有人可以帮我将此 SQL 查询转换为 Eloquent 吗？

SELECT SUM(amount) as amount
FROM recurrings
WHERE space_id = 1 
    AND YEAR(starts_on) <= 2019 
    AND MONTH(starts_on) <= 08
    AND ((YEAR(ends_on) >= 2019 AND MONTH(ends_on) >= 08) OR ends_on IS NULL)

提前致谢

标签： mysqllaraveleloquent

Answer 1

假设表的模型recurrings被称为Recurring：

# App\Recurring.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Recurring extends Model
{
    // This table name follows the Laravel naming convention so this line isn't necessary.
    // This is just to be explicit. Or if you want to give the model a different name.
    // https://laravel.com/docs/5.8/eloquent#defining-models
    protected $table = 'recurrings';
    // Since you're using dates, it's best to mutate them into Carbon objects.
    // It will make dealing with them easier when using php.
    // https://laravel.com/docs/5.8/eloquent-mutators#date-mutators
    protected $dates = ['starts_on', 'ends_on'];
}

$recurrings = Recurring::selectRaw('SUM(amount)')      // SELECT SUM(amount) FROM recurrings
    ->where('space_id', 1)                             // WHERE space_id = 1
    ->whereYear('starts_on', '<=', '2019')             // AND YEAR(starts_on) <= 2019
    ->whereMonth('starts_on', '<=', '08')              // AND MONTH(starts_on) <= 08
    ->where(function($q1) {                            // AND (
        $q1->where(function($q2) {                     //       (
               $q2->whereYear('ends_on', '>=', '2019') //         YEAR(ends_on) >= 2019
                  ->whereMonth('ends_on', '>=', '08'); //         AND MONTH(ends_on) >= 08
           })                                          //       )
           ->orWhereNull('ends_on');                   //       OR ends_on IS NULL
    })->get();                                         // )

如果您没有模型，请使用DB::table('recurrings')->...而不是Recurring::...

https://laravel.com/docs/5.8/queries

使用查询生成器的connection和toSql()方法，我能够测试此查询是否返回了您在控制台中要求的内容。

$ php artisan tinker
Psy Shell v0.9.9 (PHP 7.3.4 — cli) by Justin Hileman

\DB::connection('mysql')->table('recurrings')->selectRaw('SUM(amount)')->where('space_id', 1)->whereYear('starts_on', '<=', '2019')->whereMonth('starts_on', '<=', '08')->where(fun
ction($q1) { $q1->where(function($q2) { $q2->whereYear('ends_on', '>=', '2019')->whereMonth('ends_on', '>=', '08'); })->orWhereNull('ends_on'); })->toSql();
=> "select SUM(amount) from `recurrings` where `space_id` = ? and year(`starts_on`) <= ? and month(`starts_on`) <= ? and ((year(`ends_on`) >= ? and month(`ends_on`) >= ?) or `ends_on`
 is null)"