Problem query eloquent

I have made this query in mysql, to consult two tables and it works well.

SELECT COUNT(nombre), usuario_ad, name, last_name FROM usuarios, users where usuario_ad=username GROUP BY usuario_ad;

A member of this forum helped me with one, but I can not find how to adapt it for two tables.

public function reportes(Request $request) { $request->user()->authorizeRoles(['admin']);

    $conteo = DB::table('usuarios')
          ->select('usuario_ad', DB::raw('count(nombre) as nombre'))
          ->groupBy('usuario_ad')
          ->orderBy('nombre', 'desc')
          ->paginate(10);

    return view('usuario.reportes',compact('conteo'));

    
 }

the name and last_name columns are from the users table

douglasakula

7 years ago

Best Answer

Level 15

@daniel21gt - Check out joins - https://laravel.com/docs/5.7/queries#joins

daniel21gt

7 years ago

Level 1

Thanks douglasakula, this is solution.

public function reportes(Request $request) { $request->user()->authorizeRoles(['admin']);

    $conteo = DB::table('usuarios')
          ->join('users', 'usuarios.user_id', '=', 'users.id')
          ->select('usuario_ad', 'name', 'last_name', 'tipos_usuarios_id', DB::raw('count(nombre) as nombre'))
          ->groupBy(['usuario_ad', 'name', 'last_name', 'tipos_usuarios_id'])
          ->orderBy('nombre', 'desc')
          ->paginate(10);

    return view('usuario.reportes',compact('conteo'));

    
 }

Please or to participate in this conversation.