Solving the Repetitive Query Dilemma: Harnessing the Power of Laravel Eloquent Macros

Have you ever found yourself writing the same complex database queries repeatedly? Yeah, me too. It’s frustrating, time-consuming, and, let’s face it, not the best use of our coding superpowers.

I needed to search some tables for case-insensitive values and discovered that Laravel doesn’t have a native function in eloquent, or it’s entirely possible that I missed it. Today, we will tackle this problem head-on by diving into the world of Laravel Eloquent macros. If you’ve never used them, this will be a game-changer for your productivity.

The Problem: Query Repetition Syndrome

Picture this: You’re working on a project where you frequently need to perform case-insensitive searches across multiple columns. Or maybe you’re constantly writing complex ordering logic to handle null values. Sound familiar?

If you’re nodding your head, you’re not alone. This is what I call “Query Repetition Syndrome,” and it’s a common ailment among Laravel developers.

The Solution: Eloquent Macros to the Rescue

Enter Eloquent macros – your new secret weapon against repetitive queries. These bad boys allow you to extend Eloquent’s query builder with custom methods. It’s like giving your Laravel app query superpowers!

Let’s break down how to implement this solution step-by-step.

Step 1: Create Your Macro Factory

First things first, we need a place to house our macros. Let’s create a new service provider:

php artisan make:provider MacroServiceProvider

Step 2: Set Up the MacroServiceProvider

Open up that freshly minted MacroServiceProvider.php, and let’s give it some structure:

<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Arr;

class MacroServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        $this->addWhereInLikeMacro();
        $this->addWhereLikeMacro();
        $this->addOrderByNullsLastMacro();
    }

    // We'll add our macro methods here
}

Step 3: Craft Your First Macro

Let’s tackle that case-insensitive search problem with a whereInLike macro:

protected function addWhereInLikeMacro(): void
{
    Builder::macro('whereInLike', function ($column, array $values, $caseSensitive = false) {
        return $this->where(function ($query) use ($column, $values, $caseSensitive) {
            $column = DB::raw($caseSensitive ? $query->getGrammar()->wrap($column) : 'LOWER(' . $query->getGrammar()->wrap($column) . ')');
            foreach ($values as $value) {
                $method = $query->getQuery()->wheres ? 'orWhere' : 'where';
                $value = $caseSensitive ? "%$value%" : '%' . strtolower($value) . '%';
                $query->$method($column, 'LIKE', DB::raw('?'));
                $query->addBinding($value, 'where');
            }
        });
    });
}

Step 4: Add More Macros to Your Arsenal

While we’re at it, let’s add a couple more handy macros:

protected function addWhereLikeMacro(): void
{
    Builder::macro('whereLike', function ($columns, $value) {
        return $this->where(function ($query) use ($columns, $value) {
            foreach (Arr::wrap($columns) as $column) {
                $query->orWhere(DB::raw('LOWER(' . $query->getGrammar()->wrap($column) . ')'), 'LIKE', '%' . strtolower($value) . '%');
            }
        });
    });
}

protected function addOrderByNullsLastMacro(): void
{
    Builder::macro('orderByNullsLast', function ($column, $direction = 'asc') {
        $column = $this->getGrammar()->wrap($column);
        $direction = strtolower($direction) === 'asc' ? 'asc' : 'desc';
        
        return $this->orderByRaw("CASE WHEN {$column} IS NULL THEN 1 ELSE 0 END, {$column} {$direction}");
    });
}

Step 5: Register Your Macro Provider

Don’t forget to tell Laravel about your new macros. Add this line to your config/app.php:

'providers' => [
    // Other providers...
    App\Providers\MacroServiceProvider::class,
],

Step 6: Unleash Your New Query Superpowers

Now for the fun part – using your new macros:

// Case-insensitive search for multiple values
$users = User::whereInLike('name', ['John', 'Jane', 'Bob'])->get();

// Search across multiple columns
$users = User::whereLike(['name', 'email'], 'john')->get();

// Order results with nulls last
$users = User::orderByNullsLast('last_login')->get();

The Result: Clean, Efficient, and DRY Code

By implementing these Eloquent macros, we’ve solved our Query Repetition Syndrome. Instead of writing the same complex queries over and over, we have reusable, eloquent (pun intended) methods that make our code cleaner and more efficient.

But here’s the kicker – this is just the beginning. You can create macros for any repetitive query pattern in your projects. The possibilities are endless!

Wrapping Up

Eloquent macros are like a Swiss Army knife for your database queries. They’re powerful and flexible and can save you tons of time and headaches.

Remember, the goal here is to work smarter, not harder. By identifying repetitive patterns in your queries and turning them into macros, you’re setting yourself up for cleaner, more maintainable code in the long run.

Now, I’m curious – what repetitive query patterns have you encountered in your Laravel projects? Please drop a comment below to share your macros or share some macro solutions!

Happy coding, and may your queries be ever elegant!

