Laravel Relationship Using An OR Condition on the joining field

EDIT

To clarify, I am looking for a solution that will work with eager loading and be able to leverage all of the existing features of a Laravel relationship. I am aware I can use accessor attributes and other workarounds, but I cannot figure out how to do this and have it work EXACTLY like any other Laravel relationship.

Is there any way to create a relationship on a Laravel model using an "OR" condition for the field used to join the table while loading an eloquent model?

Example Table: user_connections

If I have a Laravel Model named UserConnection that uses the above table schema, is there any way to define a relationship on that model that can utilize either the from_user_id field OR the to_user_id field.

The goal is to have a relationship named userConnections on a User model that can be tied to either field.

Example of Desired Syntax:

class User {
...

// THE RELATIONSHIP METHOD NAME TO USE (DOESN'T HAVE TO BE `hasMany`)
public function userConnections(){
    return $this->hasMany(
        \App\Models\UserConnection::class, /* THE TARGET CLASS TO BE LOADED */
        'from_user_id||to_user_id',        /* JOIN ON EITHER OF THESE FIELDS */
        'id'                               /* THE ID OF THE USER IN THIS MODEL */
    );
}

I know I can do this with a query and load the models manually. But I want to stick to the Laravel format for relationships so that other people on this project can use the relationship naturally.

Please don't bother responding if your answer is going to be "Don't structure the database that way", because I cannot change the schema. I need to figure out how to make it work with this schema.

My concept isn't set in stone like the schema is, but it should illustrate what I'm trying to achieve. I am entirely open minded to any solution that works with this db schema.

Hoping someone else has run into a similar issue and might have a solution.

Thanks in advance to anyone who has read this far.

Answers

To achieve the desired relationship behavior in Laravel with an "OR" condition for joining tables based on multiple fields, you can use Laravel's custom relationship methods and raw SQL expressions. However, please note that Laravel's built-in relationship methods are not designed to handle such complex conditions out of the box, so you'll need to create a custom solution.

Here's how you can define a custom relationship method in your User model to achieve this:

class User extends Model
{
    // Define a custom relationship method named 'userConnections'
    public function userConnections()
    {
        // Use the 'hasMany' relationship method with a custom query
        return $this->hasMany(UserConnection::class, function ($query) {
            // Use raw SQL expression to create the OR condition
            $query->where('from_user_id', $this->id)
                  ->orWhere('to_user_id', $this->id);
        });
    }
}

In this custom relationship method:

• We're using the hasMany method to define the relationship between the User model and the UserConnection model.
• Within the hasMany method, we're providing a closure where we define the custom query for fetching related UserConnection records.
• Inside the closure, we're using raw SQL expressions to create an OR condition between the from_user_id and to_user_id fields.

This approach allows you to define a relationship named userConnections on the User model that can be tied to either the from_user_id or to_user_id field in the UserConnection model.

Now, you can use this custom relationship method as you would with any other Laravel relationship:

// Example usage
$user = User::find(1);
$userConnections = $user->userConnections;

This will fetch all UserConnection records where the current user's id matches either the from_user_id or to_user_id field in the user_connections table.

Keep in mind that using raw SQL expressions like this may have security implications, so make sure to sanitize user inputs or use parameterized queries where necessary. Additionally, testing thoroughly is important to ensure the custom relationship behaves as expected in all scenarios.