How to negate numeric range query - or - how to get records NOT matching a certain numeric range

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

How to negate numeric range query - or - how to get records NOT matching a certain numeric range

Sebastian Riemer

Hi all!

 

Consider a query containing fq-params like this:

 

"fq":["tenant_id:1",

        "u_markedAsDeleted_b:false",

        "u_id_s:[* TO *]",

        "(u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823]))"]

 

This gives me a list of users, having a last lending date (somewhat encoded as long) in that given numeric range.

 

Now, I’d like to get a list of users, *NOT* having a last lending in that given numeric range.

 

I’ve tried adding NOT and ! to the respective fq-query-part without success.

 

Additional info: the field is of type long (TrieLongField) and it is multiValued="true"

 

An example of the full query-string would be:

 

start=0&rows=50&fq=tenant_id:1&fq=u_markedAsDeleted_b:false&fq=u_id_s:[* TO *]&q=*:*&facet=true&facet.missing=true&facet.sort=count&facet.mincount=1&sort=u_userName_cp_s desc&qf=u_userName_cp_s^20 u_displayName_cp_s^20  text^2 text_en text_de text_it&pf=u_userName_cp_s^100 u_displayName_cp_s^20  text^10&mm=100%

 

Thank you for your input and a nice weekend to all of you!

 

Please let me know if I did not share vital details!

 

Mit freundlichen Grüßen

Sebastian Riemer, BSc

 

 


LITTERA Software & Consulting GmbH

A-6060 Hall i.T., Haller Au 19a

Telefon: +43(0) 50 765 000, Fax: +43(0) 50 765 118

Sitz: Hall i.T., eingetragen beim Handelsgericht Innsbruck,
Firmenbuch-Nr. FN 295807k, geschäftsführender Gesellschafter: Albert Unterkircher

 

D-80637 München, Landshuter Allee 8-10
Telefon: +49(0) 89 919 29 122, Fax: +49(0) 89 919 29 123

Sitz: München, eingetragen beim Amtsgericht München
unter HRB 103698, Geschäftsführer: Albert Unterkircher

E-Mail: <a href="blocked::mailto:office@littera.eu" title="blocked::mailto:office@littera.eu">office@...
Homepage
: www.littera.eu

 

Diese Nachricht kann vertrauliche, nicht für die Veröffentlichung bestimmte und/oder rechtlich geschützte Informationen enthalten. Falls Sie nicht der beabsichtigte Empfänger sind, beachten Sie bitte, dass jegliche Veröffentlichung, Verbreitung oder Vervielfältigung dieser Nachricht strengstens untersagt ist. Sollten Sie diese Nachricht irrtümlich erhalten haben, informieren Sie bitte sofort den Absender durch Anruf oder Rücksendung der Nachricht und vernichten Sie diese.

This communication may contain information that is legally privileged, confidential or exempt from disclosure.  If you are not the intended recipient, please note that any dissemination, distribution, or copying of this communication is strictly prohibited.  Anyone who receives this message in error should notify the sender immediately by telephone or by return e-mail and delete this communication entirely from his or her computer.

 

Reply | Threaded
Open this post in threaded view
|

Re: How to negate numeric range query - or - how to get records NOT matching a certain numeric range

David Hastings
having
fq=NOT field:value
works for me,

On Fri, Jan 24, 2020 at 10:56 AM Sebastian Riemer <[hidden email]>
wrote:

