SluggableBehavior unexpected behavior

I am puzzled by SluggableBehavior when the optional setting 'ensureUnique' => true is applied.

Perhaps it’s by design, or a bug? Suggestions, advice appreciated please.

The behavior works as expected when creating a slug, however an additional database call is made when using GridView to list the entries.

The additional db call SELECT EXISTS(SELECT * FROM `slugtest` WHERE `slugtest`.`slug`='') is included as part of the GridView setup, but does not appear to serve any purpose.

Why is it being used? And why is display in GridView Search box behaving in confusing manner?

Using
YII2 2.0.40 basic
PHP 7.4.13
Sqlite 3 and MySQL 5.7

Case 1

For example, using a simple db and ActiveRecord with Query and Search classes:

/**
 * This is the model class for table "{{%slugtest}}".
 *
 * @property int $id
 * @property string $title
 * @property string $slug
 * @property string|null $name
 */

and populate $title and $slug with simple strings like [‘one’,‘two’,‘three’], and name with any text.

Using the standard output for localhost/slugtest/index that displays all attributes, a search using ‘Title’ for ‘one’ produces the correct result. Looking at ‘DB’ calls in debug bar shows the following calls:

SELECT EXISTS(SELECT * FROM `slugtest` WHERE `slugtest`.`slug`='one')
followed by
SELECT EXISTS(SELECT * FROM `slugtest` WHERE `slugtest`.`slug`='one-2')

The first result is ‘1’, the second result is ‘0’. But why introduce the queries when the WHERE clause should be slugtest.slug LIKE '%one$'

Why are these calls made using ‘slug’ as attribute, when the search request (by the user) is using ‘title’?

The correct query SELECT * FROM `slugtest` WHERE `title` LIKE '%one%' is made at end of sequence to display results.

In addition, after the results are displayed, the search field for ‘Slug’ includes the characters ‘one-2’, which appears to come from one of the SQL constructs, but is superfluous.

Case 2

In db table set one or more of the ‘slug’ attributes to empty string (don’t use NULL).

In index display the entry for ‘slug’ will show nothing, but the search box above the table will show ‘-2’, which appears to come from an addition SQL call.

The additional SQL is SELECT EXISTS(SELECT * FROM `slugtest` WHERE `slugtest`.`slug`='-2'), which is similar to an example shown earlier, but without any search term before ‘-2’.

This extra query does not appear if the attribute values are ‘NULL’, because the previous query SELECT EXISTS(SELECT * FROM `slugtest` WHERE `slugtest`.`slug`='') returns ‘1’ and only looks for empty string.

Observations

The above queries occur when searching on any attribute like ‘name’ or ‘id’, seem unnecessary but don’t affect results.

Most likely a GridView, or any list would not display ‘slug’, unless it is for admin purposes.

Even if ‘slug’ attribute is removed from the list, the queries are still run on page load or search. They do not appear in actions for ‘View’, ‘Update’ or ‘Delete’.

Removing attribute for ‘slug’ from Search model and/or andFilterWhere, makes no difference.

Thankyou
Mike B

It looks like you execute ->validate over database model so behaviour tries to generate slug.
I suppose you have

class SlugTest extends ActiveRecord
{
     // SluggableBehaviour and db model validation here

     function search()
     {
         $this->validate();
         return new ActiveDataProvider <...>
     }
}

In that case you should split search from db model:

class SlugTest extends ActiveRecord
{
       // SluggableBehaviour and db model validation here
 }

class SluggableSearch
{
     // Search params validation here

     function search()
     {
          $query = SlugTest::find();
          <...>
          return new ActiveDataProvider <...>
     }
}

Why are these calls made using ‘slug’ as attribute, when the search request (by the user) is using ‘title’?

Three reasons:

  1. two distinct strings may have the same url friendly representation. Like “Mr’s” and “MRS” both transforms to “mrs”.
  2. You can create slug from several fields, so value => [title, creation_date] will create something like “my-first-article-2020-01-15”.
  3. Source field may not be present in database, any getter is fine.

But why introduce the queries when the WHERE clause should be `slugtest.slug LIKE ‘%one%’

I assume you meant 'one%' because the may be lots of irrelevant records with in “one” in the middle of the slug.
Imagine you already have “One, oh one” and now you add “One” - and perfectly unique title receives slug “one-2”. No good.

Also, depending on the database LIKE:

  1. may be orders of magnitude slower than dozen simple comparison queries
  2. may be unsupported.

Many thanks for your ideas. I shall explore further.
Mike B