Return first relation from mode

add a hasOne relationship on your User model

public function userDetail()
{
    return $this->hasOne(UserDetail::class);
}
public function userDetails()
{
    return $this->hasMany(UserDetail::class);
}

You can get one UserDetail for an account like this

$user_id = 123;
$account_id = 234;
$user = User::find($user_id)->with([
    'userDetail' => function($query) use ($account_id) { 
        $query->where('acount_id', $account_id); 
    },
]);
$oneDetail = $user->userDetail;
$allDetail = $user->userDetails;

You have your database setup wrong. Since you got that wrong I would advise to put everything from UserDetail into Account. But if you want to extract a separate table then go with following:

Account
- id
- name
- user_detail_id

User
- id
- email
- ...

UserDetail
- ...

Maybe I explained badly (or the Model naming may be bad...), but anyhow.

Lets say Facebook and Google shared users.

Facebook and Google would be two different "Accounts".

Users are shared between Accounts but there are some details that will differ that should be saved on the user for that specific account.

Is it possible to add the following code:

$user = User::find($user_id)->with([
    'userDetail' => function($query) use ($account_id) { 
        $query->where('acount_id', $account_id); 
    },
]);

To the model somehow so that I don't have to do this everytime I need the account specific in a query?

Yap, then I'm right. All data is specific to Account and not to User. So I advise again to put it on Account or like I said you can extract it to separate model but that model belongs to Account.

What you've created is a pivot table which is basically for ManyToMany relationships. Ofc you can use that kind of database architecture for different relationships, but it is not what you are looking for this example.

Sounds like a single user can have multiple social profiles (just two tables) with the social profile being polymorphic

Hi.

Maybe try to group the collection from the relation by account_id and then access the data with that?

public function userDetails()
{
    return $this->hasMany(UserDetail::class);
}

publuc function getUserDetailsAttribute()
{
	return $this->userDetails->groupBy('account_id');
}

$user = User::find($user_id)->with('userDetails');

$user->user_details->get($account_id)->whatever_property;

Users are shared between Accounts

This implies that Account belongs to User

but there are some details that will differ that should be saved on the user for that specific account

If something is specific for Account that that stuff belongs to an Account

Having wrong database structure can create various problems down the road. Fix that ASAP to prevent complications in your code.

Ok, maybe I'm still explaining badly... But I'm pretty sure the database structure is fine. The application is several months into development. What I'm looking for now is some "tricks" to tidy the code and move query-logic into the model if possible.

That said:

I cant add user specific information to "Account". You can see accounts as separate websites. The name of the account is the name of the website. These fields has nothing to do with the user.

When a user logins to an specific website (Account) the information saved for that user should be specific for that website (saved in user details).

That again confirms my statement that what you are referring to as "user details" is associated with what you are calling an "account". "user detail" is not associated with "user" directly, instead it goes through "account".

Account

• id: 1
• name: Facebook

Account

• id: 2
• name: Google

User

• id: 1
• name: John Smith

UserDetails

• user_id: 1
• account_id: 1
• username: john

UserDetails

• user_id: 1
• account_id: 2
• username: smith

Is this what you are trying to achieve?

Yes, that is correct.

Now, lets say I would like to sort all users by username (for a specific account).

I would like to do something like

$users = User::orderBy('userDetail.username');

(Notice the userDetail (without s))

Is the relationship between User and Account ManyToMany? Or maybe better question would be "are accounts shared between multiple users"?

The database looks like achatzi explained. (At least for my example... I've tried to simplifies it as much as possible)

An account has no relation with an user. User and Account are "freely" floating entities.

BUT, an user can save information that is available on a specific account. In that case an "UserDetails" is saved and connects the user with the account.

@andreasbergqvist I don't think you can do that without joins and custom sql.

User::orderBy('userDetail.username');

This will not work.

You would have to go this way (pseudocode below)

User::join('userDetails')->orderBy('userDetails.username');

@bugsysha Based on his response, yes. It is a ManyToMany relationship and they shared. I think that naming got us confused (I would name the model Website for example).

php artisan make:model UserDetail -p

$userDetails = UserDetail::with('user')->orderBy('username')->get();

@foreach ($userDetails as $detail)
	{{ $detail->user->something }}
@endforeach

@achatzi yes, naming put us off the right path. Also he was trying to simplify what he really needs. That is a common issue here where everyone is not giving all required info for some reason.

Yeah, maybe I'm confusing you with the naming... But I'm just trying to explain an example of what I would like to achieve.

The database structure is set, and I have already achieved what I want with joins and custom sql.

But when I want to reuse that logic (fetching that ONE detail for the active account and use in where's and order's) I see myself copying these joins and custom sql all over the place.

Thats why I asked if there is some "laravel-way" to more this logic into the model.

When you say "laravel-way" like that is sounds like a bad thing 🤣

Sorry, that what not at all what I meant...

I'm trying to clean up my code base. That means doing it the "laravel way".

@andreasbergqvist Maybe you can create scopes with these queries

public function scopeWithDetails($builder, $account_id)
{
	return $builder->join('userDetails on $account_id');
}

$users = User::withDetails($account_id)->get();

Or maybe a Repository pattern?

sorry I made a typo earlier. Just correct it. I didn't mean changing the existing hasMany relationship. You can have a hasOne and a hasMany relationships at the same time.

No need to be sorry.

I'm still not sure what you are asking.

If I understand the underlying question then you have multiple options.

1. Create separate relationship

public function orderedUserDetails()
{
	return $this->userDetails()->orderBy('userDetails.username');
}

1. Create scope

public function scopeOrderedUserDetails($query)
{
	return $query->join('userDetails')->orderBy('userDetails.username');
}

Something like that should work. Haven't used those tricks in a while so I might be mistyping something but in general that might be your guide.

Thanks for all the tips. I'll do some more testing tomorrow (late night for me now)...

If I would take a more "complicated" example. Let's say we have a "score" field in User Details. And the user can write comments. I would like to sort all comments by the users score.

I.e: $comments = Comment::with('user')->orderBy('user.userDetail.score')->get();

Still I understand that the above code does not work. Because the user only have a userDetails hasMany relationship.

But I'll try some of the above code tomorrow.

Sounds like you need a consultant.

I think this is what you need

add a hasOne relationship on your User model

public function forAccount($account_id)
{
    $this->account_id = $account_id;
    return $this;
}
public function userDetail()
{
    return $this->hasOne(UserDetail::class)->where('account_id', $this->account_id);
}

You can get one UserDetail for an account like this

$user_id = 123;
$account_id = 234;
$user = User::find($user_id)->forAccount($account_id)->with('userDetail');