> Hi all!
>
>
>
> Consider a query containing fq-params like this:
>
>
>
> "*fq*":["tenant_id:1",
>
>         "u_markedAsDeleted_b:false",
>
>         "u_id_s:[* TO *]",
>
>         "(u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823]))"]
>
>
>
> This gives me a list of users, having a last lending date (somewhat
> encoded as long) in that given numeric range.
>
>
>
> Now, I’d like to get a list of users, **NOT** having a last lending in
> that given numeric range.
>
>
>
> I’ve tried adding NOT and ! to the respective fq-query-part without
> success.
>
>
>
> Additional info: the field is of type *long (TrieLongField)* and it is *multiValued**="true"*
>
>
>
> An example of the full query-string would be:
>
>
>
> start=0&rows=50&fq=tenant_id:1&fq=u_markedAsDeleted_b:false&fq=u_id_s:[*
> TO
> *]&q=*:*&facet=true&facet.missing=true&facet.sort=count&facet.mincount=1&sort=u_userName_cp_s
> desc&qf=u_userName_cp_s^20 u_displayName_cp_s^20  text^2 text_en text_de
> text_it&pf=u_userName_cp_s^100 u_displayName_cp_s^20  text^10&mm=100%
>
>
>
> Thank you for your input and a nice weekend to all of you!
>
>
>
> Please let me know if I did not share vital details!
>
>
>
> Mit freundlichen Grüßen
>
> Sebastian Riemer, BSc
>
>
>
>
>
> [image: logo_Littera_SC] <http://www.littera.eu/>
> LITTERA Software & Consulting GmbH
>
> A-6060 Hall i.T., Haller Au 19a
>
> Telefon: +43(0) 50 765 000, Fax: +43(0) 50 765 118
>
> Sitz: Hall i.T., eingetragen beim Handelsgericht Innsbruck,
> Firmenbuch-Nr. FN 295807k, geschäftsführender Gesellschafter: Albert
> Unterkircher
>
>
>
> D-80637 München, Landshuter Allee 8-10
> Telefon: +49(0) 89 919 29 122, Fax: +49(0) 89 919 29 123
>
> Sitz: München, eingetragen beim Amtsgericht München
> unter HRB 103698, Geschäftsführer: Albert Unterkircher
>
> E-Mail: [hidden email]
> Homepage: www.littera.eu
>
>
>
> Diese Nachricht kann vertrauliche, nicht für die Veröffentlichung
> bestimmte und/oder rechtlich geschützte Informationen enthalten. Falls Sie
> nicht der beabsichtigte Empfänger sind, beachten Sie bitte, dass jegliche
> Veröffentlichung, Verbreitung oder Vervielfältigung dieser Nachricht
> strengstens untersagt ist. Sollten Sie diese Nachricht irrtümlich erhalten
> haben, informieren Sie bitte sofort den Absender durch Anruf oder
> Rücksendung der Nachricht und vernichten Sie diese.
>
> This communication may contain information that is legally privileged,
> confidential or exempt from disclosure.  If you are not the intended
> recipient, please note that any dissemination, distribution, or copying of
> this communication is strictly prohibited.  Anyone who receives this
> message in error should notify the sender immediately by telephone or by
> return e-mail and delete this communication entirely from his or her
> computer.
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to negate numeric range query - or - how to get records NOT matching a certain numeric range

David Hastings
just tried  "fq":"NOT year:[1900 TO 2000]"}},
on my data et and also worked as expected, mind if i ask why:
(u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823]))

there are ()'s around your range query?

On Fri, Jan 24, 2020 at 11:01 AM David Hastings <
[hidden email]> wrote:

> having
> fq=NOT field:value
> works for me,
>
> On Fri, Jan 24, 2020 at 10:56 AM Sebastian Riemer <[hidden email]>
> wrote:
>
>> Hi all!
>>
>>
>>
>> Consider a query containing fq-params like this:
>>
>>
>>
>> "*fq*":["tenant_id:1",
>>
>>         "u_markedAsDeleted_b:false",
>>
>>         "u_id_s:[* TO *]",
>>
>>         "(u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823]))"
>> ]
>>
>>
>>
>> This gives me a list of users, having a last lending date (somewhat
>> encoded as long) in that given numeric range.
>>
>>
>>
>> Now, I’d like to get a list of users, **NOT** having a last lending in
>> that given numeric range.
>>
>>
>>
>> I’ve tried adding NOT and ! to the respective fq-query-part without
>> success.
>>
>>
>>
>> Additional info: the field is of type *long (TrieLongField)* and it is *multiValued**="true"*
>>
>>
>>
>> An example of the full query-string would be:
>>
>>
>>
>> start=0&rows=50&fq=tenant_id:1&fq=u_markedAsDeleted_b:false&fq=u_id_s:[*
>> TO
>> *]&q=*:*&facet=true&facet.missing=true&facet.sort=count&facet.mincount=1&sort=u_userName_cp_s
>> desc&qf=u_userName_cp_s^20 u_displayName_cp_s^20  text^2 text_en text_de
>> text_it&pf=u_userName_cp_s^100 u_displayName_cp_s^20  text^10&mm=100%
>>
>>
>>
>> Thank you for your input and a nice weekend to all of you!
>>
>>
>>
>> Please let me know if I did not share vital details!
>>
>>
>>
>> Mit freundlichen Grüßen
>>
>> Sebastian Riemer, BSc
>>
>>
>>
>>
>>
>> [image: logo_Littera_SC] <http://www.littera.eu/>
>> LITTERA Software & Consulting GmbH
>>
>> A-6060 Hall i.T., Haller Au 19a
>>
>> Telefon: +43(0) 50 765 000, Fax: +43(0) 50 765 118
>>
>> Sitz: Hall i.T., eingetragen beim Handelsgericht Innsbruck,
>> Firmenbuch-Nr. FN 295807k, geschäftsführender Gesellschafter: Albert
>> Unterkircher
>>
>>
>>
>> D-80637 München, Landshuter Allee 8-10
>> Telefon: +49(0) 89 919 29 122, Fax: +49(0) 89 919 29 123
>>
>> Sitz: München, eingetragen beim Amtsgericht München
>> unter HRB 103698, Geschäftsführer: Albert Unterkircher
>>
>> E-Mail: [hidden email]
>> Homepage: www.littera.eu
>>
>>
>>
>> Diese Nachricht kann vertrauliche, nicht für die Veröffentlichung
>> bestimmte und/oder rechtlich geschützte Informationen enthalten. Falls Sie
>> nicht der beabsichtigte Empfänger sind, beachten Sie bitte, dass jegliche
>> Veröffentlichung, Verbreitung oder Vervielfältigung dieser Nachricht
>> strengstens untersagt ist. Sollten Sie diese Nachricht irrtümlich erhalten
>> haben, informieren Sie bitte sofort den Absender durch Anruf oder
>> Rücksendung der Nachricht und vernichten Sie diese.
>>
>> This communication may contain information that is legally privileged,
>> confidential or exempt from disclosure.  If you are not the intended
>> recipient, please note that any dissemination, distribution, or copying of
>> this communication is strictly prohibited.  Anyone who receives this
>> message in error should notify the sender immediately by telephone or by
>> return e-mail and delete this communication entirely from his or her
>> computer.
>>
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to negate numeric range query - or - how to get records NOT matching a certain numeric range

