User Roles Many to Many

In a previous post, I shared how to create user roles with one to many relationship. Another way to design user roles in your application is to create a many to many relationship. This means many users can have many roles.

It is very similar as the previous design except you won’t have foreign keys in the users or roles table. There will be a pivot table that will have a foreign key for the users and roles table. You also won’t need to have the roles table created prior to the users table. But both users and roles table need to exist before the pivot table is created.

Let’s create the migration file for the roles table.

php artisan make:migration create_roles_table

Edit this file with the following code.

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

Again, I chose not to have the timestamps columns, but you can if you wish. If you don’t want the timestamps column, you should add some code in your Role model class to let eloquent know. Your tables should look something like below.

Remember, we don’t have foreign keys on either table. We will now create a pivot table. Following naming conventions, it will be called role_user.

php artisan make:migration create_role_user_table

The up() in your migration file will contain the following code that defines the foreign keys for users and roles.

public function up()
{
    Schema::create('role_user', function (Blueprint $table) {
        $table->id();
        $table->foreignId('role_id')
            ->constrained('roles');
        $table->foreignId('user_id')
            ->constrained('users');
        $table->timestamps();
    });
}

Your role_user table looks like the following.

As a reminder, by default, Laravel creates INT columns as BIGINT when using id() and foreignId(). You can continue to use INT but you will have to use increments() for the primary key and the “long form” to define foreign keys that uses unsignedInteger() followed by foreign().

Next, create a model class for the role_user table.

php artisan make:model RoleUser

Edit this model class and add the following.

// app/Models/RoleUser.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class RoleUser extends Model
{
    use HasFactory;

    protected $table = 'role_user';
}

Defining $table is not necessary if you follow naming conventions but I tend to add it anyway.

Now we will add the many to many relationship between users and roles. To do this, add the following methods to the users and roles model classes.

// app/Models/Role.php

public function users()
{
    return $this->belongsToMany(User::class, 'role_user', 'role_id', 'user_id');
}

// app/Models/User.php

public function roles()
{
    return $this->belongsToMany(Role::class, 'role_user', 'user_id', 'role_id');
}

You can read more about many to many relationships at https://laravel.com/docs/master/eloquent-relationships#many-to-many. Your tables should look something like this.

With this design, users can have more than one role. For example, user1 can have an admin role and staff. User2 can be admin, staff, and manager. And so on.

User Roles One to Many

There are many ways to handle user roles in an application. One way is to create a role(s) that have many user(s) or a one to many relationship. In this scenario, we can create a role called admin and many users can have this role. We can create another role called staff and this role can belong to more than one user. Now a user cannot have more than one role so the inverse relatationship is many to one.

By default, Laravel comes with a migration file to create a users table. Running the migration file, you will get a table with the following structure.

In more recent Laravel versions, the primary key is now a BIGINT datatype. If you open the migration file, it uses $table->id();. Previously, it uses $table->increments(‘id’); and that creates a primary key that uses INT datatype. You can still use either one as of Laravel v8. Just make sure when creating foreign keys, both columns matches up. I have been using the id() to simplify creating migration files. I’m not too concerned with data size.

Next, we need to create a migration file for the roles table.

php artisan make:migration create_roles_table

This will create a migration file. The file name is prefixed with the current timestamp. The roles table has to be created prior to the users table. Migration files are run in alphabetical order. You can either rename the roles migration file to have a timestamp before the user migration file or create another migration to add the foreign after both users and roles table are created. For me, the quickest is to rename the roles migration file so that it appears “above” the user migration file when alphabetized.

Edit the roles migration class with the following code.

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

I chose not to keep the timestamp columns but you can keep them if you want. The migration file will create the following table.

Next, edit the users migration file and add the role_id foreign key. Add the code below within the up(). I normally place my foreign keys under the primary key.

// CreateUsersTable class
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->foreignId('agency_id')->constrained('agencies');
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

This will create the foreign key constraint between the users and roles table.

It is a good idea to create the relationships in your model classes as well. Create a model class for your roles table.

