Home > Net >  Laravel query showing different Result in DB::Table and DB::Select with inner join
Laravel query showing different Result in DB::Table and DB::Select with inner join

Time:05-19

I'm using 3 table 1.organizations 2.users 3.organization_user

So the organization_user is the pivot table for getting org. and user.

DB::select("SELECT users.name 'User Name',organizations.name 'Org Name'
   FROM users INNER JOIN organization_users ON 
organization_users.user_id=users.id
    INNER JOIN organizations ON 
organizations.id=organization_users.organization_id
   WHERE organization_users.organization_id=$org_id");

And getting the below result

[ { "User Name": "Navid Anjum", "Org Name": "org1" }, { "User Name": "kamal", "Org Name": "org1" } ]

But when using

DB::table('organization_users')
        ->join('users','users.id','=','organization_users.user_id')
        ->join('organizations','organizations.id','=',
        'organization_users.organization_id')
        ->select('users.name as Name','organizations.name as Org Name')
        ->where('organization_users.id','=',$org_id)
        ->get();

And getting the below result

[ { "Name": "Navid Anjum", "Org Name": "org1" } ]

So What I'm doing wrong?

CodePudding user response:

you not set the right id for organization_users in the second statement :

DB::table('organization_users')
    ->join('users','users.id','=','organization_users.user_id')
    ->join('organizations','organizations.id','=',
    'organization_users.organization_id')
    ->select('users.name as Name','organizations.name as Org Name')
    ->where('organization_users.organization_id','=',$org_id)
    ->get();
  • Related