Home / Questions / 446987
Accepted
Wilmilcard
Asked: 2022-04-25 21:20:03 +0800 CST 2022-04-25 21:20:03 +0800 CST

How to make a subquery within the Api php Laravel query?

  • 772

I am creating an Api Rest cpn php Laravel, but I have a concern. I have 2 tables already related in MySql:

  • Condition
  • Person

Model:

enter image description here

and in the controllers when consuming the method getof each one it goes like this:

return response()->json(Cliente::all(),200);

and the response in json is this:

[
  {
    "id": 1,
    "nombre": "Prof. Kaitlin Kuhic",
    "nit": "389531851",
    "estado": 1
  },
  {
    "id": 2,
    "nombre": "Rosalinda Durgan",
    "nit": "512817602",
    "estado": 1
  },
  {
    "id": 3,
    "nombre": "Loren Tillman",
    "nit": "11167177",
    "estado": 2
  }
]

And if I consume the getcontroller method of the status table I output this json:

return response()->json(Estado::all(),200);

Response in json:

[
   {
      "id": 1,
      "nombre": "Activo"
   },
   {
       "id": 2,
       "nombre": "Inactivo"
   }
]

What I want is for the json to come out like this:

[
      {
        "id": 1,
        "nombre": "Prof. Kaitlin Kuhic",
        "nit": "389531851",
        "estado": [
               {
                 "id": 1,
                 "nombre": "Activo"
               }
           ]
      },
      {
        "id": 2,
        "nombre": "Rosalinda Durgan",
        "nit": "512817602",
        "estado": [
               {
                 "id": 1,
                 "nombre": "Activo"
               }
           ]
      },
      {
        "id": 3,
        "nombre": "Loren Tillman",
        "nit": "11167177",
        "estado": [
               {
                 "id": 2,
                 "nombre": "Inactivo"
               }
          ]
      }
]

What I want is that when calling getpeople, the controller makes me a subquery in the status field and brings me the status of that Client.

php

1 Answers

  1. Best Answer

    They are already related, from the migrations.

    I understand that you have added the foreign key in the migrations. This creates a relationship, yes, but it is only a restriction when creating records, it has no effect when making queries.

    Eloquent like any other ORM, to work it needs to "map" the relationships that exist between the tables , in some way, to be able to fetch related records and other operations. In your case you must relate the Eloquent models:

    class Client extends Model
{
    public function status()
    {
        return $this->hasOne(Status::class, 'status_id');
    }
}
class Status extends Model
{
    public function client()
    {
        return $this->belongsTo(Client::class, 'client_id');
    }
}

    Now to fetch the state of a client, within the JSON, you have to use Eager Loading :

    return response()->json(Client::with('status')->get(),200);
    • 1