php - 将 MySQL 查询转换为 Laravel (5.5) 查询生成器

Question

我在将以下 MySQL 查询转换为 Laravel (5.5) Eloquent 查询生成器时遇到问题。

$start = '2018-01-22'; // Some random starting point for the Query

$query = "SELECT * FROM cdr c WHERE soort=3 AND con_duur > 0 AND con_duur
>= (select kortbel_seconden from queue q where q.queue_id=c.queue_id) AND `start_tijd >= '$start'";`

我有以下型号:

//CDR模型

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class CDR extends Model
{
    protected $table = 'cdr';
    protected $primaryKey = 'cdr_id';

    public function Queue()
    {
        return $this->hasOne('App\Models\Queue', 'queue_id', 'queue_id');
    }
}

//队列模型

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class Queue extends Model
{

    protected $table = 'queue';

    protected $primaryKey = 'queue_id';

    public function cdr()
    {
        return $this->belongsTo('App\Models\CDR', 'queue_id', 'queue_id');
    }
}

到目前为止，我的 Controller 中有以下代码:

App\Models\CDR::with('queue')
    ->where('soort', '3')
    ->where('con_duur', '>', '0')
    ->where('start_tijd', '>=' , $start)
    ->where('con_duur', '>=', ' ') // this is where the sub select from the `queue` table should be : (select kortbel_seconden from queue q where q.queue_id=c.queue_id)
    ->get();

我被困在子选择点，有没有办法用 Laravel 的查询生成器来做到这一点？

谢谢!

Answer 1

考虑这段代码:

        DB::table('cdr AS c')
            ->select("*")
            ->where('c.soort','=',3)
            ->where('c.con_duur','>',0)
            ->where('c.con_duur','>=',function($query){

                $query->select('kortbel_seconden')
                    ->from('queue AS q')
                    ->where('q.queue_id', '=', 'c.queue_id');

            })
            ->where("c.start_tijd",">=",$start)
            ->get();

这部分查询:

->where('c.con_duur','>=',function($query){

                $query->select('kortbel_seconden')
                    ->from('queue AS q')
                    ->where('q.queue_id', '=', 'c.queue_id');

            })

用于实现以下部分查询:

`c`.`con_duur` >= 
  (SELECT 
    kortbel_seconden 
  FROM
    queue q 
  WHERE q.queue_id = c.queue_id)

上面的查询结果也可以通过下面的查询得到，也可以通过join得到:

 DB::table('cdr AS c')
            ->select("c.*")
            ->join('queue AS q', 'q.queue_id', '=', 'c.queue_id')
            ->where('c.soort','=',3)
            ->where('c.con_duur','>',0)
            ->where('c.con_duur','>=','q.kortbel_seconden')
            ->where("c.start_tijd",">=",$start)
            ->get();

更多详情您可以访问:

https://laravel.com/docs/5.5/queries#where-clauses