Compare two fields within different table using Laravel

Question

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.

So what is it you want to accomplish exactly with the query? What should be the expected result? — Dees Oomens
– Dees Oomens, Commented Apr 5, 2019 at 13:21
you can try laravel relationship but for that you need to create Model file for your table. for more reference you can check below link laravel.com/docs/5.8/eloquent-relationships — Niraj Savaliya
– Niraj Savaliya, Commented Apr 5, 2019 at 13:26
I can test the results my self , i'm returning $query or the variable to my blade file. @DeesOomens . I'll update my post to show you — John
– John, Commented Apr 5, 2019 at 13:26
What do you mean by "I want other customers to see other customer items"? — party-ring
– party-ring, Commented Apr 5, 2019 at 13:29
I kind like have a following system , I want other customer to see other customers details. — John
– John, Commented Apr 5, 2019 at 13:30

party-ring · Accepted Answer · 2019-04-05 14:05:01Z

1

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();

edited Apr 5, 2019 at 14:05

answered Apr 5, 2019 at 13:31

party-ring

1,8811 gold badge21 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thisiskelvin Over a year ago

@party-ring When using relations, you need to either use ->get() to finish the eloquent builder, or use ->customer (without ()) which will return all as expected.

John Over a year ago

@party-ring this is the error message i receive SQLSTATE[42S02]: Base table or view not found: 1146 Table 'musicdatabase.follower_user' doesn't exist (SQL: select follower.*, follower_user.user_id as pivot_user_id, follower_user.follower_id as pivot_follower_id from follower inner join follower_user on follower.id = follower_user.follower_id where follower_user.user_id = 1)

John Over a year ago

i'll give an example of what I want the actual output to be in my post

Collectives™ on Stack Overflow

Compare two fields within different table using Laravel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related