Recuperating roles of users from DATABASE (Foreign

Question

Can anyone tell me how i can recuperate roles of users from my database? Knowing i have 3 tables (Users, Roles, User_role) Migrations:

Table users:

 public function up()
      {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();

        });
    }

Table roles:

public function up()
       {
        Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('description');
            $table->timestamps();
        });
       }

Table user_role:

public function up()
         {
        Schema::create('user_role', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id');
            $table->integer('role_id');
            $table->timestamps();
        });
       }

the function in my controller that returns the list of users is membrevis():

 public function membrevis()
     {
      $filter = isset($_GET['filter']) ? $_GET['filter'] : null;
        $query = DB::table('users')
        ->join('user_role', 'users.id', '=', 'user_role.user_id')
        ->join('roles', 'user_role.role_id', '=', 'roles.id')
        ->where('users.valid','=',1)
        ->select('users.*','roles.description');

    if ($filter != null) {
        $query->where('users.name','like','%'.$filter.'%')
            ->orWhere('roles.description','like','%'.$filter.'%');
         }

       $itemsPerPage = 8 ;
       $currentPage  = isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) ? $_GET['page'] : 1;
         $urlPattern   = '/membre2?page=(:num)';
         $totalItems   = $query->count();
      $donner   = $query->offset( ( $currentPage - 1 ) * $itemsPerPage )->limit( $itemsPerPage )->get();
       $paginator = new  Paginator( $totalItems, $itemsPerPage, $currentPage, $urlPattern );

      return view('membre2',['users'=> $donner,'paginator'=> $paginator]);
     }

what can i modify here to recuperate users and roles of them? foreign keys problem

Answer 1

I recommend you using Laravel Models and relationships.

Here you have a relationship between Users and Roles. This relationship is defined in what is called an intermediate table, in this case the user_roles table.

There is a Many-to-Many type of relationship here between users and roles, where one user can have many roles and one role type can be assigned to many users.

I recommend you reading this: https://laravel.com/docs/5.2/eloquent-relationships#many-to-many

That's for Laravel 5.2, but you'll see that documentation for every Laravel 5.x version. There you'll learn how to build these relationships and how to use them effectively.