Shawn Heisey-2
On 1/24/2020 9:04 AM, David Hastings wrote:
> just tried  "fq":"NOT year:[1900 TO 2000]"}},
> on my data et and also worked as expected, mind if i ask why:
> (u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823]))
>
> there are ()'s around your range query?

I think David is correct here about the parentheses causing a problem.
If that query is working without the negation, that's a little odd.  I
do know the parentheses should not be there.

Purely negative queries in Lucene do not actually work.  The problem
with them is that if you start with nothing and then subtract something,
you end up with nothing.

When the query being negated is very simple, Solr is able to detect the
problem and internally fix it before running the query.  If there is ANY
complexity to it at all, Solr cannot do this, and it won't work.  It is
likely that adding parentheses around the range as you have makes the
query complex enough that this detection doesn't work.

The fully correct way to write a negated version of the query above is:

*:* -u_lastLendingDate_combined_ls_ns:[8610134693 TO 8611935823]

This is a starting point of all documents, subtracting documents where
the field falls within the specified range.  You could replace the minus
sign with "AND NOT " for the same effect.

Thanks,
Shawn
Reply | Threaded
Open this post in threaded view
|

AW: How to negate numeric range query - or - how to get records NOT matching a certain numeric range

Sebastian Riemer
Dear community!

It works as suggested, either using

"-u_lastLendingDate_combined_ls_ns:[8610134693 TO 8611935823]"

or

"NOT u_lastLendingDate_combined_ls_ns:[8610134693 TO 8611935823]"

It seems that additional bracketing (as in the next line) does not harm my query but I will eliminate it as it's unnecessary and possibly wrong.

"!u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823])"]

About the only negated query-parts, thanks I already thought of that! Glad I already have a positive part with "u_id_s:[* TO *]"

I am not sure about my initial mistake, either my positioning of the negate-keyword was wrong, or I did not spell the "NOT"-keyword in uppercase.

Thank you all for your time and effort,  have a nice day!


Sebastian

-----Ursprüngliche Nachricht-----
Von: Shawn Heisey [mailto:[hidden email]]
Gesendet: Freitag, 24. Jänner 2020 21:51
An: [hidden email]
Betreff: Re: How to negate numeric range query - or - how to get records NOT matching a certain numeric range

On 1/24/2020 9:04 AM, David Hastings wrote:
> just tried  "fq":"NOT year:[1900 TO 2000]"}}, on my data et and also
> worked as expected, mind if i ask why:
> (u_lastLendingDate_combined_ls_ns:([8610134693 TO 8611935823]))
>
> there are ()'s around your range query?

I think David is correct here about the parentheses causing a problem.
If that query is working without the negation, that's a little odd.  I do know the parentheses should not be there.

Purely negative queries in Lucene do not actually work.  The problem with them is that if you start with nothing and then subtract something, you end up with nothing.

When the query being negated is very simple, Solr is able to detect the problem and internally fix it before running the query.  If there is ANY complexity to it at all, Solr cannot do this, and it won't work.  It is likely that adding parentheses around the range as you have makes the query complex enough that this detection doesn't work.

The fully correct way to write a negated version of the query above is:

*:* -u_lastLendingDate_combined_ls_ns:[8610134693 TO 8611935823]

This is a starting point of all documents, subtracting documents where the field falls within the specified range.  You could replace the minus sign with "AND NOT " for the same effect.

Thanks,
Shawn