Laravel relationship with whereColumn condition

Question

Appears I'm trying to do something uncommonly complex or going about this the wrong way. I can't seem to find anything online similar to what I am doing here. I am trying to query a relationship where a column in the related table is equal to a column in the table of the model.

Given the following tables

| entity
============
id
...

| foo
============
id
entity_id
type_id
...

| baz
============
id
entity_id
type_id
...

| type
============
id
...

and Models

class Entity extends Model 
{
    public function baz()
    {
        $this->hasMany( Baz::class );
    }


    public function foo()
    {
        $this->hasMany( Foo::class );
    }
    //...
}


class Foo extends Model 
{

    public function entity()
    {
        $this->belongsTo( Entity::class );
    }

    public function type()
    {
        $this->belongsTo( Type::class );
    }
    
    //...
}


class Baz extends Model {
    
    public function entity()
    {
        $this->belongsTo( Entity::class );
    }


    public function type()
    {
        $this->belongsTo( Type::class );
    }
    
    //...
}


class Type extends Model 
{
    //...
}

I have the following code working, but in a different context Both Foo and Baz are filtered using $type_id

public function controllerActionWorking( Request $request, $type_id )
{

    // I'm using this in another place to get rows of Foo with type_id == $type_id.  Including Related Baz which also have type_id == $type_id
    $results = Foo::with( 
        [ 
            'entity.baz' => 
            function( $query ) use ($type )
            {
                return $query->where( 'type_id', $type->id ))
            }
        ])
        ->where( 'type_id', $type_id )
        ->get()
        
       // ....
}

What I need is all Foo records, The Entity that goes with Foo, and Entities related Baz records where the Baz.type_id is equal to the type_id of the Foo record. this is non working code, but something like this is what I started trying first.

public function controllerActionHelpWanted( Request $request )
{

    $results = Foo::with( 
        [
            'entity.baz' =>
            function( $query )
            {
                
                // Here I need to verify the entity.baz.type_id = foo.type_id                
                
                // the following does NOT work
                // I get errros that 'foo'.'type_id' does not exist
                return $query->whereColumn( 'foo.type_id', 'baz.type_id' ) 
            })
        ])
        ->get()

    // .....

}

I can use a join() something like this

$results = Foo::with( 'entity' )
    ->join( 
        'baz',
        function($join)
        {
            return $join->on( 'foo.entity_id', 'baz.entity_id' )
                ->on( 'foo.type_id', 'baz.type_id' );
        })
    ->get()

That returns an instance of Foo with combined attributes of both tables. It also looses the 'entity.baz' nested relationship. which will matter in downstream code.

I could $results->map() to filter the 'entity.baz' relationship after the query. I'd prefer to avoid the impact on performance if possible.

just seems like I'm missing something here. Any help/advice is appreciated.

Answer 1

Hard to read, when names are foo/baz and no logic reference is applied. Looks like many to many relation, where you can apply pivot.

public function products()
{
    return $this->belongsToMany(Product::class)
        ->withPivot('quantity', 'price'); // joins data from intermediate table
}

Pivot tables are queried by: $obj->pivot->quantity;

Answer 2

As far you wrap your question into faz/baz/entity it becomes quiet abstract to me, still :)

Let's try to make your query more model-side:

class Foo extends Model
{
    use HasFactory;

    public function entity()
    {
        return $this->belongsTo(Entity::class, 'entity_id', 'id');
    }

    public function scopeTypes($query, $type_id)
    {
        return $query->where('type_id', $type_id);
    }

    public function scopeWithBazes($query, $type_id)
    {
        /* @var $query Builder */
        return $query->join('bazs', function($type_id) {
            return self::where('type_id', $type_id);
        });
    }
}

Inside controller I can do:

class TestController extends Controller
{
    public function index(Request $request)
    {
        $foos = Foo::query()->withBazes(1)->get();

        foreach($foos as $foo)
            dd($foo->baz_name); // i placed baz_name in Baz table for test
    }
}

Well, drawback is that the table is joined. It is joined because you don't have any intermediate tables, so you need use joins etc.

Foo::types($type) will return collection of foos with type given. Foo::bazTypes($type) will return joined table.

Still, these kind of problems appear when you omit intermediate tables, queries become more complex but database is a bit less robust.

If you were able to make that naming abstraction into 'real' problem, maybe people would play with it more. Just make foo => invoice, baz => address, etc, so perhaps you'll find better database schema for your scenario. Sometimes you will end up with multiple queries no matter what, but it's always worth asking :)