Indexing with customized parameters

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Indexing with customized parameters

Anuj Bhargava
I am trying to index where the *sector field* has the values 27 and/or 2701
and/or 2702 using the following -

<entity name="country" pk="ID"
  query="SELECT * FROM country WHERE sector = 27 OR sector = 2701 OR sector
= 2702"
  deltaImportQuery="SELECT * FROM country
    WHERE posting_id = '${dataimporter.delta.posting_id}' AND sector = 27
OR sector = 2701 OR sector = 2702"
  deltaQuery="SELECT posting_id FROM country
    WHERE last_modified > '${dataimporter.last_index_time}' AND sector = 27
OR sector = 2701 OR sector = 2702">
</entity>

The sector field has comma separated multiple values like -
27,19,527
38,27,62701
2701,49
55,2702,327

The issue is when I run the above, it indexes the fields containing data
27,19,527 and 2701,49 and ignores the other data. It indexes if the data in
the sector fields starts with either 27 or 2701 or 2702. It doesn't index
if the values 27 or 2701 or 2702 are placed 2nd or 3rd in the sector data
field
Reply | Threaded
Open this post in threaded view
|

Re: Indexing with customized parameters

Anuj Bhargava
Any suggestions?

Regards,

Anuj

On Tue, 10 Dec 2019 at 20:52, Anuj Bhargava <[hidden email]> wrote:

> I am trying to index where the *sector field* has the values 27 and/or
> 2701 and/or 2702 using the following -
>
> <entity name="country" pk="ID"
>   query="SELECT * FROM country WHERE sector = 27 OR sector = 2701 OR
> sector = 2702"
>   deltaImportQuery="SELECT * FROM country
>     WHERE posting_id = '${dataimporter.delta.posting_id}' AND sector = 27
> OR sector = 2701 OR sector = 2702"
>   deltaQuery="SELECT posting_id FROM country
>     WHERE last_modified > '${dataimporter.last_index_time}' AND sector =
> 27 OR sector = 2701 OR sector = 2702">
> </entity>
>
> The sector field has comma separated multiple values like -
> 27,19,527
> 38,27,62701
> 2701,49
> 55,2702,327
>
> The issue is when I run the above, it indexes the fields containing data
> 27,19,527 and 2701,49 and ignores the other data. It indexes if the data in
> the sector fields starts with either 27 or 2701 or 2702. It doesn't index
> if the values 27 or 2701 or 2702 are placed 2nd or 3rd in the sector data
> field
>
Reply | Threaded
Open this post in threaded view
|

Re: Indexing with customized parameters

Emir Arnautović
Hi Anuj,
Maybe I am missing something but this is more question for some SQL group than for Solr group. I am surprised that you get any records. You can consult your DB documentation for some more elegant solution, but a brute-force solution, if your column is string, could be:
WHERE sector = 27 OR sector LIKE ’27,%’ OR sector LIKE ‘%,27,%’ OR sector LIKE ‘%,27’ OR sector = 2701…

HTH,
Emir
--
Monitoring - Log Management - Alerting - Anomaly Detection
Solr & Elasticsearch Consulting Support Training - http://sematext.com/



> On 12 Dec 2019, at 08:38, Anuj Bhargava <[hidden email]> wrote:
>
> Any suggestions?
>
> Regards,
>
> Anuj
>
> On Tue, 10 Dec 2019 at 20:52, Anuj Bhargava <[hidden email]> wrote:
>
>> I am trying to index where the *sector field* has the values 27 and/or
>> 2701 and/or 2702 using the following -
>>
>> <entity name="country" pk="ID"
>>  query="SELECT * FROM country WHERE sector = 27 OR sector = 2701 OR
>> sector = 2702"
>>  deltaImportQuery="SELECT * FROM country
>>    WHERE posting_id = '${dataimporter.delta.posting_id}' AND sector = 27
>> OR sector = 2701 OR sector = 2702"
>>  deltaQuery="SELECT posting_id FROM country
>>    WHERE last_modified > '${dataimporter.last_index_time}' AND sector =
>> 27 OR sector = 2701 OR sector = 2702">
>> </entity>
>>
>> The sector field has comma separated multiple values like -
>> 27,19,527
>> 38,27,62701
>> 2701,49
>> 55,2702,327
>>
>> The issue is when I run the above, it indexes the fields containing data
>> 27,19,527 and 2701,49 and ignores the other data. It indexes if the data in
>> the sector fields starts with either 27 or 2701 or 2702. It doesn't index
>> if the values 27 or 2701 or 2702 are placed 2nd or 3rd in the sector data
>> field
>>

Reply | Threaded
Open this post in threaded view
|

Re: Indexing with customized parameters

Anuj Bhargava
Emir Thanks,

Perfect

On Thu, 12 Dec 2019 at 13:40, Emir Arnautović <[hidden email]>
wrote:

> Hi Anuj,
> Maybe I am missing something but this is more question for some SQL group
> than for Solr group. I am surprised that you get any records. You can
> consult your DB documentation for some more elegant solution, but a
> brute-force solution, if your column is string, could be:
> WHERE sector = 27 OR sector LIKE ’27,%’ OR sector LIKE ‘%,27,%’ OR sector
> LIKE ‘%,27’ OR sector = 2701…
>
> HTH,
> Emir
> --
> Monitoring - Log Management - Alerting - Anomaly Detection
> Solr & Elasticsearch Consulting Support Training - http://sematext.com/
>
>
>
> > On 12 Dec 2019, at 08:38, Anuj Bhargava <[hidden email]> wrote:
> >
> > Any suggestions?
> >
> > Regards,
> >
> > Anuj
> >
> > On Tue, 10 Dec 2019 at 20:52, Anuj Bhargava <[hidden email]> wrote:
> >
> >> I am trying to index where the *sector field* has the values 27 and/or
> >> 2701 and/or 2702 using the following -
> >>
> >> <entity name="country" pk="ID"
> >>  query="SELECT * FROM country WHERE sector = 27 OR sector = 2701 OR
> >> sector = 2702"
> >>  deltaImportQuery="SELECT * FROM country
> >>    WHERE posting_id = '${dataimporter.delta.posting_id}' AND sector = 27
> >> OR sector = 2701 OR sector = 2702"
> >>  deltaQuery="SELECT posting_id FROM country
> >>    WHERE last_modified > '${dataimporter.last_index_time}' AND sector =
> >> 27 OR sector = 2701 OR sector = 2702">
> >> </entity>
> >>
> >> The sector field has comma separated multiple values like -
> >> 27,19,527
> >> 38,27,62701
> >> 2701,49
> >> 55,2702,327
> >>
> >> The issue is when I run the above, it indexes the fields containing data
> >> 27,19,527 and 2701,49 and ignores the other data. It indexes if the
> data in
> >> the sector fields starts with either 27 or 2701 or 2702. It doesn't
> index
> >> if the values 27 or 2701 or 2702 are placed 2nd or 3rd in the sector
> data
> >> field
> >>
>
>