Problem with Retrieving Intermediate Table Columns Laravel

Viewed 24

CONTEXT in a relationship many to many, i have 3 tables

  1. Avisos

  2. Avisos_estados (Intermediate Table)

  3. 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?

Structure many to many Relacion manytomany

table estados (abierta = open, cerrada=closed,desierta=deserted, eliminada= deleted)

Tabla estados

all states where aviso.id=1

Aviso=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

0 Answers
Related