I have two tables, one called users and another users, I made the query directly to mysql.
SELECT COUNT(nombre), usuario_ad, name, last_name
FROM usuarios, users
where usuario_ad=username
GROUP BY usuario_ad;
which works fine, the last_name and name columns are from the users table, while the user_ad and count (name) are from users.
The problem is when I want to pass this to eloquen, since I have tried to join two tables with this dinaminca but it has caused me problems.
DB::table('usuarios')
->select('usuario_ad', DB::raw('count(nombre) as nombre'))
->groupBy('usuario_ad')
->orderBy('nombre', 'desc')
->paginate(10);This query works well for a single table, how can I adapt it to the mysql query of two tables that I showed above?
users
$table->increments('id');
$table->foreign('tipos_usuarios_id')->references('id')->on('tipos_usuarios');
$table->string('name');
$table->string('last_name');
$table->string('username',15)->unique();
$table->string('email')->unique();
$table->string('password');
$table->unsignedInteger('tipos_usuarios_id');
$table->rememberToken();
$table->timestamps();
});users
Schema::create('usuarios', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('user_id');
$table->foreign('user_id')->references('id')->on('users');
$table->string('usuario_ad')->nullable();
$table->string('nombre')->required();
$table->string('apellido')->required();
$table->integer('cedula')->required()->unique();
$table->string('telefonos')->required();
$table->string('direccion')->required();
$table->string('barrio')->required();
$table->string('lugar_votacion')->required();
$table->string('mesa_votacion')->required();
$table->string('email')->required()->unique();;
$table->string('ciudad')->required();
$table->string('asignacion')->nullable();
$table->string('estado')->nullable();
$table->timestamps();
});SOLUTION
$conteo = DB::table('usuarios')
->join('users', 'usuarios.user_id', '=', 'users.id')
->select('usuario_ad', 'name', 'last_name', DB::raw('count(nombre) as nombre'))
->groupBy(['usuario_ad', 'name', 'last_name'])
->orderBy('nombre', 'desc')
->paginate(10);