Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

grozavule's avatar

Create a query joining three Eloquent models

I have the following SQL query that I need to replicate using Eloquent:

SELECT SUM(COL.ORDER_QTY) as UNITS_SOLD, SR.NAME
FROM CUSTOMER_ORDER CO JOIN SALES_REP SR ON CO.SALESREP_ID = SR.ID
JOIN CUST_ORDER_LINE COL ON CO.ID = COL.CUST_ORDER_ID
WHERE COL.PART_ID LIKE 'MODEL%' AND CO.ORDER_DATE > '2018-05-01'
GROUP BY SR.NAME
ORDER BY UNITS_SOLD DESC

I created three Eloquent models for CUSTOMER_ORDER, SALES_REP, and CUST_ORDER_LINE.

CUSTOMER_ORDER

<?php

namespace App\Visual;

use Illuminate\Database\Eloquent\Model;

class CustomerOrder extends Model
{
    protected $connection = 'vmfg';
    protected $table = 'CUSTOMER_ORDER';
    protected $primaryKey = 'ID';
    protected $keyType = 'string';

    public function lineItems()
    {
        return $this->hasMany('App\Visual\CustomerOrderLine', 'CUST_ORDER_ID', 'ID');
    }

    public function salesRep()
    {
        return $this->belongsTo('App\Visual\SalesRep', 'SALESREP_ID');
    }
}

SALES_REP

<?php

namespace App\Visual;

use Illuminate\Database\Eloquent\Model;

class SalesRep extends Model
{
    protected $connection = 'vmfg';
    protected $table = 'SALES_REP';
    protected $primaryKey = 'ID';

    public function orders()
    {
        return $this->hasMany('App\Visual\CustomerOrder', 'SALESREP_ID');
    }
}

CUST_ORDER_LINE

<?php

namespace App\Visual;

use Illuminate\Database\Eloquent\Model;

class CustomerOrderLine extends Model
{
    protected $connection = 'vmfg';
    protected $table = 'CUST_ORDER_LINE';
    protected $primaryKey = 'ROWID';

    public function order()
    {
        return $this->belongsTo('App\Visual\CustomerOrder', 'CUST_ORDER_ID');
    }
}

How can I leverage these models to create the equivalent of the SQL query above?

0 likes
4 replies
jlrdw's avatar

An eloquent query is written like a query, just example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Whereas an eloquent relation is like a parent to children, (one to many), separate queries.

grozavule's avatar

If I understand what you're saying, I could use any of the three models to formulate my query. I just can't use all three together.

Dhakalsandeep's avatar

You can use query builder to get the desired result

DB::table('CUSTOMER_ORDER as CO')
->join('SALES_REP as SR','CO.SALESREP_ID', 'SR.ID')
->join('CUST_ORDER_LINE as COL', 'CO.ID', 'COL.CUST_ORDER_ID')
->where([
  ['COL.PART_ID', 'LIKE', 'MODEL%'],
  ['CO.ORDER_DATE', '>', '2018-05-01']
])
->selectRaw('SUM(COL.ORDER_QTY) as UNITS_SOLD, SR.NAME')
->groupBy('SR.NAME')
->orderByRaw('UNITS_SOLD desc')
->get();
jlrdw's avatar

If I understand what you're saying, I could use any of the three models to formulate my query. I just can't use all three together.

No, I am not saying that. You can join 20 tables. I am saying an eloquent query with joins is not the same as eloquent relations.

A relation uses separate queries in the background. @dhakalsandeep example is using query builder. So you can use that or the same in eloquent. Your choice.

Eloquent also has all of query builder methods. https://laravel.com/docs/8.x/eloquent#building-queries

Please or to participate in this conversation.