This is an update to my previous script to deploy Storybook to S3 via Gitlab Pipelines. We’re running a dev server on EC2 and I wanted to be able to deploy the project using SFTP. This assumes you already created a new user account on the FTP server.

Create an SSH key on your local machine and save it without a password using the following command:

sh-keygen -t ed25519 -C "GitLab SSH key"

You should have two new files in .ssh directory:

• id_ed25519 — private key
• id_ed25519.pub — public key

Add Key to Gitlab CI/CD Settings

Copy content of private key and go back to GitLab project. Navigate to Settings -> CI/CD -> Variables -> Expand -> Add Variable. GitLab’s variable is a key-value pair. Name key SSH_PRIVATE_KEY and paste private key in value field. Click Add Variable.

Add two more variables:

• SSH_USER — name of the user on the remote server
• SSH_HOST — IP address of remote server
• FTPFOLDERPATH – The path where to upload files too on your server.

Copy the contents of public key and go back to remote server. Login as the same user which you have specified in SSH_USER GitLab’s variable.

Add SSH Key to Server

Navigate to /home/<username>/.ssh. If directory .ssh doesn’t exist, then create it. Paste the public key into authorized_keys file. If you don’t have authorized_keys file, create it.

Add .gitlab.ci.yml

Important Note: I ended up using node:16 for my script because my instance of Storybook was using Webpack 4 and the node:latest was using Node 18 which wasn’t compatible. If you’re using webpack 5, you should be able to use a later version. I’m also using node:16 instead of alpine or buster because I ran into issues adding the SSH keys. I’ll work on optimizing this later for performance but it works.

image: node:16

stages:
  - build
  - deploy

build:
  stage: build
  script:
    - npm install
    - npm run build-storybook
  artifacts:
    paths:
      - storybook-static
  only:
    - develop
    - main
