I'm building an app for a company dealing in phone numbers. A single phone number can have multiple clients over time. The pivot table has these columns:
number_id
customer_id
valid_from
valid_till
So, one number can have many customers, and one customer can have many numbers. But, a number can only have one CURRENT customer.
I'd like to be able to define a hasOne relationship, so that I can eager load the currently associated customers.
What I have already tried is the following:
public function customer()
{
return $this->belongsToMany(Customer::class, 'customer_number', 'number_id', 'customer_id')
->wherePivot('valid_from', '<=', Carbon::now())
->wherePivot('valid_till', '>', Carbon::now())
->limit(1);
}
But this returns a collection, which just doesn't feel right. Any idea how to define this as a has-one relationship?
I guess you can change it to first instead, limit still returns a collection for you.
return $this->belongsToMany(Customer::class, 'customer_number', 'number_id', 'customer_id')
->wherePivot('valid_from', '<=', Carbon::now())
->wherePivot('valid_till', '>', Carbon::now())
->first();
However keep in my mind that the many-to-many pattern is based on multiple items. Like a post can have multiple tags and it would weird to always return one tag instead of all the tags.
I have done something like this. I had Users belonging to companies. Multiple users could belong to multiple companies. So I had created a many to many relationship between them.
But at the user I also created a field current_company. And with this field I made a different relationship to always have access to the current company of the user.
Don't know if this is the right way, but for me it worked.
Edit: see post below.
customers
id
customer_number
customer_id number_id
numbers
id current_customer_id
class Customer extends Model
{
function numbers()
{
return $this->belongsToMany(Number::class);
}
}
class Number extends Model
{
function customers()
{
return $this->belongsToMany(Customer::class);
}
function currentCustomer()
{
return $this->belongsTo(Customer::class);
}
}
Actually I would do this (just spotted the valids).
contracts
id number_id customer_id valid_from valid_till
customers
id
numbers
id
class Contract extends Model
{
function customer()
{
return $this->belongsTo(Customer::class);
}
function number()
{
return $this->belongsTo(Number::class);
}
}
@bobbybouwmann noooooo, no first, a relationship method must return a relationship object ! ^^
@roggel use both relationship and accessor :
public function current_customers()
{
return $this->belongsToMany(Customer::class, 'customer_number', 'number_id', 'customer_id')
->wherePivot('valid_from', '<=', Carbon::now())
->wherePivot('valid_till', '>', Carbon::now());
}
public function getCustomerAttribute ()
{
return $this->current_customers->first(); // First is called on the collection, no extra query
}
@pmall I know how can i refactor getCustomerAttribute method into a oldestCustomer that returns a HasOne relationship?
Recording this for my own sanity and
@andrewalkermo responding to you since you are the most recent person asking the same question I had.
I wanted to create HasOne from a ManyToMany/BelongsToMany so that I could eager load the relations in queries.
This was the workaround I figured out after scouring for answers.
customers
id
customer_number
customer_id number_id
numbers
id current_customer_id
class Customer extends Model
{
function numbers()
{
return $this->belongsToMany(Number::class, 'customer_number');
}
}
class CustomerNumber extends Pivot
{
// Indicates if the IDs are auto-incrementing
public bool $incrementing = true;
function customer()
{
return $this->belongsTo(Customer::class);
}
function number()
{
return $this->belongsTo(Number::class);
}
}
class Number extends Model
{
function customers()
{
return $this->belongsToMany(Customer::class, customer_number);
}
function customerPivots()
{
return $this->hasMany(CustomerNumber::class);
}
function currentCustomerPivot()
{
return $this->customerPivots()->one()->ofMany(
['id' => 'max'], // I am assuming current is the latest here
function (Builder $customerPivotsQuery) {
$customerPivotsQuery->where('valid_from', '<=', Carbon::now());
$customerPivotsQuery->where('valid_till', '>', Carbon::now());
}
);
}
}
So if we want to grab the current customer then we can do something like this
$number->currentCustomerPivot->customer
OR
$number->load('currentCustomerPivot.customer'); OR Number::query()->with('currentCustomerPivot.customer');
@reinhardt021 fortunately, we now have OneOfMany relationships. https://laravel.com/docs/11.x/eloquent-relationships#has-one-of-many
@Snapey apologies,
I meant to say HasOne from a ManyToMany/BelongsToMany
instead of HasOne from a HasMany
Thanks @pmall, works like a charm.
@roggel Expanding on my previous post and stealing @pmall snippet.
Note none of this is tested but hopefully conveys the idea. In this specific case I wouldn't use a pivot.
contracts
id number_id customer_id valid_from valid_till
customers
id
numbers
id
class Contract extends Model
{
public function customer()
{
return $this->belongsTo(Customer::class);
}
public function number()
{
return $this->belongsTo(Number::class);
}
}
trait ContractTrait
{
function contracts()
{
return $this->hasMany(Contract::class, $this->foreign_key);
}
public function validContracts()
{
return $this->hasMany(Contract::class, $this->foreign_key)
->where('valid_from', '<=', Carbon::now())
->where('valid_till', '>', Carbon::now());
}
public function getCurrentContractAttribute()
{
return $this->validContracts->first();
}
}
class Customer extends Model
{
use ContractTrait;
protected $foreign_key = 'customer_id';
}
class Number extends Model
{
use ContractTrait;
protected $foreign_key = 'number_id';
}
Please or to participate in this conversation.