-
Notifications
You must be signed in to change notification settings - Fork 1.5k
Erro ao criar índice com valor nulo #2524
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
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, 'partia...
Replies: 1 comment
-
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);
Beta Was this translation helpful? Give feedback.