Laravel - How to properly generate unique slugs from article titles?

Question

I need to generate unique slugs based on titles of articles. For matching slugs I want to append a number to the end to make them unique. I made this function based on others work I found around:

static function slugify($title)
    {
        $slug = str_slug($title, '-');
        //THIS IS THE PROBLEMATIC LINE:
        $common = Article::whereRaw("slug RLIKE '^{$slug}(-[0-9]+)?$'")->orderBy('slug', 'desc')->get();
        $count = count($common);
        if( $count > 0 ){
                    $last = $common[0];
                    $broken = explode('-', $last->slug);
                    $num = $broken[count($broken)-1];
                    $num = intval($num) + 1;
                    return $slug.'-'.$num;
        } else
            return $slug.'-1';
    }

The problem: As you can see I try to generate new slugs by using Laravel's str_slug function which will convert a string to a slug form. I then try to query the existing slugs from the databse, order them descendingly (high > low) and take essentialy the highest one within the ordered set. The problem is that MySQL orders them of course as strings and so slug-title-9 would be actually considered higher than slug-title-10 because it's ordering them as characters and not based on the value of the number at the end. How can I make this work? Is there a way to order them based on the last number or am I going in the wrong direction?

Solutions I want to avoid:

I have seen other implementations where people query all the simmilar slugs at once, count them and then append count+1 to the end of the new slug. This is bad because if you delete some articles the overall count would lower and the new slug could conflict with an older one.

I have seen an implementation where a person would append 1 to end of the slug and check if it exists. If it exists they would instead append 2 and try to check if that exists and so one until they find for example slug-title-9 which would not exists. IMO this is bad because you are putting unnecessary strain on the database.

I need a decent solution because the project I am working on has a decend potential of encountering matching slugs often.

Answer 1

lastInsertId returns id's per-connection, meaning concurrent sql connections will not interfere with each other.

However, lastInsertId() is probably not the best approach for whatever you're doing. I see you're already using Eloquent models. When creating a new entry in table, those models return corresponding object, and then you can get id of exactly that object without any confusion.

$article = Article::create(['title' => 'First post', 'content' => '...']);
echo $article->id;

This will always output the correct article id and you do not have to worry about the implementation details. I cannot see a good reason to use lastInsertId in a modern web framework, except maybe for a very few edge cases.

As a general rule, try to avoid dealing directly with database in your application and use the abstractions that the framework provides - it will make your code much more maintainable and simpler to understand.

P.S. that might not be important to you, but lastInsertId() does not work with transactions.

Answer 2

You can use this method. This is one that I am using to get unique seo friendly slug https://stackoverflow.com/a/72137537/7147060