php artisan make:model Role

Edit the Role.php model class file.

// app/Models/Role.php

class Role extends Model
{
    use HasFactory;

    protected $table = 'roles';              // defines table name
    public $timestamps = false;         // no created_at or updated_at columns

    /**
     * Has many users.
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function users()
    {
        return $this->hasMany(User::class);
    }
}

You will need to define the relationship in your User model class.

// app/Models/User.php

/**
 * Relationship to role.
 * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
 */
public function role()
{
        return $this->belongsTo(Role::class);
}

That’s all you need to do in terms of create users and roles relationship. The next step is to create a way to authorize users based on roles. You can either use Laravel’s Gates and Policies. Or you can create your own middleware.

Laravel Dev Environment in Ubuntu 18.04

It’s not often I have to set up a dev enviroment but when I do, I tend to blog about it so I can refer to it again if I need to. This time I will be creating a dev environment on Ubuntu Desktop 18.04. I will be installing the following.

  • PHP 7.2.x
  • MySQL Community Server 5.7.x
  • Composer
  • Node
  • Laravel Installer
  • PHPStorm

I will not use Apache or Nginx. I will be using PHP’s built-in web server for testing and debugging.

Installing PHP 7.2.x

I like using packages made by Ondřej Surý. You will need to add his PPA. This will give you the ability to use different versions of PHP.

sudo add-apt-repository ppa:ondrej/php
sudo apt-get update
sudo apt-get install php7.2 php7.2-cli php7.2-mysql php7.2-zip php7.2-mbstring php7.2-xml

# check version of php
php -v

php_version.png

Installing MySQL

Installing MySQL is straight forward. You install MySQL server then you run the secure installation process. Just answer the questions and that will do it.

sudo apt-get install mysql-server

#run secure installation
sudo mysql_secure_installation

Just follow the prompts and and answer the questions. Being it’s a dev environment I don’t enable the password validation plugin. Enter your password you will use as root. I do remove any test related stuff and remote connection. Reload privileges.

The odd thing is root won’t have access unless you run it with sudo.

sudo_mysql.png

As you can see from the screenshot above, you will get an access denied unless you use sudo.

If you don’t want to use terminal, then I would suggest install Workbench.

#install mysql workbench
sudo apt-get install mysql-workbench

You might run into the issue as explained above. One thing you can do is create a new user but essentially it will have the same priveleges as root. I guess you can increase security by using a username that is not commonly used to define such an account. But it’s still decreasing security.

CREATE USER 'admin'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON . TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

I used admin as an example but you can use whatever you like. You can also omit the GRANT OPTION if you wish. Open Workbench and edit the connection to match the new user and you should be able to log in to MySQL. Check out this article. I got some answers from here https://askubuntu.com/questions/773446/unable-to-connect-via-mysql-workbench-to-localhost-in-ubuntu-16-04-passwordless.

Installing Composer

Installing composer is easy. Just head on over to https://getcomposer.org/download/ and follow the instructions. I would move the composer.phar and rename it to /usr/local/bin/composer.

sudo mv composer.phar /usr/local/bin/composer

Test command with…

composer about

Running composer about should tell you about composer and should tell you that the path is accessible throughout the system.

Install Laravel via Composer

Now that you have composer installed, you can use it to install Laravel installer.

composer global require "laravel/installer"

Next we need to add the composer vendor bin director to our path. Edit your ~/.profile file.

pico ~/.profile

Add the following…

export PATH=~/.config/composer/vendor/bin:$PATH

Save it and source the file.

source ~/.profile

You can now create a new app with the following command.

laravel new test

It should install the Laravel framework on the test directory. Go into the folder and run the built-in PHP web server.

php artisan serve

You will now see the website by opening your browser to http://localhost:8000.

laravel_new_browser.png

For bonus points, you can create an alias so you don’t have to keep typing php artisan. Edit your ~/.bashrc file and add the following.

alias pa='php artisan'

After saving, don’t forget to source the .bashrc file. Now you should be able to run pa and it will run php artisan for you. You can create different aliases to help your development workflow.

Laravel Homestead on Windows Issues

I really want to use Windows and Laravel Homestead as a dev environment so that I don’t have to keep pulling out my Macbook Pro when I’m at home. I have a 2015 iMac at home but I do want to utilize the better hardware on my PC. No matter how many times I try, I just can’t get it to work.

This post is meant for me to have notes on my latest attempts and results. Many seem to have success with Homestead and Windows. Laravel commands run fine but when it comes to NPM commands it just gives me problems.

The latest error is…

npm ERR! path /home/vagrant/myproject/node_modules/fsevents/build/Release/.deps/Users/eshanker/Code/fsevents/lib
npm ERR! code ETXTBSY
npm ERR! errno -26
npm ERR! syscall rmdir
npm ERR! ETXTBSY: text file is busy, rmdir ‘/home/vagrant/a1careportal/node_modules/fsevents/build/Release/.deps/Users/eshanker/Code/fsevents/lib’

npm ERR! A complete log of this run can be found in:
npm ERR! /home/vagrant/.npm/_logs/2018-01-27T18_23_45_986Z-debug.log

Some solutions say to use sudo but you’re not really supposed to so I did the solution found at https://docs.npmjs.com/getting-started/fixing-npm-permissions. That did not work so I used sudo anyway and still a problem.

I ran the command with sudo, –no-bin-links, and –no-optional. I even followed suggestions from https://stackoverflow.com/questions/45678817/error-etxtbsy-text-file-is-busy-on-npm-install. Still issues.

Once again, I have wasted an entire morning. I really want to use my PC hardware but don’t want to dual boot on Ubuntu. I have a spare Dell Inspiron running Ubuntu and I have my iMac and Macbook Pro. I guess I just want it to work. Oh well, when I get the itch to look into it again, I guess I’ll find another morning to waste time on it.

Update 2018-01-01: Problem Solved

I finally fixed the problem. I updated the package.json using code from another project. I noticed there was a new entry called “config”. I ended up copying the “scripts” and “config” section. Here’s what I have below.

"scripts": {
    "dev": "node node_modules/cross-env/dist/bin/cross-env.js NODE_ENV=development node_modules/webpack/bin/webpack.js --progress --hide-modules --config=node_modules/laravel-mix/setup/webpack.config.js",
    "watch": "node node_modules/cross-env/dist/bin/cross-env.js NODE_ENV=development node_modules/webpack/bin/webpack.js --watch --progress --hide-modules --config=node_modules/laravel-mix/setup/webpack.config.js",
    "watch-poll": "node node_modules/cross-env/dist/bin/cross-env.js NODE_ENV=development node_modules/webpack/bin/webpack.js --watch --watch-poll --progress --hide-modules --config=node_modules/laravel-mix/setup/webpack.config.js",
    "hot": "node node_modules/cross-env/dist/bin/cross-env.js NODE_ENV=development node_modules/webpack-dev-server/bin/webpack-dev-server.js --inline --hot --config=node_modules/laravel-mix/setup/webpack.config.js",
    "production": "node node_modules/cross-env/dist/bin/cross-env.js NODE_ENV=production node_modules/webpack/bin/webpack.js --progress --hide-modules --config=node_modules/laravel-mix/setup/webpack.config.js"
  },
  "config": {
    "webpack": "node_modules/laravel-mix/setup/webpack.config.js"
  },

Finally, Homestead works on Windows 10.

Laravel Development on Windows 10 Using Linux Subsystem

This is a guide on how to configure Windows 10 using the Fall Creators Update released in October 2017. I will be using Ubuntu for the Linux Subsystem.

Ubuntu

