Relationships with onCondition

Looking for a better way to do this I must be missing something as we're on Laravel 11 now and I expect more (I've been looking for this since Laravel 5). Example I've 2 models Company and User which have a foreign key on company.id and user.company_id

If I want to find the administrator/primary user it's the only entry where the company.email and the user.email match. The best suggestion I can find is do do the following

public function getAdminUser(): ?\Illuminate\Database\Eloquent\Relations\HasOne
    {
        return $this->hasOne(User::class, 'company_id', 'id', )->whereRaw('company.email = user.email');
    }

Is there a better way that exists to do this than a whereRaw(...) ? I get that we can use closures for regular joins to add onConditions but it would be a much better experience to have this built into the framework particularly for relations. Composite keys have been really lacking but this is the only instance where it negatively affects me. Any better suggestions to leverage the framework in a reliable way would be very appreciated.

LaryAI

1 year ago

Best Answer

Level 58

To achieve the desired relationship without using whereRaw, you can use a combination of Eloquent relationships and query scopes to make the code cleaner and more maintainable. However, as of Laravel 11, there isn't a built-in way to directly add conditions to relationships in a more elegant way than using whereRaw for this specific case.

Here's a refined approach using a query scope to encapsulate the condition logic:

Define a scope in the User model:

// In User.php model
public function scopeAdminUser($query, $companyEmail)
{
    return $query->where('email', $companyEmail);
}

Use this scope in the Company model to define the relationship:

// In Company.php model
public function adminUser()
{
    return $this->hasOne(User::class, 'company_id', 'id')
                ->adminUser($this->email);
}

This way, you encapsulate the condition logic within the User model and make the relationship definition in the Company model cleaner.

Here is the complete code for both models:

User.php:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    // Other model methods and properties

    public function scopeAdminUser($query, $companyEmail)
    {
        return $query->where('email', $companyEmail);
    }
}

Company.php:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Company extends Model
{
    // Other model methods and properties

    public function adminUser()
    {
        return $this->hasOne(User::class, 'company_id', 'id')
                    ->adminUser($this->email);
    }
}

With this approach, you avoid using whereRaw directly in the relationship definition, making the code more readable and maintainable. The scopeAdminUser method in the User model encapsulates the condition logic, and the adminUser method in the Company model uses this scope to define the relationship.

Please or to participate in this conversation.