deploy:
  stage: deploy
  artifacts:
    paths:
      - storybook-static
  before_script:
    - "command -v ssh-agent >/dev/null || ( apt-get update -y && apt-get install openssh-client -y )"
    - eval $(ssh-agent -s)
    - echo "$SSH_PRIVATE_KEY" | tr -d '\r' | ssh-add -
    - mkdir -p ~/.ssh
    - chmod 700 ~/.ssh
    - ssh-keyscan $SSH_HOST >> ~/.ssh/known_hosts
    - chmod 644 ~/.ssh/known_hosts
    - ls -la
  script:
    - echo "$SSH_USERNAME@$SSH_HOST"
    # clean up old storybook files
    - ssh -p22 $SSH_USERNAME@$SSH_HOST "rm -rf $FTPFOLDERPATH"
    # deploy artifacts
    - scp -P22 -r storybook-static/* $SSH_USERNAME@$SSH_HOST:$FTPFOLDERPATH
  only:
    - develop
    - main

Up until today, everyone I know has struggled to capture a Google Analytics event on redirect links. We’ve all had to come up with hacks and workarounds to ensure that the initial landing page is tracked. Today I finally figured out a solution that works. Google analytics now offers a callback event which lets you fire the redirect (or any custom functionality after the main tracking event fires.

The snippet to add after the Google analytics tracking code setup is below:

ga('send', 'pageview', {
'page': '/my-new-page',
'hitCallback': function() {
window.location.replace = "[new url]";
}
});

The Problem

I recently ran into an issue where random devices wouldn’t connect to my WiFi while others could. None of my android devices could connect including my phone and tablet but most Windows 7 devices could. A friend brought her Windows 7laptop over and was unable to connect it to my WiFi.

I just spent two weeks troubleshooting the issue which should have been more obvious. At first I thought it was because I had setup the encryption as WPA2 and the devices didn’t

It was definitely a stupid mistake on my part but one that is easily overlooked.

The Solution

Check your WiFi settings and verify the mode. It turns out I had set the router to Wireless N only and the devices that couldn’t connect only supported up to Wireless G. I switched the router to Wireless G/N and all the devices started working.

On Verizon’s router, go to Wireless Settings -> Advanced Security Settings

Under Level 3, you’ll see “802.11b/g/n Mode”.

Following my ordeal with Ford, I started my drive home and drove past a BMW dealership. I decided to check it out and pulled into the lot, parked, and walked inside. My mother and I walked around looking at the cars and nobody walked over to help for a good 15 minutes. Finally I walked over to the front desk and the girl looked up and asked what I wanted. I said “I’m looking to purchase a BMW.” She suddenly had a smile on her face and said “OH! One second, let me see whom I can get to help you.”

The girl made a phone call and spoke softly and then followed by “uh huh. Ok.” and then looked up to me and said “One of the sales reps will be right with you.” I nodded and gave her my thanks and walked back to the car I was looking at. I couldn’t help but feel like the lack of anyone coming to help and her initial attitude was caused by some sort of racial profiling but I could totally be off base on that (but based on prior experiences, I’m more than likely correct). Finally a salesman came out, introduced himself and then asked how he could help. I explained I was looking for a 328xi to which he responded we should step into his office. He started filling out some paperwork to which I stated “Wait, we haven’t discussed anything yet. Why are you filling out forms already?” to which he replied ”This will save time. You obviously seem like you’ve decided on the car you want.” I eyed him suspiciously and after he filled in the paperwork, he excused himself and took the paperwork and walked away. I looked at my mother and whispered “I seriously think he’s running a credit check to see if I can get the car.” We were both agitated already from our previous experience not 30 minutes prior. I’ve purchased a few cars in the past, for both myself and my parents and have never encountered the type of obnoxious behaviors I’ve run into that day.

So he comes back, sans paperwork and sits down. He starts asking me what I’m looking for and is interrupted by a phone call. He says “Excuse me one second, that’s my wife.” and I happened to see that the phone was an extension internally. I listened very carefully and heard “The credit is seriously excellent. They can buy the 7 series if they wanted.” to which he replied “Wonderful! OK, I have to go, I’ll talk to you later.” and turned to us. “Sorry, she wanted to let me know dinner would be waiting for me when I got home.”

I’m going to shorten the story but we discussed what I wanted, and he said he had the car in the lot and made me an offer. I countered and he looked at me and said “This is BMW. We don’t negotiate.” I was totally surprised and countered with “I know for a fact that’s not true as four people I know negotiated before they bought their car.” He said “They might have got a good special, but the best I can do for you is give you a 1.99% financing. It ends tomorrow so we have to sign today.” I said “If I sign, how long do I have before I can cancel the deal with no penalties?” He said “You have until you take delivery.”

“Let’s go look at the car.” I got in and drove it a bit, and it handled great. Even my mother was impressed how it handled, but it had no options or features. I wasn’t so thrilled since I was giving up everything I actually wanted and not getting a great deal. In the beginning, I told him I wanted one thing no matter what – a hookup for my iphone to connect to the Bluetooth and to play music through the radio. If I have to compromise, that’s the one thing I refuse to give up. We talked as I drove the car around the lot a bit and he made comments about it’s power, acceleration, etc. Finally I got out of the car and began to look for the hookup for the iphone. When he inquired as to what I was searching for, I said “The iphone hookup which you guaranteed came with the car. I don’t see it anywhere.”

He said “It’s right…” and opened the center console followed by “uh-oh”. “Wow…ok…looks like the car doesn’t have the connection after all…not a big deal. You can get an aftermarket part. Let’s go finish the paperwork.”

I looked at him and point blank said “No. The deal’s off. I told you flat out that I’m giving up every option I wanted with this car. I’m settling for a lesser car and the ONE thing I REFUSE to give up is the one thing you guaranteed to be included. Now you’re telling me it’s not and I have to spend MORE money? I’m done.”

He replied “You’d really walk away from the deal for just this?”. “I already said deal’s off.” “OK, OK. Let me go talk to my boss. See what I can do.”

We go inside and he walks inside the manager’s office. He says “I have a customer who wants to purchase a car but he wants to hook up an iphone to it. The car is supposed to have the part but doesn’t. He’s ready to walk away on the deal.” I heard the manager say “For a $30 cable? Give it to him!”

The sales guy comes out and says “I convinced my manager to give you the part. It’s a $650 part that we’re giving you for free.”

We walked back to the office and sat down to sign the paperwork. Again I asked, “Just to double check, if I sign, how long do I have before I can cancel the deal with no penalties?” He said “I’m so confident you’re going to want the car, that you can break it until you take delivery of the vehicle.”

I signed the paperwork and told my friend what happened. He had been researching cars with me and said I should check out the Hyundai Genesis. He went with me to test drive the car the following Monday. We were both seriously impressed with the car and how it handled. I told the salesman that I’m looking to pay X for all the features and I don’t really need the car. If he can meet it, I’ll sign and drive away. If not, no hard feelings and I’ll walk away. He said “Wow, that’s low…but let me see what I can do.” He went to talk to his manager and came back after about 10 minutes. He said “My manager is calling the bank to see if they’ll approve it. It’s a real low offer and he doesn’t think they’ll approve but we never know.” Five minutes later, the manager comes over, shakes my hand and says “Congratulations. I hope you really enjoy your car. It’s the pride of our fleet.”

I called the BMW dealership and spoke to the salesman to let him know I was cancelling the deal. He responded “What can I do to keep your business?” I said “Honestly? There’s not much. You’re not going to beat the price.” He said “What kind of car are you getting and how much are you paying monthly?”  I said “Hyundai Genesis and x dollars.” He said “You’re giving up a BMW for a HYUNDAI?” in shock. I chuckled and said “Not just any Hyundai, one that ranks higher than yours, is cheaper, more room in every direction, has every option you offer, and uses regular gas instead of premium.” He said “Is there anything that I could do to make you reconsider?” to which I calmly replied “Sorry, you’re BMW. You don’t negotiate.”