  1. Turn on Windows Subsystem for Linux in the Windows Feature
    windows_feature_wsl
  2. Restart Windows so it fully installs it
  3. Then go to the Windows Store app and download and install Ubuntu
    ubuntu_windows_store
  4. Launch it from the Windows Store screen. Terminal will open and will ask you questions to configure the environment such as username and password.
  5. You should update Ubuntu with the usual commands
    sudo apt-get update
    sudo apt-get upgrade -y
  6. Install build essentials
    sudo apt-get install build-essential -y

PHP7

Now your environment is up to date. The next step is to get PHP7 installed. Depending on the version of Ubuntu, you may not have access to the latest version of PHP. Just add a repository and install it. For this guide, I will be install PHP7.1.

  1. sudo apt-get install -y python-software-properties
  2. sudo add-apt-repository -y ppa:ondrej/php
  3. sudo apt-get update -y
  4. sudo apt-get install -y php7.1 php7.1-cli php7.1-common php7.1-mbstring php7.1-gd php7.1-mysql php7.1-mcrypt php7.1-zip php7.1-xml

NodeJS

  1. curl -sL https://deb.nodesource.com/setup_8.x | sudo -E bash –
  2. sudo apt-get install -y nodejs

Composer

Instructions can be found at https://getcomposer.org/download/. Please note, step 2 below changes. The hash value is generated automatically. Step 5 will move and rename the command so it is accessible throughout the environment. Step 6 will test it if it works.

