Home / Questions / 530327
Accepted
Wilmilcard
Asked: 2022-06-27 17:55:35 +0800 CST 2022-06-27 17:55:35 +0800 CST

How to fetch fields from related tables with Eloquent PHP Laravel?

  • 772

I am creating an api in PHP Laravel, I already come from C# and Entity Framework but there are things that I still don't understand how they work in Laravel.

I have this query:

People::with('user','rol','state','tower','apartament')->get()

and it brings me this information:

[
        {
            "id": 1,
            "name": "User1",
            "last_name": "lnUser1",
            "phone": 3132835875,
            "img_profile": null,
            "floor": 4,
            "user": {
                "id": 1,
                "email": "[email protected]",
                "email_verified_at": null,
                "created_at": null,
                "updated_at": "2022-04-26T03:12:03.000000Z"
            },
            "rol": {
                "id": 1,
                "name": "Administrador"
            },
            "state": {
                "id": 1,
                "name": "Activo"
            },
            "tower": {
                "id": 1,
                "name": "A"
            },
            "apartament": {
                "id": 116,
                "name": "1206"
            }
        },
        {
            "id": 2,
            "name": "User2",
            "last_name": "lnUser2",
            "phone": 3132835875,
            "img_profile": null,
            "floor": 2,
            "user": {
                "id": 2,
                "email": "[email protected]",
                "email_verified_at": null,
                "created_at": null,
                "updated_at": null
            },
            "rol": {
                "id": 2,
                "name": "Moderador"
            },
            "state": {
                "id": 2,
                "name": "Inactivo"
            },
            "tower": {
                "id": 1,
                "name": "A"
            },
            "apartament": {
                "id": 116,
                "name": "1206"
            }
        }
]

a list of users. but I need to show only a couple of data not everything. so build this query like this:

People::with('user','rol','state','tower','apartament')->select(DB::raw('CONCAT(name, " ", last_name) as name, phone, img_profile, floor'))->get()

and it brings me this response:

[
{
            "name": "User1 lnUser1",
            "phone": 3132835875,
            "img_profile": null,
            "floor": 4,
            "user": null,
            "rol": null,
            "state": null,
            "tower": null,
            "apartament": null
        },
        {
            "name": "User2 lnUser2",
            "phone": 3132835875,
            "img_profile": null,
            "floor": 2,
            "user": null,
            "rol": null,
            "state": null,
            "tower": null,
            "apartament": null
        }
]

I have searched and found out but on all occasions it generates an error or problems with the query.

So I don't know how to select only the specific field(s) from the related tables (user, role, state, tower and apartment). I need an answer that comes like this:

[
    {
        "name": "User1 lnUser1",
        "phone": 3132835875,
        "img_profile": null,
        "floor": 4,
        "user_email": "[email protected]",
        "rol_name": "Administrador",
        "state_name": "Activo",
        "tower_name": "A",
        "apartament_number": "1206"
    },
    {
        "name": "User2 lnUser2",
        "phone": 3132835875,
        "img_profile": null,
        "floor": 2,
        "user_email": "[email protected]",
        "rol_name": "Moderador",
        "state_name": "Inactivo",
        "tower_name": "A",
        "apartament_number": "1206"
    }
]
php

3 Answers

  1. Good day,

    To get only certain columns you can use the methodpluck()

    Example:

    People::pluck('user','rol','state','tower','apartament')->get()
    • 1
  2. Best Answer

    If you require something more complex and want to use a simpler query you can use Laravel's querybuilder an example here with a join

              DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();
    • 1

  3. You can use accessors to create Eloquent model fields, for example:

    public function getRolNameAttribute {
    return $this->rol->name;
}

    This makes it possible for you to access the role's name field from an instance of People: $somePeople->rol_name. Here you can read more about accessors.

    After defining your accessors for the fields you want to add to the response, you add them to the $appendsmodel class property, this causes those fields to be added to the model responses:

     protected $appends = ['rol_name', 'state_name', 'tower_name'];
    • 0