Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Erro ao criar índice com valor nulo #2524

Answered by alcaeus
JefersonCelestino asked this question in Q&A
Discussion options

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?

You must be logged in to vote

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

Comment options

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);
You must be logged in to vote
0 replies
Answer selected by divine
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
Q&A
Labels
None yet

AltStyle によって変換されたページ (->オリジナル) /