Convert MySQL query to Laravel (5.5) Query Builder

Question

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!

Do you want to use DB:raw() method? Please check Raw Expressions here. laravel.com/docs/5.5/queries#raw-expressions The first example might help you. — Kristiyan
– Kristiyan, Commented Jan 26, 2018 at 17:03
@Kristiyan Thanks for your reply. I don't mind using a DB::raw() method. Still I don't really understand how that will work. I've been to the Laravel docs and know of DB::Raw(), but it will still be a select on a relational table. I've 'solved it' on an other query with a join(), but the the CDR table can become quite large and I don't want to eat all the memory. — dJ-j79
– dJ-j79, Commented Jan 26, 2018 at 20:16

adil · Accepted Answer · 2019-01-31 12:56:38Z

1

Consider this code:

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

This part of the query:

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

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

            })

is used to achieve the below part of the query:

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

The above query results can be achieved by the following query as well through 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();

For more details you can visit:

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

edited Jan 31, 2019 at 12:56

answered Jan 30, 2019 at 13:38

adil

431 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nico Haase Over a year ago

Why? Can you add some explanation to that code that shows why it could solve the problem?

adil Over a year ago

->where('c.con_duur','>=',function($query){ $query->select('kortbel_seconden') ->from('queue AS q') ->where('q.queue_id', '=', 'c.queue_id'); }) This part of the query is equivalent to c.con_duur >= (SELECT kortbel_seconden FROM queue AS q WHERE q.queue_id = c.queue_id) Further more you can visit official documentation at laravel.com/docs/5.5/queries#where-clauses

Nico Haase Over a year ago

Please add such explanation to the answer itself, not to the comment section

Collectives™ on Stack Overflow

Convert MySQL query to Laravel (5.5) Query Builder

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related