Making a helpful catalogue data reporting command in Laravel

The catalogue data model in our 3D pop up cards shop includes Product and Tag entities. Tags are a flat structure with a many-to-many relationship to Products, to allow some simple tagging to help customers find cards with particular elements.

The Tag-Product relationship is managed manually in the backoffice UI, which is fine but can be error prone when we miss Products that should have a particular Tag.

To make this easier to work with, I put together a quick command line tool in Laravel that identifies Products that seem to be missing an appriate Tag.

You can initialise a new command with this:

php artisan make:command ReportTaggableProducts

That gives you an empty command class to fill in.

This tool is driven by an SQL query that tries to find Products whose name or description contains the name of a Tag, but the Product is not associated with that Tag:

SELECT
	t.name,
	p.id
FROM tags t
INNER JOIN products p
	ON (
		p.name LIKE CONCAT('%', t.name, '%')
		OR p.description LIKE CONCAT('%', t.name, '%')
	)
LEFT JOIN product_tag pt
	ON t.id = pt.tag_id
	AND p.id = pt.product_id
WHERE pt.product_id IS NULL
GROUP BY t.name, p.id

This is not a very efficient query due to the join on two alternative LIKE conditions, but it’s fast enough for this reporting tool.

That can be run in the console command to identify pairs of Tags and Products that seem to be missing an association between each other. The console command can use those rows to generate URLs to go and edit the Products and set up that association in each case if it’s appropriate.

The command could go one step further and automatically set up these associations, but it’s more effective overall to leave that step to manual intervention that is assisted by the automated reporting tool.

The handle() method of the console command ends up looking like this:

<?php

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle(): int
    {
        $taggable = DB::select(<<<SQL
SELECT
  t.name,
  p.sku
FROM tags t
INNER JOIN products p
  ON (
  p.name LIKE CONCAT('%', t.name, '%')
  OR p.description LIKE CONCAT('%', t.name, '%')
  )
LEFT JOIN product_tag pt
  ON t.id = pt.tag_id
  AND p.id = pt.product_id
WHERE pt.product_id IS NULL
GROUP BY t.name,
         p.sku
SQL
        );

        foreach ($taggable as $row) {
            $editUrl = route('products.show', [$row->sku]);
            $this->info(<<<REPORT
{$row->name} could be associated with {$row->sku} {$editUrl}
REPORT
);
        }

        return 0;
    }

Then you can review the report with e.g.:

php artisan report:taggable