Martin Carlin

Laravel Excel - Format Columns

If you are using Laravel Excel to generate a template (i.e. a file with a header row and no other content), then you might find it tricky if you are looking to format all cells in each of the columns.

This is because the formatting is only applied to exported rows, which in this use case is only the header, so only the first row would have the formatting applied and all other cells would be General formatted.

In my situation, I wanted all columns to be Text to avoid dealing with dates in Excel and any other funny business, basically, what is in the template when it is uploaded is what will be imported.

At first, I had:

use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;

class MyClass implements WithColumnFormatting
{

    public function columnFormats(): array
    {
        $lastColumn = Coordinate::stringFromColumnIndex(count($this->headings()));

        return [
        	'A1:' . $lastColumn . '1000' => NumberFormat::FORMAT_TEXT,
        ]
    }

}

The downside to this approach is hardcoding the number of rows you want to be formatted, 1000 in this case. This obviously isn't ideal as no doubt there would come a time when more rows are imported that have been formatted.

The solution is to replace the above with:

use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class MyClass implements WithStyles
{

    public function styles(Worksheet $sheet): array
    {
        $lastColumn = Coordinate::stringFromColumnIndex(count($this->headings()));

        return [
            "A:$lastColumn" => ['numberFormat' => ['formatCode' => NumberFormat::FORMAT_TEXT]],
        ];
    }

}

If you are using Laravel Excel to import, you might find that you need to throw a manual exception not covered by your validation rules, here's how to do it:

Assuming we were looking for a $department model in our database based on a query that we had just executed:

if (!$department) {

    $error = ['Could not find department'];
    $failures[] = new Failure($currentRowNumber, 'department', $error, $row);

    throw new \Maatwebsite\Excel\Validators\ValidationException(\Illuminate\Validation\ValidationException::withMessages($error), $failures);
}

The exception thrown will now be consistent with the validation exception thrown by the package itself, e.g.:

try {
    $import->import($file);
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {

    $failures = $e->failures();

    foreach ($failures as $failure) {
        $errors[] = [
            'row' => $failure->row(),
            'attribute' => $failure->attribute(),
            'errors' => $failure->errors(),
        ];
    }
}

I was looking for a way in deployer to install the composer dev dependencies on a staging site but without affecting deployments to the live site.

I managed to get it working using the below. At first it failed because I was missing install which I thought was strange since I assumed that only the options would be changed and not the action of the composer command.

deploy.php

host('staging.mydomain.co.uk')
    ->stage('staging')
    ->set('branch', 'staging')
    // remove --no-dev flag for staging
    ->set('composer_options', 'install --verbose --prefer-dist --no-interaction --optimize-autoloader')
    ->set('deploy_path', 'path/to/my/site');

I have researched the topic a few times and never really found a good way to achieve having a Laravel application running alongside an existing legacy application.

The benefits for doing it this kind of side-loading way would be so that a section at a time can be ported across instead of actively maintaining two different projects and trying to keep the logic in sync with each other.

This guide assumes that there is no existing single entry point/front controller and no routing, e.g. that the url mysite.com/path/to/my/script/index.php is how the current site behaves.

composer global require laravel/installer

laravel new [directory name]

It might be easier to use a different directory from the project you want to use Laravel with and copy the files and directories over one at a time.

Create an .env file from the .env.example.

Create an app key:

php artisan key:generate

Add the database connections that you need, e.g.:

DB_CONNECTION=db
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=my_main_app_db
DB_USERNAME=root
DB_PASSWORD=

SECONDARY_DB_CONNECTION=secondary_db
SECONDARY_DB_DRIVER=mysql
SECONDARY_DB_HOST=localhost
SECONDARY_DB_PORT=3306
SECONDARY_DB_DATABASE=my_secondary_app_db
SECONDARY_DB_USERNAME=root
SECONDARY_DB_PASSWORD=

If you only have one database then this will be even easier.

Then ensure that these are also in config/database.php:

'db' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'my_main_app_db'),
    'username' => env('DB_USERNAME', ''),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'latin1',
    'collation' => 'latin1_swedish_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

