Laravel (Lumen) Eloquent querying with WHERE on a relation (Multi-level)

Question

I have the following DB tables:

Purchase
-id
-workplace_id

Workplace
-id
-client_id

(and obviously some more fields, but for the example these are all the needed ones). I would like to make a query like this:

SELECT * FROM
  purchase
  INNER JOIN workplace ON (purchase.workplace_id = workplace.id)
WHERE
  (workplace.client_id = 1)

I'm trying to make this work with the Eloquent models, but I can't figure out how to filter on a joined table. I tried:

    $purchases = Purchase::query()
        -> workplace()
        -> where('client_id', '=', Auth::user() -> client_id)
        -> get();

But apparently workplace() is undefined for some reason.

My Purchase.php model file looks like this:

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Purchase extends Model
{
    public function workplace(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(Workplace::class);
    }
}

Any pointers on how to make this simple select work?

Thanks!

====EDIT=====

I found a possible solution:

    $purchases = Purchase::with('workplace')
        -> whereHas('workplace', function($q) {
            return $q -> where('client_id', '=', Auth::user() -> client_id);
        })
        -> get();

But this generates an SQL that seems more complicated and is probably also slower:

select * from `purchases` where exists (select * from `workplaces` where `purchases`.`workplace_id` =
`workplaces`.`id` and `client_id` = ? and `workplaces`.`deleted_at` is null)

So I'm still looking for better alternatives

Heya, don't edit your answer into your question. If you find an answer, post it as an answer :) — Joundill, Feb 10 '22 at 21:17
Layers between the user and MySQL are supposed to help. Instead, they require the user to learn two different ways to write queries. Alas. — Rick James, Feb 12 '22 at 01:56
@RickJames Yep, sometimes I feel like it is easier to just stick with writing plain old SQL queries. — Lippai Zoltan, Feb 12 '22 at 10:25

score 2 · Accepted Answer · answered Feb 10 '22 at 20:50

If you want to do a join, you need to build it with the join method, you can't use a relationship. See here for the docs:

https://laravel.com/docs/9.x/queries#joins

So you should be able to do something like this:

$purchases = Purchase::query()
    ->join('workplace', 'purchase.workplace_id', '=', 'workplace.id')
    ->where('workplace.client_id', '=', Auth::user()->client_id)
    ->get();

The generated SQL that you show in your edit is a sub-query, and you may still want to consider that. Sure, sub-queries are often slower than joins, but unless you're dealing with a massive dataset the performance difference might be negligible, and it allows you to use native Eloquent relationships.

See here for a discussion on this: https://stackoverflow.com/a/2577188/660694

Laravel (Lumen) Eloquent querying with WHERE on a relation (Multi-level)

1 Answers1