Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi I ve these tables with relationships
Customers
namespace App;
use Illuminate\Database\Eloquent\Model;
class Customers extends Model
{
public $primaryKey = 'id';
protected $fillable = [
'contr_nom',
'contr_cog',
'benef_nom',
'benef_cog',
'email',
'polizza',
'targa',
'iban',
'int_iban',
'cliente',
];
public function claims()
{
return $this->hasMany(Claims::class);
}
}
Claims
namespace App;
use Illuminate\Database\Eloquent\Model;
class Claims extends Model
{
public $primaryKey = 'id';
protected $fillable = [
'dossier',
'date_cla',
];
public function refunds()
{
return $this->hasMany(Refunds::class);
}
public function customers()
{
return $this->belongsTo(Customers::class,'customers_id');
}
}
Refunds
namespace App;
use Illuminate\Database\Eloquent\Model;
class Refunds extends Model
{
public $primaryKey = 'id';
protected $fillable = [
'date_ref',
'status_ref',
'disactive',
];
public function services()
{
return $this->belongsToMany(Services::class)
->withPivot(['services_id','services_amount','services_status']);
}
public function claims()
{
return $this->belongsTo(Claims::class,'claims_id');
}
}
Services
namespace App;
use Illuminate\Database\Eloquent\Model;
class Services extends Model
{
public $primaryKey = 'id';
protected $fillable = [
'code',
'name',
];
public function refunds()
{
return $this->belongsToMany(Refunds::class);
}
}
I wanted to do a query that extract all "cliente" from Customers with a condition in the pivot table Refunds_services (where services_id=1) How can do this? Thx
So the pivot is on the refunds model, if you're trying to get all refunds where services_status is false for instance, you should be able to do something like
$false_service_refunds = Refunds::where('pivot.services_status', false)->get();
Is that what you mean?
ehm doesn't work. My original intent is to obtain a table that have two field one is "Client" field from CUSTOMERS and then another field that count how many times each "Client" have services_id = 1... a sort of summary pivot table that count how many services_id = 1 there are for each Client
Maybe i need something like this but no works :(
Customers::select('cliente')
->addSelect([
'countRefundService' => Customers::withCount('claims.refunds.services', function($query) {
$query->where('services_id', 1);
})->get()
])
->whereHas('claims.refunds.services', function ($query) {
$query->where('services_id', 1);
} )
->get();
@niloleon maybe instead of services_id in your example, you should use services.id? Just a guess.
In my pivot table "refunds_services" the name of the column is "services_id". It is the cross table because i ve many to many relationship between Refunds and Services. Mmm maybe i could just consider Refunds and Services that are correlated directly if i could obtain in some way the related field "clienti" from customer.. as a father relationship (Customers->Claims->Refunds). I dunno :(
@niloleon ok I reproduced your project locally (to shoot a video on my Youtube channel about Eloquent lesson) and here's the solution I've come up with.
// Step 1. Add hasManyThrough relationship
class Customer extends Model
{
// ...
public function refunds()
{
return $this->hasManyThrough(Refund::class, Claim::class);
}
}
// Step 2. The Query.
$customers = Customer::select(['cliente'])
->withCount(['refunds' => function($query) {
$query->whereHas('services', function ($query) {
$query->where('services.id', 1);
});
}])
->get()
->where('refunds_count', '>', 0);
// Step 3. Echo for testing:
foreach ($customers as $customer) {
echo $customer->cliente . ': ' . $customer->refunds_count; echo '<hr />';
}
P.S. In HasManyThrough, maybe your class names are different and you need to add more parameters like column names, check docs - https://laravel.com/docs/7.x/eloquent-relationships#has-many-through
Let me know if it works!
@povilaskorop Great! It works! Thx a lot
Hi @povilaskorop I noticed a problem, when services.id = 1 for different customers (different ids) but with the same "cliente" it separate rows. I tried a groupBy cliente, but no works....
Please or to participate in this conversation.