problem with one of many

[fpolli]

I want to have a one of many relationship that selects the one based on the VALUE of a column, not on an aggregate. I don't want the latest or oldest or min or max, but the one that has a specific column value. For example, I have a many to many relationship between users and addresses, and for the users, I also want to have a one of many relationship called "billing address" where I select the address of the many with the "usage" of "billing". As far as I can see from the docs and trying to decipher the source code, the only way to do that is to run a phony min/max on some column then add a closure that does my real selection. What I want to be able to do is something like:

return $this->hasOne(Address::class)->ofMany('usage', '=', 'billing')

I have been unable to find a way to do that within the relationships system.

I tried the following that used my many-to-many relationship:

return $this->addresses()->where('addresses.usage','billing')

Apparently it wrote the correct SQL but threw the error "must return a relationship instance but null was given." Null would be the correct return here because this is a new user with no addresses. I thought that the Eloquent queries were supposed to return null if no results were found.

[ghost]

return $this->hasOne(Address::class)->where('usage', '=', 'billing')

update

    return $this->hasOneThrough(
        Address::class,
        UserAddressPivot::class,
        'fill this', // b from pivot
        'fill this', // d from Address
        'fill this', // a from User
        'fill this' // c from pivot
    )->where('usage', '=', 'billing');

"Apparently it wrote the correct SQL but threw the error "must return a relationship instance but null was given." Null would be the correct return here because this is a new user with no addresses. I thought that the Eloquent queries were supposed to return null if no results were found."

one to many should return empty collection not null.

[fpolli]

Thank you. The primary relationship between User and Address is many-to-many, so the suggested solution does not work (I tried it earlier). It tries to access the addresses table directly looking for a user_id, which of course is not there.

[ghost]

That is what is happening when you don't define all params for relation functions...
You can use hasOneThrough

    return $this->hasOneThrough(
        Address::class,
        UserAddressPivot::class,
        'fill this', // b from pivot
        'fill this', // d from Address
        'fill this', // a from User
        'fill this' // c from pivot
    )->where('usage', '=', 'billing');

[fpolli]

Interesting use of hasOneThrough. I will work with that and let you know. Thank you for your time.