Simplify SQL queries in WordPress projects with Laravel's Capsule

What you'll learn here?

$wpdb object makes database interactions in WordPress better, but for more advanced cases it might be not enough. I have a feeling that writing raw SQL just makes code readability more common problem. So in this article I'll show you how to simplify SQL usage by adopting Laravel's way of interacting with database. I'll show how to write SQL queries in WordPress looking like this 👇

$capsule->table('users')->where('active', 1)->limit(5)->get();

global $wpdb;
$query = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}users WHERE active = %d LIMIT %d", 1, 5);
$users = $wpdb->get_results($query);

How to initialize Laravel's Capsule object?

Laravel's database manager - called Capsule - relies on the illuminate/database package which can be installed by running the following command in the project root.

composer require illuminate/database

Manager can be initialized by creating a new Capsule instance, which as $wpdb is a central point for interacting with the database. Once created, you need to establish database connection by passing configuration to addConnection function.

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule();
$capsule->addConnection([
    'driver' => 'mysql',
    'host' => DB_HOST,
    'database' => DB_NAME,
    'username' => DB_USER,
    'password' => DB_PASSWORD,
    'charset' => DB_CHARSET,
    'collation' => $wpdb->collate,
    'prefix' => $wpdb->prefix,
]);
$capsule->setAsGlobal();

There's no need to initialize capsule every time you want to use it. You can do it once in the module responsible for managing database operation, and use through the code.

How to create tables with Laravel Capsule?

If you want to perform schema operations like creating tables, removing them, checking their availability, you can do it using schema builder that can be accessed with schema method available in the $capsule object. The Blueprint object is used to define the columns, data types, and constraints for each table.

$schema = $capsule->schema();

if (! $schema->hasTable('table_name')) {
    $schema->create('table_name', function (Blueprint $table) {
        $table->bigIncrements('id')->primary();
        $table->string('name');
        $table->integer('age');
    });
}

Similar things in WordPress can be handled using $wpdb object but I think that we all can agree that there is a difference in code readability between both solutions.

global $wpdb;

$table_name = $wpdb->prefix . 'table_name';
$table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;

if (!$table_exists) {
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        name varchar(255),
        age int(11),
        PRIMARY KEY (id)
    ) $charset_collate;";

    $wpdb->query($sql);
}

How to write queries with Laravel Capsule?

To perform the standard CRUD (create, read, update, delete) operations you need access Query Builder with table function and build query using other methods available there. The following example presents how to get 5 active users from users table.

$capsule->table('users')->where('active', 1)->limit(5)->get();

Similar results can be achieved using $wpdb object but again, even in this simple scenario the difference in code readability is noticeable. Method chaining does the job 🔥

global $wpdb;
$query = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}users WHERE active = %d LIMIT %d", 1, 5);
$users = $wpdb->get_results($query);

Of course, you can perform much more that just querying database for the results. Check out other usecases and the official docs for simple and more advanced examples.

Why would you need Laravel Capsule?

Let's make it clear. Things you'll learn here are not a replacement for the standard database management in WordPress. Saying that it should be used by default now is just an overhead far away from being correct. If so, when should you give It a try?

A long time ago, far, far away...

There was a boy with a passion for digital art. He always wanted to create something that would resonate with people, so he dedicated himself to learning every day, armed with the tools his mother could provide. In the beginning, a laptop and a mouse sufficed. They were easy to use and suitable for his budding skills.

However, as he learned more, he started facing challenges. With more ideas in mind demanding greater precision, achieving desired results became more difficult. He felt that better tools could streamline his efforts, so he saved up to buy a pen tablet. The difference was remarkable. It helped him to bring complex concepts to life more easily, fostering rapid progress. In less time, he crafted artworks that would have taken significantly longer using his initial tools.

Yet, he soon discovered that even the pen tablet had its limitations. While it marked a significant improvement, his appetite for learning and results led to a desire for more precise tools which made the complex work easier. The pen tablet was good, but he dreamed of a Surface Studio for maximum precision and easiness. He couldn't afford it initially, but after months of progress, with more art sales, he decided to give it a try and invest. The boost was even more remarkable than earlier. He was able now to focus mostly on bringing life to ideas that he had rather than fighting with inefficient tools.

Not that long ago and quite close...

Was it a good learning curve? I think so. Would a beginning graphic designer like to use a tool for 25,000 zł? Of course! Would it be efficient at this level? Not really, but as the experience grows - maybe! This situation can be easily reflected in the standard product life. Is using a custom query builder efficient for making simple queries for posts in WordPress? Totally not, especially if we have objects like WP_Query. Would it be efficient to create custom tables and queries for high-traffic systems? Totally.

Check out the real situation that we had while creating a huge project.

In the initial stages of the project, when things were simple, using standard WP_Query worked fine. It met all our needs without any issues. But as the project grew, we faced new challenges. Clients wanted more customized queries, and with more users, the system started to slow down. We had to come up with clever tricks to keep it running smoothly but it was not efficient they required more time with not always great results.

public function search(array $args): array
{
  return get_posts([
    'post_type' => 'team',
    'posts_per_page' => $args['limit'],
  ]);
}

So we decided to write our own queries with $wpdb database access object. WordPress doesn't have query builder so we had to build the query on our own using arrays, string concatenations and other manual tricks.

