Laravel eloquent not fetching the data properly

user_10249502 0
Viewed 90

In my laravel vue application I have two tables, user table and user_document table.

In my user_document table, I have a column called, added_by.

This column stores the ids of the users.

There are two roles in my app, admins and general users.

My users can upload documents to the system via vue component. Admin can upload documents for other users as well.

And users can view their already uploaded document details in a datatable.

In that datatable, I'm displaying a column called, Added By, which shows the users who uploaded the document(the user him self or by the admin).

I have following controller to fetch and display those records.

<?php

namespace App\Http\Controllers\Dashboard\Corporate\Employee;

use App\Company;
use App\Models\OtherDocument;
use App\User;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class CompanyEmployeeOtherDocumentsController extends Controller
{
    /**
     * @param string $locale
     * @return \Illuminate\Http\JsonResponse
     */
    public function index(string $locale, Company $company, User $user)
    {

        $otherDocuments = OtherDocument::where('user_id', $user->id)
            ->with('added_by_user')
            ->when(request('validity_status'), function ($query) {
                $query->where(function ($query) {
                    $query->when( in_array('active', request('validity_status')), function ($query) {
                        $query->where(function (Builder $query) {
                            $query->whereDate('issued_at', '<=', Carbon::now())
                                ->whereDate('expires_at', '>=', Carbon::now()->addMonth());
                        })->orWhere('is_valid_forever',1);
                    })->when( in_array('expires', request('validity_status')), function ($query) {
                        $query->orWhere(function (Builder $query) {
                            $query->whereDate('expires_at', '<=', Carbon::now()->addMonth())
                                ->whereDate('expires_at', '>=', Carbon::now());
                        });
                    })->when( in_array('expired', request('validity_status')), function ($query) {
                        $query->orWhereDate('expires_at', '<', Carbon::now());
                    });
                });
            })
            ->when(request('search_text'), function ($query) {
                $query->where('name', 'like', '%' . request('search_text') . '%');
            })
            ->paginate(request('per_page',config('statguru.pagination.limit')));

        for ($i=0; $i < count($otherDocuments); $i++) {
            $addedByUser = $otherDocuments[$i]->added_by_user;
            if ($addedByUser) {
                $otherDocuments[$i]['added_user_name'] = $addedByUser->first_name . ' ' . $addedByUser->last_name;
            }
        }

        return  response()->json($otherDocuments);

    }
}

And in my vue component I have following header array,

HeaderArray() {
            return [
                {text: 'Certificate', value: 'certificate'},
                {text: 'Added by', value: 'added_by_user_name'},
                {text: 'Institute', value: 'institute'},
                {text: 'Expiration', value: 'valid_date', classList: 'text-center'},
                {text: 'Validity', value: 'validity_status', classList: 'text-center'},
                {text: 'Verification', value: 'verification_status', classList: 'text-center'},
            ];
        },

This whole solution works perfectly.

But now instead of using this for loop

for ($i=0; $i < count($otherDocuments); $i++) {
                $addedByUser = $otherDocuments[$i]->added_by_user;
                if ($addedByUser) {
                    $otherDocuments[$i]['added_user_name'] = $addedByUser->first_name . ' ' . $addedByUser->last_name;
                }
            }

I'm trying to get the same result via eloquent

->with(['added_by_user' => function($q){
                $q->select('first_name', 'last_name');
             }])

When I replace the for loop with this, it does not show me the Added by user's first name.... Where do I need to change in the eloquent to fetch that data correctly?

I'm using laravel 9

Update

I have updated my code according to the Malkhazi Dartsmelidze answer below.

Yet I am not being able to display the name on the datatable.

Following is my updated controller,

<?php

namespace App\Http\Controllers\Dashboard\Corporate\Employee;

