CODEWITHSUNDEEP
phplaraveleloquent
Mohammad Fahad Rao
Mohammad Fahad Rao

Reputation: 190

How to Fetch data from multiple tables using relationships in laravel on certain conditions?

I am fetching data from multiple tables by joining them and the relationship is one to many and one to one Everything works fine as I am using joins with raw method (directly writing queries) but I want to use Laravel sophistication which means I am looking to use with() has() or whereHas() to get same results.

                                          My Query that works fine in controller

    

$licenses = DB::table('licenses')
            ->join('users as u', 'u.id', '=', 'licenses.user_id')
            ->join('users as sp', 'sp.id', '=', 'licenses.sales_person_id')
            ->join('license_types', 'license_types.id', '=', 'licenses.license_type_id')
            ->select('licenses.*', 'license_types.*', 'u.first_name', 'u.last_name', 'u.email', 'sp.first_name as fname', 'sp.last_name as lname')
            ->where('u.first_name', 'LIKE', '%' . $query . '%')
            ->orWhere('u.last_name', 'LIKE', '%' . $query . '%')
            ->orWhere('u.email', 'LIKE', '%' . $query . '%')
            ->orWhere('sp.first_name', 'LIKE', '%' . $query . '%')
            ->orWhere('sp.last_name', 'LIKE', '%' . $query . '%')
            ->get();


                                        My License Model

public function user()  
{
    return $this->hasOne('App\User','id','user_id');
}
public function license_type()
{   
    return $this->hasOne('App\LicenseType','id','license_type_id');
}



                The Response I get when the above mentioned query is executed
 #items: array:1 [
0 => {#444
  +"id": 1
  +"user_id": 2
  +"sales_person_id": 3
  +"license_type_id": 1
  +"license": "CCVTGS7S0R4M8P7R7S3R"
  +"license_duration": 23
  +"license_expiry": null
  +"allowed_test": "fef"
  +"no_of_devices_allowed": 1
  +"is_deleted": 0
  +"trial_activated_at": "2021-03-10 10:18:04"
  +"license_activated_at": null
  +"user_device_unique_id": "a9dc00sssd6daf79"
  +"is_active": 1
  +"created_at": null
  +"updated_at": null
  +"title": "monthly"
  +"price": "232"
  +"type": 2
  +"first_name": "Mohammad"
  +"last_name": "Fahad"
  +"email": "[email protected]"
  +"fname": "Mohammad"
  +"lname": "Fahad"
}

]

I want to get the same results with the Laravel eloquent but when I use with I dont know how to specify where conditions and to get same output.

Upvotes: 0

Views: 2129

Answers (1)

Kamlesh Paul
Kamlesh Paul

Reputation: 12401

you can try something like this

$licenses = License::with(['license_type', 'user', 'salesPerson'])

    ->whereHas('user', function ($q) use ($query) {
        $q->where('first_name', 'LIKE', '%' . $query . '%')
            ->orWhere('last_name', 'LIKE', '%' . $query . '%')
            ->orWhere('u.email', 'LIKE', '%' . $query . '%');
    })
    ->whereHas('salesPerson', function ($q) use ($query) {
        $q->where('first_name', 'LIKE', '%' . $query . '%')
            ->orWhere('last_name', 'LIKE', '%' . $query . '%')
            ->orWhere('u.email', 'LIKE', '%' . $query . '%');
    })->get();

in model you need to create another relationship

public function salesPerson()
{
    return $this->hasOne('App\User', 'id', 'sales_person_id');
}

NOTE this is not exact solution but you can modify as per your need

ref link https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

Upvotes: 1

Related Questions