CODEWITHSUNDEEP
mysqllaraveljoin
John
John

Reputation: 181

Compare two fields within different table using Laravel

I have two tables employees and customers , i've gave the schema below.

Customers('id' , 'username', 'location');

Employees('id' , 'EmployeeID' , 'CustomerID', 'location');

Currently I can use a query to retrieve customers details like the below query , note this is when the user is logged into the system hence the Auth::

$customerQuery1 = DB::table('customer')
            ->where('id', '!=', Auth::id())  
            ->where('item', '=' , Auth::customer()->recommendation)  
            ->get();

Each Employee has many customers ,I want other customers to see other customer items so i have attach the CustomerID field which is a foreign key and relates to the id field within the Customer table.

I've tried something like the below however I think I may need a join query but i'm unsure.

$query2 =  DB::table('Customer','Employee')
        ->select('username')
        ->where(['EmployeeID' => Auth::id(), 'CustomerID' => 'id']) 
        ->get(); 

$query2 =  DB::table('Customer')
        ->select('username')
        ->join('Employee', 'Customer.id', '=', 'Employee.CustomerID')             
        ->where(['EmployeeID' => Auth::id(), 'CustomerID' => 'id']) 
        ->get();

I am then returning the values to my blade file like the below

 return view ('pages.dashboard')
 ->with('query1',$query1)

and then Im using php indentation within my blade file to return the users data

 @foreach ($query1 as $Userfound)
 {{ $Userfound->username}}</p> 

@endforeach

Actual Query needed in plain english

so I need to select a customer , where CustomerID == id

NOTE: id is from the customers table, CustomerID stored in the Employees table.

Upvotes: 0

Views: 2721

Answers (1)

party-ring
party-ring

Reputation: 1871

You can create Models using Laravel, for example:

Employee.php

public function customers()
{
    return $this->hasMany('App\Customer');
}

Customer.php

public function employee()
{
    return $this->belongsTo('App\Employee');
}

Which you can access like so:

$customer = Customer::where('id',Auth::user()->id)->firstOrFail();

or

$employee = Employee::where('id',Auth::user()->id)->firstOrFail();

And to see an employee's customers:

$employee->customers()->get();

Or to see the other customers of $customer's employer:

$customer->employee()->customers()->get();

Upvotes: 1

Related Questions