Erro ao criar índice com valor nulo

[JefersonCelestino]

Versão laravel: 9
Versão PHP: 8.2
Versão package: 3.9

Estou tentando criar um índice único, segue meu código:

try { Schema::table('invoice_services', function (Blueprint $collection) { $collection->index('company_id'); $collection->index('numRps'); $collection->index('serieRps'); $options['sparse'] = true; $options['unique'] = true; $collection->index(['company_id', 'numRps', 'serieRps'], 'uniques', null, $options); }); } catch (\Exception $ex) { Log::debug($ex->getMessage()); }

Porém mesmo utilizando sparse para ignorar registros como valor nulo, é retornado o seguinte erro:

Index build failed: fd1868d8-db3c-4367-b818-42939da3bd5d: Collection invoice_hmlg.invoice_services ( 34a28ecd-882d-49b4-8e8b-e6e90b08b932 ) :: caused by :: E11000 duplicate key error collection: invoice_hmlg.invoice_services index: company_id_1_numRps_1_serieRps_1 dup key: { company_id: "5b6da812e014611c866da693", numRps: null, serieRps: null }

Alguém poderia me ajudar?

[alcaeus]

The sparse index option doesn't exclude documents where a single field in a compound index is null. From the documentation:

Only indexes documents that contain a value for at least one of the keys.

In this case, you should be using partial indexes. These offer a superset of the sparse index functionality by letting you define your own criteria for including a document in the index. However, the partialFilterExpression only supports a subset of the usual filter criteria. In this case, you'd be looking to exclude any document where numRps or serieRps is not null. Usually, this would result in this filter (note that the example does not work):

$options = [
    'unique' => true,
    'partialFilterExpression' => [
        'numRps' => ['$ne' => null],
        'serieRps' => ['$ne' => null],
    ],
];

$collection->index(['company_id', 'numRps', 'serieRps'], 'uniques', null, $options);

Creating this index will yield the following error (note that $ne: null is converted to $not: { $eq: null }:

Expression not supported in partial index: $not

Depending on the type of the values in the two fields you can work around this by using the $type operator or the $gt operator (if the value is an int). The following is an example where I assume that numRps is an int and serieRps is a string field - you can adapt this depending on your needs:

$options = [
    'unique' => true,
    'partialFilterExpression' => [
        'numRps' => ['$gt' => 0],
        'serieRps' => ['$type' => 'string'],
    ],
];

$collection->index(['company_id', 'numRps', 'serieRps'], 'uniques', null, $options);