John Davidson

Message:

I'm new in Laravel (5.4). I work on redmine database and I want to have users role. The tables are :

users : id, login, hashed_password,...

members : id, user_id, project_id, created_on, mail_notification

member_roles : id, member_id (foreign key), role_id, inherited_from

roles : id, name,...

My query works perfectly but I want to make it fluent without "->select(...)" :

$membersRole = DB::connection('redmine')

      ->select('SELECT roles.name  as role, user_id

        FROM users, members, projects, member_roles, roles

        WHERE projects.id = ?

                  AND members.user_id = ?

                  AND members.project_id = projects.id

                  AND members.id = member_roles.member_id

                  AND roles.id = member_roles.role_id

        LIMIT 1', [$id, $userId]);

How can I do it ?

---

My Answer:
having to write raw SQL queries. Here's how you can achieve this using Laravel's Eloquent ORM and query builder:

First, define your models for the users, members, roles, and projects tables:

php
// User model
class User extends Model
{
protected $table = 'users';

public function members()
{
return $this->hasMany(Member::class, 'user_id');
}
}

// Member model
class Member extends Model
{
protected $table = 'members';

public function user()
{
return $this->belongsTo(User::class, 'user_id');
}

public function roles()
{
return $this->hasMany(MemberRole::class, 'member_id');
}
}

// Role model
class Role extends Model
{
protected $table = 'roles';

public function members()
{
return $this->hasMany(MemberRole::class, 'role_id');
}
}

// Project model
class Project extends Model
{
protected $table = 'projects';

public function members()
{
return $this->hasMany(Member::class, 'project_id');
}
}


Now, you can use Eloquent relationships to build your query:

php
$usersWithRoles = User::with(['members.roles.role'])->get();

foreach ($usersWithRoles as $user) {
foreach ($user->members as $member) {
foreach ($member->roles as $role) {
echo $user->login . ' has role ' . $role->role->name . ' in project ' . $member->project_id . '
';
}
}
}


This code will fetch all users along with their roles in each project they are a member of. You can further customize the query by adding conditions, eager loading, or other methods provided by Eloquent.