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?