  1. php -r “copy(‘https://getcomposer.org/installer&#8217;, ‘composer-setup.php’);”
  2. php -r “if (hash_file(‘SHA384’, ‘composer-setup.php’) === ‘[this is where the hash goes – do not copy this verbatim – go to the link above’) { echo ‘Installer verified’; } else { echo ‘Installer corrupt’; unlink(‘composer-setup.php’); } echo PHP_EOL;”
  3. php composer-setup.php
  4. php -r “unlink(‘composer-setup.php’);”
  5. sudo mv composer.phar /usr/local/bin/composer
  6. composer about

Laravel

Please be careful with copying and pasting. The quotes sometimes gets pasted with a different font – thus ending in a different result. After installation, make sure you add the path to your profile file (~/.profile) with step 3. If there’s already a line that exist, just add it. Keep in mind that each path is separated by a colon (:) and ends with $PATH.

  1. composer global require “laravel/installer”
  2. pico ~/.profile
  3. export PATH=~/.config/composer/vendor/bin:$PATH
  4. source ~/.profile

MySQL Server

Install MySQL server and follow the on-screen instructions. If the server is not started or you get a mysqld.sock error, you probably need to start the service. The command is in step 3.

  1. sudo apt-get install mysql-server -y
  2. sudo service mysql start
  3. sudo mysql_secure_installation

To help manage your database, download MySQL Workbench from https://dev.mysql.com/downloads/workbench/. You may need to download some prerequisite software from Microsoft.

mysql_workbench_requirements
Click the OK button

mysql_workbench_download_prerequisites
Click the Download Prerequisites button

Click on the Download Prerequisites button and it will take you to https://dev.mysql.com/resources/wb62_prerequisites.html. This page will list a couple of software but all I download is the Visual C++ Redistrubatble for Visual Studio 2015. Meeting that requirement seems to be enough for me. I download the 64bit version to match my operating system. Once installed, run the MySQL Workbench installer again and you should have no problem.

Create a new connection. The default values should work. All you have to do is provide the password.

mysql_connection_test
Connect to your new MySQL Server

Make sure that Ubuntu is running and that the MySQL service is also running or you won’t be able to connect.

That’s about it. I’m glad WSL is out of beta. So far it’s been running well.

I tried Virtualbox with Ubuntu workstation and after the Creator’s Update, it stopped working. I can log in but it won’t load the desktop on the console window. Oddly enough, the preview window shows the desktop loaded.

I also tried Homestead but I get an error with the shared folder. I run npm commands and it tells me that it can’t delete and write because of permission denied. I also tried Hyper-V and Ubuntu workstation is horrible on it. Moving the mouse lags. I’ve given it 2 CPU and 6GB of ram. I used different drivers – nothing.

I also posted a different way to develop Laravel on Windows check it out here. It works for the most part but it’s not as clean with SSH keys and GIT repositories. That article is actually pretty popular based on my analytics. I’m a bit surprised since I don’t really recommend using Windows for development unless you’re using Microsoft technology. It’s just a huge headache. We’ll see how well this environment last. Fortunately for me I have Apple computers and can run Ubuntu on spare hardware. This guide is just for my curiousity and to help others. Also, I like being able to use “better” hardware than my Apple devices.

Laravel 5.4 with Foundation 6.3

I wanted to share how I got Foundation Site 6.3 to work with Laravel 5.4 and webpack. I tried this on a brand new Laravel project.

First, edit the package.json file and replace the bootstrap-sass entry with

"foundation-sites": "^6.3.1"

Then run npm install. This will download all the dependencies and store them in the node_modules directory. Inside this directory, you will find the foundation-sites directory. You will need to copy the _settings.scss from there to the resources directory.

cp node_modules/foundation-sites/scss/settings/_settings.scss resources/assets/sass/

Edit the _settings.scss file. You will need to modify the path to the util.

@import 'node_modules/foundation-sites/scss/util/util';

Edit the resources/assets/sass/app.scss file by removing the @import bootstrap entry then entering the following code.


@import "settings";
$global-flexbox: true; // optional if you want to enable flexbox globally
@import "node_modules/foundation-sites/scss/foundation";
@include foundation-everything(true);

Edit the resources/assets/js/bootstrap.js file.


require('bootstrap-sass'); // comment this out or remove
require('foundation-sites'); // add this

Edit webpack.mix.js file.


// remove the following code
modify mix.js(...)
.sass(...);


// replace it with this
mix.sass('resources/assets/sass/app.scss', 'public/css');
mix.combine([
'node_modules/jquery/dist/jquery.min.js',
'node_modules/foundation-sites/dist/js/foundation.min.js'
], 'public/js/app.js');

Now you can run npm run dev to compile your webpack. Only thing left is to add the following javascript on your page.

$(document).foundation();

This was done on Laravel 5.4.27 and Foundation-Site 6.3.1.

Configuring Windows Server 2012 to Run a Laravel Application

My latest project required me to use the Laravel framework and use a MSSQL Server backend. To do this, I will be using Windows Server 2012, IIS, and MSSQL Server 2012.

Installing IIS

First thing is make sure every thing is up to date. If it’s a brand new server, you will need to install and configure IIS.

  • Under Server Roles, select Web Server (IIS).
  • Look for CGI under Web Server / Application Development.
  • We will leave everything else to default values. Just continue to the next windows until it starts to install. Check and see if there are any updates after you install IIS.

You need to install an extension called URL Rewrite. You  can find details at https://www.iis.net/downloads/microsoft/url-rewrite. The download link is at the bottom. Select the x64 or x86 depending on the architechture you are running.

PHP

  • PHP 7.0: We will be using the x64 Non Thread Safe version since we will be running PHP in Fastcgi mode. Unzip this into C:php. You can name this whatever you want. I usually leave version numbers as part of the folder name just in case I need to upgrade or run different versions. It’s your call on what to name it.
  • You will need to copy either the development or production .ini file and save it to C:Windows. Because of its location, you many need to run your text editor (like Notepad) with admin permissions every time you need to make changes to the file. Here are the changes.
    • extension_dir = “ext” (enable this if you’re going to use extensions)
    • fastcgi.impersonate = 1 (enable this)
    • cgi.fix_pathinfo=1 (enable this)
    • cgi.force_redirect = 1 (enable this)
  • Download the MSSQL driver for PHP. I will be using version 4.0 for this guide. Extract it to a folder. Look for the file name php_pdo_sqlsrv_7_nts_x64.dll and move it to C:phpext folder. Rename it to php_pdo_sqlsrv.dll.
  • You will need to install the ODBC driver to work with the SQLSRV driver above. Since our OS is 64bit, use that version or you may get an incompatible error during install if you try to use the 32bit version.
  • Optional: You can add the PHP executable to your environment variables. Just add C:php. By doing this, you can use the php command throughout Command Prompt. You can test it by running php -v command and it should show you the version of PHP you are running.

If you encounter the VCRUNTIME140.dll is missing error, please download the component from https://www.microsoft.com/en-us/download/details.aspx?id=48145. Again, I used the 64bit version to match what I’ve installed so far.

windows_server_2012_vcruntime140dll_error

Back to editing the php.ini file. Enable the following extensions in your php.ini file.

  • php_mbstring.dll
  • php_openssl.dll
  • php_odbc.dll
  • php_pdo_sqlsrv.dll (Please note, this is not in the ini file and you will have to add it.)

Save the file.

Configuring IIS

I have posted a guide on running WIMP (Windows, IIS, MySQL, PHP) and I’ll be using the settings from this guide as well here. Now we need to let IIS know how to serve PHP pages. Open up IIS Manager. Highlight the entire Server to apply to all websites hosted or specific ones – in this case, highlight Computer/Server name below Start Page. Next, double-click on Handler Mappings and Add Module Mappings. Here’s how mine looks.

 

windows_server_2012_module_mapping

You will then be asked if you want it to create a FastCGI application, yes.

windows_server_2012_module_mapping_executable

That should be it for IIS for now.

Composer

If you don’t have Composer installed, do so now by using the Windows installer from their website. This application requires openssl, which is why we enabled it during the PHP configuration. You can test the installation by running composer about in the Command Prompt.

NodeJS

If you don’t have NodeJS installed, do so now by using the installer from their website. I’m using version 6.0+ and the 64bit flavor for this article. This is optional and is not a requirement to run Laravel applications but it makes things easier. You can test the installation by running node -v and/or npm -v.

Create New Laravel Project

Use composer to create a new project using Laravel. Go to a folder where you’d like to store projects then run the following command.

composer create-project laravel/laravel test

Composer will pull all the dependencies down and create a new project inside the test folder. Once completed, right-click on the test folder and select Properties. Under the Security tab, add IIS_IUSRS and IUSR with the following permissions.

  • Read & execute
  • List folder contents
  • Read
  • Write

It should apply throughout the subfolders as well.

Run Notepad as administrator and open C:WindowsSystem32driversetchosts. In Notepad’s Open dialog window, make sure you have All Files (.) selected or you won’t see this file. At the bottom of the file, add the following entry.

127.0.0.1                 test.dev

Back to IIS, we will Add Website. The Site name and Host name that I used is test.dev. The Physical path is the public folder of the application.

iis_new_website

Check if the web.config imported the values in .htaccess found in the public folder. While the new website, test.dev, is highlighted, look for URL Rewrite and open it. If you don’t see imported rules, just import the rules from the .htaccess file.

Finally, open Default Document and add index.php. You should now be able to view the default Laravel home page by visiting http://test.dev in your browser.

You can test the URL Rewrite by adding the following code to /routes/web.php.


Route::get('/about', function() {
return 'hello world';
});

Then visit http://test.dev/about. You should see “hello world”.

MSSQL Server 2012

For this article, I am using MSSQL Server 2012. Open /config/database.php file. Change the default value from mysql to sqlsrv. Add the following connection.


'sqlsrv' => array(
'driver' => 'sqlsrv',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'prefix' => '',
),

Open the .env file and provide your database credentials and information. Create the database in MSSQL server. If everything works as they should, you can run the php artisan migrate command in Command Prompt and it should create the migrations, users, password_resets tables in the database.

That should do it. You now have Laravel app with MSSQL database backend running in IIS and Windows Server.

Laravel Episode 11: Updating My Profile


episode 10

Episode 11: Updating My Profile

 

  • Changes in between episode 10 and 11
    • Cleaned up code in routes file.
    • Go through code and replaced Role::userHasRole() with User::hasRoles().
    • Removed __construct() from ProductController.
    • Update database diagram to match current database schema.
  • Create route for /my-profile.
    • Create new route group for auth middleware.
  • Create methods in UserController.
    • myProfile()
    • myProfileUpdate()
      • Make sure to show that the password field is optional.
  • Create view users/my_profile.blade.php.
  • Create link in navbar for my profile interface.