'secondary_db' => [
    'driver' => 'mysql',
    'url' => env('SECONDARY_DATABASE_URL'),
    'host' => env('SECONDARY_DB_HOST', '127.0.0.1'),
    'port' => env('SECONDARY_DB_PORT', '3306'),
    'database' => env('SECONDARY_DB_DATABASE', 'my_secondary_app_db'),
    'username' => env('SECONDARY_DB_USERNAME', ''),
    'password' => env('SECONDARY_DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'latin1',
    'collation' => 'latin1_swedish_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

and set the default database connection:

'default' => env('DB_CONNECTION', 'my_main_app_db'),

Be careful of the charset and collations above, if you don't use those then change them to what you currently use, e.g. utf8mb4 and utf8mb4_unicode_ci.

If composer and npm are already being used within the project, you will also need to consolidate composer.json and package.json in order to get the Laravel dependencies installed along with the currenty dependencies of the project.

Create or update .htaccess in your project vhost entry point:

Options +FollowSymLinks
RewriteEngine on

# Redirect Trailing Slashes If Not A Folder...
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_URI} (.+)/$
RewriteRule ^ %1 [L,R=301]

# Send Requests To Front Controller...
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^ laravel.php [L]

Create laravel.php in the same directory as the .htaccess file above and insert the following:

<?php

require_once __DIR__ . '/../public/index.php';

Add the following to routes.web.php

/* Fallback route */
Route::any( "/{path?}", [RouteController::class, 'oldRoute'])->where('path', '.*');

Create a RouteController - php artisan make:controller RouteController

Open it app and add the oldRoute method:

/**
 * Include file at the given path
 *
 * @param  String $path
 * @return Response
 */
public function oldRoute($path)
{
    ob_start();

    if (file_exists(base_path('entrypoint-directory/' . $path))) {
        include base_path(sprintf('entrypoint-directory/%s', $path));
    } elseif (file_exists(base_path('public/' . $path))) {

        $publicPath = base_path(sprintf('public/%s', $path));

        header(sprintf('Content-Type: %s', mime_content_type($publicPath)));
        readfile($publicPath);
    } else {
        abort(404, 'Page not found');
    }

    return response(ob_get_clean());
}

Update Http/Middleware/Authenticate.php to point to the login url of the application, e.g.

// return route('login');
return 'login.php';

Make the custom auth middleware:

php artisan make:middleware CustomAuth

Set the contents of the handle method to:

$user = User::getLoggedInUser();

// Login the user manually using Laravel's Auth Facade
// don't use `Auth::login($user, true);` unless `remember_token` is added to the users table.
Auth::login($user);

// set the user for the current request
$request->setUserResolver(function() use ($user) {
    return $user;
});

return $next($request);

Open app/Http/Kernel.php and change the auth array entry so that it reads:

'auth' => \App\Http\Middleware\CustomAuth::class,
// 'auth' => \App\Http\Middleware\Authenticate::class,

Add a getLoggedInUser method to the User class:

public static function getLoggedInUser()
{

    if (!isset($_SESSION['user']['id'])) {
        return null;
    }

    // check if we want to impersonate someone
    if (isset($_SESSION['impersonate'])) {
        $user = self::find($_SESSION['impersonate']);
    } else {
        $user = self::find($_SESSION['user']['id']);
    }

    if (empty($user)) {
        self::logout();
        return null;
    }

    return $user;
}

Also, make sure the User class is using the correct database connection if you have more than one database. This needs to be correct for each of your models if  that is the case.

protected $connection = 'db';

That's it! Your legacy app should continue to operate as normal and now any new Laravel based routes should work alongside the existing application.

Filter Sensitive Data from Whoops

The Whoops composer package comes as standard with the Laravel framework, but you can actually use it standalone in non-Laravel projects.

$whoops = new \Whoops\Run;
$whoops->prependHandler(new \Whoops\Handler\PrettyPageHandler);

$whoops->register();

The above should only run in local/non-production environments.

However, if you are part of a team and you have some kind of error monitoring, you might want to filter out sensitive information such as your passwords.

Laravel has a nice way to do this, but to do it without the framework it's a bit harder to find out how to achieve it. After some digging, it is possible by adding something like:

$whoops->pushHandler(function($exception, $inspector, $run) {
    isset($_POST['password']) ? $_POST['password'] = '👾 [FILTERED] 👾'; 
});

How you achieve this depends entirely on how your application is structured, but the best place is more than likely the same place that the composer require is used.

The whole example would look like:

$whoops = new \Whoops\Run;
$whoops->prependHandler(new \Whoops\Handler\PrettyPageHandler);

$whoops->pushHandler(function($exception, $inspector, $run) {
    isset($_POST['password']) ? $_POST['password'] = '👾 [FILTERED] 👾'; 
});

$whoops->register();

Compile SASS Using GitLab Pipelines

ci

Since using GitLab pipelines I have been committing the compiled css from my SASS files and then doing an rsync from GitLab CI in order to deploy the changes I make to my website, but this has always annoyed me as before I used to compile the SASS on the fly, but I have finally managed to make it work like so:

stages:
    - compile
    - deploy
    
compile:
  stage: compile
  image: node:8.15-alpine
  script:
    - yarn global add node-sass
    - node-sass ./content/themes/carlin/assets/sass/input.scss ./content/themes/carlin/assets/css/screen.css --style compressed
  only:
      - master
  artifacts:
    paths:
      - ./content/themes/carlin/assets/css

which you'd then use with your own deploy stage.

The reason for using yarn is that I experienced issues trying to use npm.

The artifacts makes the generated css file available in subsequent stages, nice!

Hope it helps someone.

I made a gist which is also embedded below:

I recently had to deal with migrating legacy systems to a Laravel 5.4 app, as you might have guessed, the legacy app was using sha1 and not bcrypt.

Here is how I managed to silently update sha1 passwords to the more secure bcrypt version:

<?php

namespace App\Listeners;

use Illuminate\Auth\Events\Attempting;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;

use App\User;

use Hash;

class CheckOldHashedPassword
{
    /**
     * Create the event listener.
     *
     * @return void
     */
    public function __construct()
    {
        //
    }

    /**
     * Handle the event.
     *
     * @param  Login  $event
     * @return void
     */
    public function handle(Attempting $event)
    {
        $user = User::where('email', $event->credentials['username'])->first();

        if (!empty($user) && $user->password === sha1($event->credentials['password'])) {
            // update password
            $user->password = Hash::make($event->credentials['password']);
            $user->save();
        }
    }
}

Just created the above file in app\Listeners.

Depending on the name of your login field, you might need to change the array key being used in $event->credentials, possibly username or email.

If you are using md5 or something else, then you can easily add in the checks that you need.

Save yourself the time, if you are trying to submit a form via ajax using FormData, it doesn't work with put requests, change the request type to post and it should be fine.

Just a quick post about how to use Laravel Echo without the npm package.

The way I did it was to add it to my local project as per the docs:

npm install --save laravel-echo pusher-js

Then I copied the node_modules/laravel-echo/dist/echo.js file to my public/vendor directory.

Then I just updated my scripts partial to include the Echo js file and the Pusher library:

<!-- Echo -->
<script>
  var module = { };
</script>

<script src="{{ cached_asset('vendor/echo.js', true) }}"></script>

<!-- Pusher -->
<script src="{{ cached_asset('vendor/pusher.min.js', true) }}"></script>

<script>
  window.Echo = new Echo({
    broadcaster: 'pusher',
    key: '{{ config('broadcasting.connections.pusher.key') }}',
    cluster: 'eu',
    encrypted: true
  });
</script>

The var module = { }; is needed because how it's meant to be used with webpack I think, I picked that up from a Stack Overflow answer.

I've been trying to figure this out for a while before I cracked it:

Users Controller

use Illuminate\Foundation\Auth\SendsPasswordResetEmails;

class Users extends Controller
{

    use SendsPasswordResetEmails;

    public function resetPassword(Request $request, $id)
    {
        $this->sendResetLinkEmail($request);

        // your response
    }

The form I used in my view is

<form id="password-reset-form" action="/users/{{ $user->id }}/reset-password" method="put">
   {{ csrf_field() }}
   <input type="hidden" name="_method" value="put">
   <input type="hidden" name="email" value="{{ $user->email }}">
   <button type="submit">Reset Password</button>
</form>

Then all you need is a route

Route::put('users/{id}/reset-password', 'Users@resetPassword');

Apologies if this is obvious, but thought it could possibly help others. I had this link on a product detail page to redirect back to the results page:

<a href="{{ URL::previous() }}">Search Results</a>

but after the page reload triggered by adding the item to the basket, that link would go 'back' to the same page (not to mention the possibility of navigating to the page from somewhere other than the results page). I couldn't think of any way around it but managed to fix it by updating the controller method for the search results page. The items are fetched using the inbuilt paginator:


/* query logic */

$items = $items->paginate(15);

$url = $items->url($items->currentPage()); // e.g. /some-url?page=1

// then append all the get params to the string from above
foreach ($request->all() as $key => $value) {
    $url .= '&' . $key . '=' . $value;
}

$request->session()->put('results-route', $url);

then use <a href="{{ Session::get('results-route') }}">Search Results</a> instead.

Not sure if there's a neater way but chuffed I managed to come up with a solution, had no idea how I was going to fix that.

I was using the excellent slick Carousel and needed to implement next and previous buttons to move the carousel in the desired direction, but also had to show and hide these buttons depending on the current slide as I didn't want to use the infinite functionality in this scenario.

First off, initialise the plugin using your desired options, I'd say that these are the minimum requirement but in this case I also had arrows set to false, dots set to true and was also using the lazyLoad option:

$('.steps').slick({
    infinite: false,
    slidesToShow: 1,
    slidesToScroll: 1
});

Then use the slick api to move the carousel when buttons are clicked:

$('.next').click(function() {
    $('.steps').slick('slickNext');
});

$('.previous').click(function() {
    $('.steps').slick('slickPrev');
});

and, finally:

$('.steps').on('beforeChange', function(event, slick, currentSlide, nextSlide) {

    if (nextSlide == slick.slideCount - 1) {
        $('.next').hide();
        $('.previous').show();
    } else if (nextSlide != slick.slideCount - 1 && nextSlide != 0) {
        $('.next').show();
        $('.previous').show();
    } else if (currentSlide == 0 || nextSlide == 0) {
        $('.next').show();
        $('.previous').hide();
    }

});

I used beforeChange because using the afterChange meant the showing and hiding didn't happen until what felt like to be too late, since the new slide has to be in place before that code is executed.

Update

After writing the first version of the post, I wanted to go back to have another look at it as I wasn't quite happy.

I was running php vendor/bin/dep for deploys since I installed Deployer as a local package.

I changed that by installing globally composer global require deployer/deployer.

Then I could use dep instead of php vendor/bin/dep.

I then removed all of the tasks that were already defined in the Deployer Laravel recipe from my script to avoid cluttering. I didn't even notice that include before so I'm glad I went back to have another look at this.

After that I setup some variables at the top to avoid any confusion over what needed updating for individual use cases.

I also followed the Laravel recipe and included the .env in the shared_files array.

The biggest change after that was to simply upload the public/build directory and its contents over having to do npm install and then gulp --production.

This makes the deploy significantly quicker and it also saves you having to install node and npm on production, as well as not having a node_modules directory for every release which would take up a significant amount of room.

I was still having issues with the bootstrap/cache folder after my re-shuffle, so I removed that line from the top level .gitignore (can't remember if it was me that added that or not, but looking at Laravel's .gitignore it must have been me) and that helped and saved needing a further task in the deploy. There is already a .gitignore in the bootstrap/cache directory so that nothing in there will be version controlled, but just having the empty directory saved endless amounts of pain.

The final piece of the puzzle was to run chmod g+s <directory> to make files inherit group to make files inherit the group from the parent directory (my parent directory had martin:nginx as the owner and group). Without that, all the newly deployed files were martin:martin which didn't work for my configuration since nginx is running as .... nginx.

New gist:


I recently tried using Deployer for the first time to setup a deployment process for a Laravel 5 project, rather than using something like Capistrano that would require installing ruby and gems rather than just adding a Composer package.

This gist represents hours of heartbreaking errors trying to figure out why things weren't working, the main one being the first task:

/**
 * Create cache folder in bootstrap directory
 */
task('deploy:cache_folder', function() {
  run('mkdir {{release_path}}/bootstrap/cache');
})->desc('Manually create cache folder');

I was getting an error saying that bootstrap/cache/services.php didn't exist when trying to run php artisan optimize. The solution seems to be creating the bootstrap/cache directory but of all the tutorials and blog posts I read, none mentioned it so I'm not sure if this is a recent change since the .gitignore file has /bootstrap/cache in it but a 5.1 project I have doesn't.

I think the rest is fairly self-explanatory, just replace the placeholders in the square brackets, the curly brackets are actually used by Deployer so don't change those!

This example is made a bit more complicated due to using elixir for assets, so that's why we have to have a task that runs npm install.

I also wasn't sure what to do with the .env file since it's not in source control and obviously your local one will be based on a local environment, not production, so I decided just to create a .env.production file that gets uploaded and renamed to .env. The only problem I can see there is that if you are part of a team then you'd need to communicate any changes somehow since it's not meant to be version controlled.

To deploy, run dep deploy.

If you have any suggested improvements then I'd love to hear them.

Rails Tips

Use helper functions in models and controllers

Sometimes you just need to use ActionView functions in your models or controllers.

A way to achieve this is to include them like so:

include ActionView::Helpers::TextHelper

but a nicer approach seems to be to use the function directly like so:

ActionController::Base.helpers.[FUNCTION_NAME] (arguments)

Better pagination - avoid multiple queries

First, do your query as normal but with one difference.

results = ModelName.select('SQL_CALC_FOUND_ROWS model.*')

and then immediately after, run the following:

total = ModelName.connection.execute('SELECT FOUND_ROWS()')

To get the actual total number, you can use .first on the total object.

This works even when using limit and offset in the original query.