vendredi 26 janvier 2018

Convert MySQL query to Laravel (5.5) Query Builder

I have a problem tying to convert the following MySQL query to the Laravel (5.5) Eloquent query builder.

$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'";`

I have the following Models:

// CDR Model

<?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');
    }
}

// Queue Model

<?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');
    }
}

So far I have the following code in my 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();

I’m stuck at the point of the sub select, is there a way to do this with Laravel’s Query Builder?

Thanks!



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire