I'm using Laravel (in Jetstream/Inertia with VueJS), and in my controller, I'm querying three tables: users, stations, and shifts. shifts is my main table, and it has foreign keys to the users table (user_id) and stations table (station_id).

Within my controller, I'm using the following query to get all of the data.

$monthShifts = Shift::where(DB::raw('MONTH(date)'), $queryMonth)
    ->where(DB::raw('YEAR(date)'), $queryYear)
    ->with('user', 'station')
    ->get();

and this gets me what I need in the following format:

$shifts = [
    [0] => Array
        (
            [id] => 1
            [date] => 2021-09-15
            [user_id] => 3
            [station_id] => 1
            [am_pm] => pm
            [user] => Array
                (
                    [id] => 3
                    [name] => Andy Ryan
                    [type] => suppression
                    [email] => emann@mydomain.com
                )

            [station] => Array
                (
                    [id] => 1
                    [station_number] => 12
                    [station_name] => Main
                )
        )

    [1] => Array
        (
            [id] => 6
            [date] => 2021-09-12
            [user_id] => 6
            [station_id] => 2
            [am_pm] => pm
            [created_at] => 2021-09-15T23:05:27.000000Z
            [updated_at] => 2021-09-15T23:05:27.000000Z
            [user] => Array
                (
                    [id] => 6
                    [name] => Mohammed Veum
                    [type] => suppression
                    [email] => hhuel@mydomain.com
                )

            [station] => Array
                (
                    [id] => 2
                    [station_number] => 13
                    [station_name] => Secondary
                )

        )

    [2] => Array
        (
            [id] => 7
            [date] => 2021-09-11
            [user_id] => 7
            [station_id] => 2
            [am_pm] => pm
            [created_at] => 2021-09-15T23:05:27.000000Z
            [updated_at] => 2021-09-15T23:05:27.000000Z
            [user] => Array
                (
                    [id] => 7
                    [name] => Santa Wolf
                    [type] => support
                    [email] => mbashirian@mydomain.com
                )

            [station] => Array
                (
                    [id] => 2
                    [station_number] => 13
                    [station_name] => Secondary
                )

        )

    [3] => Array
        (
            [id] => 9
            [date] => 2021-09-05
            [user_id] => 6
            [station_id] => 2
            [am_pm] => pm
            [created_at] => 2021-09-15T23:05:27.000000Z
            [updated_at] => 2021-09-15T23:05:27.000000Z
            [user] => Array
                (
                    [id] => 6
                    [name] => Mohammed Veum
                    [type] => suppression
                    [email] => hhuel@mydomain.com
                )

            [station] => Array
                (
                    [id] => 2
                    [station_number] => 13
                    [station_name] => Secondary
        )

    [4] => Array
        (
            [id] => 10
            [date] => 2021-09-02
            [user_id] => 7
            [station_id] => 2
            [am_pm] => pm
            [created_at] => 2021-09-15T23:05:27.000000Z
            [updated_at] => 2021-09-15T23:05:27.000000Z
            [user] => Array
                (
                    [id] => 7
                    [name] => Santa Wolf
                    [type] => support
                    [email] => mbashirian@mydomain.com

            [station] => Array
                (
                    [id] => 2
                    [station_number] => 13
                    [station_name] => Secondary
                )

        )

    [5] => Array
        (
            [id] => 13
            [date] => 2021-09-02
            [user_id] => 3
            [station_id] => 2
            [am_pm] => pm
            [created_at] => 2021-09-15T23:05:27.000000Z
            [updated_at] => 2021-09-15T23:05:27.000000Z
            [user] => Array
                (
                    [id] => 3
                    [name] => Andy Ryan
                    [type] => suppression
                    [email] => emann@mydomain.com
                )

            [station] => Array
                (
                    [id] => 2
                    [station_number] => 13
                    [station_name] => Secondary
                )
        )
]

However, what I would like to do is get only specific fields from all three:

1. am_pm, id from the top level (shift)
2. station_number from station
3. name and type from user

In straight SQL, I would write it like this:

select s.id, s.date, s.am_pm, u.name, u.type, st.station_number
from shifts s inner join users u on s.user_id = u.id
inner join stations st on s.station_id = st.id
where MONTH(date) = {month}
and year(date) = {year}

How can I limit the items to just those fields, and also flatten them? If I want to not include the station and user objects, I can do this to limit the shift data:

         $monthShifts = Shift::where(DB::raw('MONTH(date)'), $queryMonth)
             ->where(DB::raw('YEAR(date)'), $queryYear)
             ->get(['date', 'am_pm', 'id'])
             ->groupBy(['date', 'am_pm']);

but then I lose the other data (user and station) I need, as well as the grouping by station_number.

CodePudding user response：

using pluck method will solve your problem

https://laravel.com/docs/8.x/collections#method-pluck

CodePudding user response：

 ->with(['user'=>function ($query) {
                $query->select('id', 'name', 'type');
              }, 
 'station'=>function ($query) {
          $query->select('station_number');
      }])->get(['id','am_pm','user_id','station_id'])

u have to include 'user_id' and 'station_id' from shifts table inside get() in order to get the relation data this way