Laravel Eloquent: Join two models using just one SQL query (using JOIN)

Question

I have two models. There are just two different tables, and the only common is the order number.

class Order extends Model
{
    protected $primaryKey = 'OrderId';
    protected $keyType = 'string';

    protected $table = 'Order';

    public $timestamps = false;
    public $incrementing = false;

    public function crmOrder()
    {
        return $this->hasOne(CrmOrder::class, 'Order_Num', 'OrderId')
            ->select(['Account', 'OrderType']);
    }
}

class CrmOrder extends Model
{
    protected $primaryKey = 'Order_Num';
    protected $keyType = 'string';

    protected $table = 'CRMORDERS';

    public $timestamps = false;
    public $incrementing = false;
}

When I'm trying to query the following.

Order::with('crmOrder')->select(['OrderId', 'OrderCreatedDt'])->whereDate('OrderCreatedDt', '>', DB::raw('DATEADD(day,-21,GETDATE())'))->get();

I see two different queries. 1st is to model Order and 2nd to CrmOrder with ids from the 1st result. How to make just one query using JOIN between them?

Your example query is perfect for laravel, what are you not achieving or looking for? — mrhn, Apr 26 '22 at 22:16
@kgtu your database is prepared for the disaster. As a user mentioned, you are using PascalCase and snake_case... you should be using snake_case as the documentation states... — matiaslauriti, Apr 27 '22 at 02:37

Michael Mano · Answer 1 · 2022-04-27T23:34:19.287

2

EDIT: corrected to table names.

$orders = Orders::join('CRMORDERS', 'order.OrderId', '=', 'CRMORDERS.Order_Num')->get(['orders.*', 'CRMORDERS.*']);

edited Apr 27 '22 at 23:34

answered Apr 26 '22 at 22:33

Michael Mano

3,339
2
14
35

It would be better to use Models and let the relationships automatically do the right query – matiaslauriti Apr 27 '22 at 00:28
@matiaslauriti yeah I know. no idea why their asking this but they have already done it the right way. But then again they are also mixing camel case with snake case & pascal case. could be to speed up queries since its just one verse the eloquent 2. – Michael Mano Apr 27 '22 at 01:30
There is an error: Illuminate\Database\QueryException with message 'SQLSTATE[42S02]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'crmOrder'. (SQL: select [order].*, [crmOrder].* from [order] inner join [crmOrder] on [order].[OrderId] = [crmOrder].[Order_Num])' – kgtu Apr 27 '22 at 14:20
1

I think join asks table name - not a model – kgtu Apr 27 '22 at 14:24
Looks like it ignores $table from the first model and passes model name... so real sql join available only using table names in builder, i can't use models for join?.. – kgtu Apr 29 '22 at 18:10

Laravel Eloquent: Join two models using just one SQL query (using JOIN)

1 Answers1