CONTEXT in a relationship many to many, i have 3 tables
Avisos
Avisos_estados (Intermediate Table)
Estados
and the models:
Model Aviso:
public function estados(){
return $this->belongsToMany(Estado::class,'avisos_estados','estados_id','avisos_id')->withPivot('activo','created_at')->wherePivot('activo', 1);
}
Model Estado:
public function avisos(){
return $this->belongsToMany(Aviso::class,'avisos_estados','avisos_id','estados_id')->withPivot('activo','created_at');
}
The issue:
When i try to get the Aviso where id =1, with state (estados where activo =1)
My eloquent query:
$id=1;
$aviso = Avisos::select(
'Avisos.id',
'Avisos.titulo',
'Avisos.descripcion',
'Avisos.imagen',
'Avisos.created_at as fecha',
'Avisos.vistas',
'users.id as idUsuario',
'users.name',
'comunas.nombreComuna',
'ciudads.nombreCiudad'
)->join('categorias','categorias.id', '=','avisos.idCategoria')
->join('estados','estados.id', '=','avisos.idEstado')
->join('users','users.id', '=','avisos.idUsuario')
->join('comunas','comunas.id','=','avisos.idComuna')
->join('ciudads', 'ciudads.id','=','comunas.idCiudad')
->with('valoraciones')
->find($id);
//Aca uso el modelo para traer los estados y usuarios relacionados con el aviso
$aviso->users;
$aviso->estados;
The response has some states that has not relation with aviso.id=1
Response (just the important part) Complete response
"estados": [//these are the states in the response
{
"id": 2,
"descripcionEstado": "Cerrada",
"created_at": "2022-09-02T19:37:45.000000Z",
"updated_at": "2022-09-02T19:37:45.000000Z",
"pivot": {
"estados_id": 1,
"avisos_id": 2,//this is wrong, because i ask for aviso,id=1
"activo": 1,
"created_at": "2022-09-08T17:05:17.000000Z"
}
},
{
"id": 3,
"descripcionEstado": "Desierta",
"created_at": "2022-09-02T19:37:45.000000Z",
"updated_at": "2022-09-02T19:37:45.000000Z",
"pivot": {
"estados_id": 1,
"avisos_id": 3,//this is wrong, because i ask for aviso,id=1
"activo": 1,
"created_at": "2022-09-21T15:12:42.000000Z"
}
}
]
Expected response:
"estados": [//expected response
{
"id": 4,
"descripcionEstado": "Cerrada",
"created_at": "2022-09-02T19:37:45.000000Z",
"updated_at": "2022-09-02T19:37:45.000000Z",
"pivot": {
"estados_id": 2,
"avisos_id": 1,
"activo": 1,//With pivot only get where activo =1
"created_at": "2022-09-08T17:05:17.000000Z"
}
}
]
the question: is there something worng with my model relationship?, or should i use some filter in my query?
table estados (abierta = open, cerrada=closed,desierta=deserted, eliminada= deleted)
all states where aviso.id=1
as you can see, when i create the aviso this has a default state (open), but later change the state to closed, for that reason the record with description "Abierta (open)" has activo=0


