schwartzmj
1 month ago

Help building a query or using Eloquent

Posted 1 month ago by schwartzmj

I'm having some issues creating a query and structuring my data for a view.

I have a User model with role family that hasMany Applications

The view I'm displaying to the user is a table with the following table headings: Name, Email, and Year 2020 Application Status

Originally I was looping over every family and querying the database for every single row of the table in the view, which was causing hundreds of database queries. This is when I realized I should probably just query the database for all of the data I need in the controller and then send it to the view.

My goal is to query the database for the following:

  1. ALL Users with the role of "family"
  2. The current year's application for EACH family.
  3. If the family does NOT have a current year application, I want that field to still be there, but just null or some other falsy value.

One issue I'm having is that it looks like SQL "joins" merge all of the data together, so I have User data and Application within the same $family object. Is there a way to get the Application data to just be a key in the User data, like $family->application?

In my view I want to loop over each family and spit out a <tr> with the following data: $family->name, $family->email, $family->application->status.

$family->application can be null, so it'll be more like this for that <td>:

<td>
	@if($family->application)
		{{ $family->application->status }}
	@else
		Not Created
	@endif
</td>

Here's a few methods of retrieving data that I've tried which aren't quite what I'm going for:

                $data = DB::table('users')
                    ->where('role', 'family')
                    ->join('applications', function ($join) {
                        $join->on('users.id', '=', 'applications.family_id')
                            ->whereYear('applications.created_at', now()->year)
                    })
                    ->get();
                $family_users = DB::table('users')->where('role', 'family')->get();

                $applications_current_year = DB::Table('applications')
                    ->whereYear('created_at', now()->year)->get();

                return view('livewire.families-index')
                    ->with(compact('family_users', 'applications_current_year'));

                $this->families = User::where('role', 'family')
                    ->with([
                        'applications' => function ($query) {
                            $query->whereYear('created_at', now()->year);
                        }
                    ])
                    ->get();

Please sign in or create an account to participate in this conversation.