CONTEXT I have a many to many relationship, so I have 3 tables
Notices
Notices_states (Intermediate table)
state
This to have a history of the states of the Notice
This is how I have the relationships:
Model Notice:
public function estados(){
return $this->belongsToMany(Estado::class,'avisos_estados','estados_id','avisos_id')->withPivot('activo','created_at')->wherePivot('activo', 1);
}
Model State:
public function avisos(){
return $this->belongsToMany(Aviso::class,'avisos_estados','avisos_id','estados_id')->withPivot('activo','created_at');
}
The problem:
The problem arises when I try to query Notices that have active states (active = 1), I do the query as follows:
$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;
This brings me several states, some of which are unrelated to the notice I am consulting:
Result (It is summarized, it only shows the important part) Response without summarizing
"estados": [//Estos son los estados que me trae como respuesta
{
"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,//Esta respuesta es incorrecta debido a que busque el aviso con 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,//Esta respuesta es incorrecta debido a que busque el aviso con id=1
"activo": 1,
"created_at": "2022-09-21T15:12:42.000000Z"
}
}
]
The expected response would be:
"estados": [//Esta deberia ser la respuesta esperada, debido a que withpivot tiene un filtr cuando activo =1
{
"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,
"created_at": "2022-09-08T17:05:17.000000Z"
}
}
]
The question is: I will have something wrong in the relationships, should I do some kind of filter in the query?
This is the relationship structure.
And here I also leave what the states table contains just in case
And these are the only two status records that the notice has with id = 1
As can be seen at some point it was a notice with status = open but later I closed it for that reason the first status (open) became active = 0
I was able to solve it following indications in the comments, the only thing that changed was the relationships in the models, placing
avisos_id
first and it was as follows:Model Notice:
Model State:
And with this it brings me the active state that each notice has.