public function search(array $args): array
{
  global $wpdb;

  $params = [
    'select' => 'id, post_id',
    'where' => '1 = 1',
    'offset' => 10,
    'limit' => 10,
    'orderby' => 'id',
  ];

  if (! empty($args['post_id'])) {
    $params['where'] .= $wpdb->prepare(" AND `post_id` = %d", $args['post_id']);
  }

  if (! empty($args['title'])) {
    $params['where'] .= $wpdb->prepare(" AND `title` LIKE %s", '%' . $args['title'] . '%');
  }

  if (! empty($args['level'])) {
    $params['where'] .= $wpdb->prepare(" AND `level` = %s", $args['level']);
  }

  $query = "SELECT {$params['select']}
    FROM {$this->name}
    WHERE {$params['where']}
    ORDER BY {$params['orderby']}
    LIMIT {$params['offset']},{$params['limit']}";

  return $wpdb->get_results($query, ARRAY_A);
}

public function search(array $args): array
{
  $default = [
    'distance' => 25,
    'limit' => ! empty($args['limit']) ? (int) sanitize_text_field($args['limit']) : 10,
    'page' => ! empty($args['page']) ? (int) sanitize_text_field($args['page']) : 1,
  ];

  $params = [
    'select' => '`id`, `post_id`, `title`, `logo_id`, `level`, `region`, `city`, ST_X(coordinate) lat, ST_Y(coordinate) lon',
    'where' => '1 = 1',
    'offset' => $default['page'] <= 1 ? 0 : (($default['page'] - 1) * $default['limit']),
    'limit' => $default['limit'],
    'orderby' => '`id`',
  ];

  if (! empty($args['post_id'])) {
    $params['where'] .= $this->db->prepare(" AND `post_id` = %d", $args['post_id']);
  }

  if (! empty($args['title'])) {
    $params['where'] .= $this->db->prepare(" AND `title` LIKE %s", '%' . $args['title'] . '%');
  }

  if (! empty($args['region'])) {
    $params['where'] .= $this->db->prepare(" AND `region` LIKE %s", $args['region']);
  }

  if (! empty($args['city'])) {
    $params['where'] .= $this->db->prepare(" AND `city` LIKE %s", $args['city']);
  }

  if (! empty($args['level'])) {
    $params['where'] .= $this->db->prepare(" AND `level` = %s", $args['level']);
  }

  if (empty($args['distance'])) {
    $args['distance'] = (int) $default['distance'];
  }

  if (! empty($args['lat']) && ! empty($args['lon'])) {
    $params['select'] .= $this->db->prepare(", ST_Distance_Sphere(POINT(ST_Y(coordinate), ST_X(coordinate)), POINT(%f, %f)) / 1609.34 as distance", $args['lon'], $args['lat']);
    $params['where'] .= $this->db->prepare(" AND ST_Distance_Sphere(POINT(ST_Y(coordinate), ST_X(coordinate)), POINT(%f, %f)) / 1609.34 < {$args['distance']}", $args['lon'], $args['lat']);
    $params['orderby'] = '`distance`';
  }

  $query = "SELECT {$params['select']} FROM {$this->name} WHERE {$params['where']} ORDER BY {$params['orderby']} LIMIT {$params['offset']},{$params['limit']}";

  return $this->db->get_results($query, ARRAY_A);
}

Once the codebase grows managing this function becames more and more problematic. Adding join operations, parameters exclusions, more custom logic can make the readability hard as hell - I've already experienced this and that's nothing funny.

How does the capsule handles this? One of the biggest advantages over a $wpdb is that we don't need to write a raw SQL query from scratch. We can build a query using simple methods like where, whereIn, join and more in many different places until we execute it using get method.

public function search(array $args): array
  {
    $query = $capsule->table('posts')
      ->select('id, post_id')
      ->orderBy('id')
      ->skip(10)
      ->limit(10);

    if (! empty($args['post_id'])) {
      $query->where('post_id', $args['post_id']);
    }

    if (! empty($args['title'])) {
      $query->where('title', 'like', "%{$args['title']}%");
    }

    if (! empty($args['level'])) {
      $query->where('level', $args['level']);
    }

    return $query->get()
  }

Even when the codebase grows the repository becames simpler and easier to extend. The queries also take a more human form what allows to be more confidend with the solutions we create and like working with SQL a little more. It looks just easier, and that's important especially if you crafting long-term project or work in a team.

Use it or not to use, that's the question.

Is it a default choice in all my WordPress projects? Totally not. I don't think that using additional library just for simple queries is worth. For such cases, using $wpdb object is mostly enough, but if I would work in the projects that requires more database operations, especially when working with custom tables - I would totally try to use it.

What does the community think?

I would like to point out very interesting observations that I agree with. Mr. Calvin pointed out that such configuration duplicates database connections. I'm not as much concerned about this in my usecase, because I use it with call-by-need pattern and in general it almost have no impact. But it might have in your case! Be aware of this.

Also, it was pointed out that using this component just for sugar syntax is unneded overhead. I was not able to confirm the bad impact of the Capsule object in my codebase but in general terms it's important point that I trurly belive in. Unnecessary overhead should be treated as important factor when using solutions.

For the ones who can't accept such overhead, I insist to check out the library that Mr. Calvin linked in the Tweet above. It seems to solve problems with readability as well, without such overhead 👆 Since I already use Capsule in my codebase and I haven't noticed bad impact on the business I'll continue using it with a full responsibility, but I mention this, because we need to be aware of things that are not visible at first view.

Another interesting discussion, which results can be used for supporting the use of such a solution. It is about working with prepared statements to avoid SQL injection.

I'm not the MySQL admin expert, but as far as I check some sources I was surprised about the results 🤷‍♂️ It looks that WordPress uses strings replacement for avoiding SQL complexity. So as far as I understand, it's a far away of the real prepared statements. Check out the full thread for more information 👋