use App\Company;
use App\Models\OtherDocument;
use App\User;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class CompanyEmployeeOtherDocumentsController extends Controller
{
    /**
     * @param string $locale
     * @return \Illuminate\Http\JsonResponse
     */
    public function index(string $locale, Company $company, User $user)
    {

        $otherDocuments = OtherDocument::where('user_id', $user->id)
        ->with(['added_by_user' => function($q){
            $q->select('id', DB::raw('CONCAT(first_name, " ", last_name) as added_user_name'));
        }]) 
            ->when(request('validity_status'), function ($query) {
                $query->where(function ($query) {
                    $query->when( in_array('active', request('validity_status')), function ($query) {
                        $query->where(function (Builder $query) {
                            $query->whereDate('issued_at', '<=', Carbon::now())
                                ->whereDate('expires_at', '>=', Carbon::now()->addMonth());
                        })->orWhere('is_valid_forever',1);
                    })->when( in_array('expires', request('validity_status')), function ($query) {
                        $query->orWhere(function (Builder $query) {
                            $query->whereDate('expires_at', '<=', Carbon::now()->addMonth())
                                ->whereDate('expires_at', '>=', Carbon::now());
                        });
                    })->when( in_array('expired', request('validity_status')), function ($query) {
                        $query->orWhereDate('expires_at', '<', Carbon::now());
                    });
                });
            })
            ->when(request('search_text'), function ($query) {
                $query->where('name', 'like', '%' . request('search_text') . '%');
            })

            ->paginate(request('per_page',config('statguru.pagination.limit')));

        return  response()->json($otherDocuments);

    }
}

and following is the vue header array,

 HeaderArray() {
            return [
                {text: 'Certificate', value: 'certificate'},
                {text: 'Added by', value: 'added_user_name'},
                {text: 'Institute', value: 'institute'},
                {text: 'Expiration', value: 'valid_date', classList: 'text-center'},
                {text: 'Validity', value: 'validity_status', classList: 'text-center'},
                {text: 'Verification', value: 'verification_status', classList: 'text-center'},
            ];
        },

And following is the data I recieve

{
      "id": 6,
      "user_id": 941,
      "added_by": 869,
      "name": "driving licence",
      "issued_at": "2022-04-06T22:00:00.000000Z",
      "expires_at": "2022-04-29T22:00:00.000000Z",
      "is_valid_forever": 0,
      "document_url": null,
      "document_number": null,
      "file_name": "6-driving-licence.pdf",
      "url": "users/941/other-documents/6-driving-licence.pdf",
      "status": "EXPIRES",
      "created_at": "2022-04-07T03:39:47.000000Z",
      "updated_at": "2022-04-07T03:39:47.000000Z",
      "deleted_at": null,
      "expires_at_formatted": "30-04-2022",
      "validity_status": "EXPIRES",
      "remaining_days": 18,
      "added_by_user": {
        "id": 869,
        "added_user_name": "Satya Nadella",
        "country_code": null,
        "formatted_date_of_birth": "2022-04-11",
        "name": " ",
        "company_role": "admin",
        "profile_image": [
          {
            "url": "/img/statguru-user.png"
          }
        ],
        "inviter_user": "",
        "country": null,
        "media": [
          
        ]
      }
    },
eloquent laravel vue.js relationship php
1 Answers

You are doing it wrong here:

->with(['added_by_user' => function($q){
    $q->select('first_name', 'last_name');
}])

The way relationship is working is that laravel loops trough result of that query and matches users.id to user_documents.added_by each other and if matches then attaches it.

Since you have selected only first_name and last_name columns, id of it always is null and not matches any user_documents.

Solution:

->with(['added_by_user' => function($q){
    $q->select('id', 'first_name', 'last_name');
}])

More convenient way to get full name:

use Illuminate\Support\Facades\DB;
...
->with(['added_by_user' => function($q){
    $q->select('id', DB::raw('CONCAT(first_name, " ", last_name) as added_user_name'));
}])

Edit:

Since front end requres that added_user_name should be in the first level of object, you should either map through results and move added_user_name to first level, or join users table and get result from it: But if you are using pagination it's not recommended to use map solution. I'll write only using join

Using join:

Remove ->with function from query and replace it with join.

OtherDocument::where('user_id', $user->id)
    ->join('users', 'users.id', '=', 'user_document.added_by') // do not know exact names of columns
    ->select(DB::raw('user_document.*'), DB::raw('CONCAT(first_name, " ", last_name) as added_user_name'))
    ->...
user_95074020
Related