How to effectively build a view with the following filter criteria: select records which do not contain string ".jpg" and ".gif" and ".png" and ",pdf" and .... and so on.

It is easy to create a criteria to contain those patterns, simply choose Regular Expression operator for the Filter Critera and enter those patterns separated by "|".

But how to create a criteria which does *not* contain these patterns? In MySQL it would look: NOT RLIKE '.jpg|.gif|.png|.pdf'

(effectively - meaning, without entering each pattern as a separate criteria).

CommentFileSizeAuthor
#28 views-negative-regular-expression-filter-1602658-28.patch2.68 KBandrefy
#23 views-negative-regular-expression-filter-1602658-23.patch2.97 KBhargobind
#11 views-negative-regular-expression-filter-1602658-11.patch2.79 KBReg
#8 views-negative-regular-expression-filter-1602658-7.patch3.24 KBReg
#6 views-negative-regular-expression-filter-1602658-6.patch2.49 KBReg
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

damiankloip’s picture

damiankloip CreditAttribution: damiankloip commented

Regular expressions usually are a bit easier/suited to finding matches. This is more of a general issue, you should do some research online for patterns you can use for stuff like this.

raincloud’s picture

raincloud CreditAttribution: raincloud commented

The MySql formula NOT RLIKE '.jpg|.gif|.png|.pdf' is the effect of my online research. I just don't know how to reflect it in Views, because in Views there is only "LIKE" criteria and no "NOT LIKE" criteria.

damiankloip’s picture

damiankloip CreditAttribution: damiankloip commented

I think this is the nature of most regular expressions. It can be done, Off the top of my head I don't know. You might want to search for more general terms, as it's not really anything to do with sql.

raincloud’s picture

raincloud CreditAttribution: raincloud commented

Views has a "Regular Expression" filter. If you check the SQL query it produces, it is translated to RLIKE.

The opposite of RLIKE is NOT RLIKE. This operator is in MySql but is missing in Views. It is powerful because allows to eliminate many records easily. In Views, you just have to eliminate line by line using "Does not contain".

The solution would be to add another filter criteria which would simply translate to MySql NOT RLIKE. Views have negative operators for all other operators:

Contains - Not Contains
Like - Not Like
Starts With - Does not Start With

etc.

The only exception is Regular Expression filter. It does not have its negative form.

yang_yi_cn’s picture

yang_yi_cn CreditAttribution: yang_yi_cn commented

I think it should be added as all the other filters (equals, contains) have the negative form to select.

Reg’s picture

Reg CreditAttribution: Reg commented
Version: 7.x-3.3 » 7.x-3.7
Component: Miscellaneous » Code
Category: Support request » Feature request
Issue summary: View changes
Status: Active » Needs review
FileSize
views-negative-regular-expression-filter-1602658-6.patch2.49 KB

I recently came across this problem and modified views to do this since it makes no sense not to have it. Here's a patch against views version 7.x-3.7.

Status: Needs review » Needs work

The last submitted patch, 6: views-negative-regular-expression-filter-1602658-6.patch, failed testing.

Reg’s picture

Reg CreditAttribution: Reg commented
FileSize
views-negative-regular-expression-filter-1602658-7.patch3.24 KB

This one is a git style patch (created by svn) so hopefully the system will be fine with it now.

I noticed that there's an option to retest a patch. I assume that means you can also replace it before retesting. How do you do that? I always create a new comment with a new patch but it's waste at times, like this time where it's just a matter of giving the tester a format it wants to see.

Reg’s picture

Reg CreditAttribution: Reg commented
Status: Needs work » Needs review

Status: Needs review » Needs work

The last submitted patch, 8: views-negative-regular-expression-filter-1602658-7.patch, failed testing.

Status: Needs review » Needs work

The last submitted patch, 8: views-negative-regular-expression-filter-1602658-7.patch, failed testing.

Reg’s picture

Reg CreditAttribution: Reg commented
Status: Needs work » Needs review
FileSize
views-negative-regular-expression-filter-1602658-11.patch2.79 KB

"svn diff --git ..." doesn't do the paths the way I expected. Let's try after a little manual massaging of the file.

Reg’s picture

Reg CreditAttribution: Reg commented
Title: how to effectively use negative regular expression filter? » Add negative regular expression filter
FerCamp’s picture

FerCamp CreditAttribution: FerCamp commented

patch posted in #11 works for me. Adds a "not regular expression" operator. thanks

eileenmcnaughton’s picture

eileenmcnaughton CreditAttribution: eileenmcnaughton commented
Status: Needs review » Reviewed & tested by the community

Works well for me too.

hargobind’s picture

hargobind
he/him
Location Austin, Texas
CreditAttribution: hargobind commented

#11 works perfectly as advertised.

Reg’s picture

Reg CreditAttribution: Reg commented

Can we get this into the next version or at least the dev. version now?

xurizaemon’s picture

xurizaemon
he/him
Location Ōtepoti, Aotearoa 🏝
CreditAttribution: xurizaemon commented
Version: 7.x-3.7 » 7.x-3.x-dev

RTBC++

Ludo.R’s picture

Ludo.R
Location Brussels
CreditAttribution: Ludo.R commented

#11 works for me with Views 7.x-3.11

RTBC++

annya’s picture

annya CreditAttribution: annya commented

#11 works. Thanks!

Reg’s picture

Reg CreditAttribution: Reg as a volunteer commented

Looks like this is well tested. Any chance this will go into the next version? I think this was asked once before and no answer was given. Anyone else want to see this in the next version?

xurizaemon’s picture

xurizaemon
he/him
Location Ōtepoti, Aotearoa 🏝
CreditAttribution: xurizaemon at Fuzion commented

@Reg / @all, maybe find a member of the Views Bug Squad and get them to review / give their OK will help?

Reg’s picture

Reg CreditAttribution: Reg as a volunteer commented

@xurizaemon - I sent a message out to one of them, we'll see what happens.

hargobind’s picture

hargobind
he/him
Location Austin, Texas
CreditAttribution: hargobind commented
Status: Reviewed & tested by the community » Needs review
FileSize
views-negative-regular-expression-filter-1602658-23.patch2.97 KB

Since #11 no longer applies against -dev, here's an updated patch against the latest 3.x branch. Nothing else has been changed. It also nicely applies against the latest stable release 3.13.

Unfortunately it now has to go back to Needs Review, so please review it and mark it RTBC!

heddn’s picture

heddn
Primary language English
Location Nicaragua
CreditAttribution: heddn at MTech, LLC commented
Status: Needs review » Reviewed & tested by the community

Works. And is almost identical to #2137069: Add support for NOT RLIKE (negative regex)

SilviuChingaru’s picture

SilviuChingaru CreditAttribution: SilviuChingaru commented
SilviuChingaru’s picture

SilviuChingaru CreditAttribution: SilviuChingaru commented

+1

attheshow’s picture

attheshow CreditAttribution: attheshow commented

Patch #23 is working for me as well.

andrefy’s picture

andrefy CreditAttribution: andrefy commented
FileSize
views-negative-regular-expression-filter-1602658-28.patch2.68 KB

Since #27 no longer apply to dev after 3.20, here is an update patch against the latest 3.x branch Nothing else has been changed. It also nicely applies against the latest stable release 3.20.

andrefy’s picture

andrefy CreditAttribution: andrefy commented
Status: Reviewed & tested by the community » Needs review
Chris Matthews’s picture

Chris Matthews CreditAttribution: Chris Matthews as a volunteer commented
Status: Needs review » Reviewed & tested by the community

The patch in #28 applied cleanly to the latest views 7.x-3.x-dev and works for me as well so changing to RTBC.

Applied patch handlers/views_handler_filter_combine.inc cleanly.
Applied patch handlers/views_handler_filter_numeric.inc cleanly.
Applied patch handlers/views_handler_filter_string.inc cleanly.

  • DamienMcKenna committed a8a41a2 on 7.x-3.x authored by Reg 
    Issue #1602658 by Reg, hargobind, andrefy, raincloud, damiankloip,...
DamienMcKenna’s picture

DamienMcKenna
Location NH, USA
CreditAttribution: DamienMcKenna at Mediacurrent commented
Status: Reviewed & tested by the community » Fixed
Parent issue: » #2960871: Plan for Views 7.x-3.23 release

Committed. Thank you!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

strelkov.av’s picture

strelkov.av
he/him
Primary language English
Location Russia
CreditAttribution: strelkov.av commented

Is it possible to add the condition DOES NOT CONTAIN ANY WORD, i.e. that it absolutely also works as CONTAIN ANY WORD only so that the query is not LIKE but NOT LIKE

It is just difficult for the user to define a negative regular expression , in my case I give the user to find results that contain a CAR or PLANE, but not a MOTORCYCLE or HELICOPTER
I.e. 2 filters, positive and negative

xurizaemon’s picture

xurizaemon
he/him
Location Ōtepoti, Aotearoa 🏝
CreditAttribution: xurizaemon commented

Hey @strelkov.av, yes that is possible, but to get that in Views you'd need to open that as a new feature request (and help make it happen) as this issue has already been resolved.

An alternative would be to implement it via custom VIews hooks which invert the query behaviour to suit your requirements.

To get this into Views (or propose changes to other modules), I recommend a thorough read of these two links on collaborating in the issue queue - following that guidance will help you be effective.

(Also note this one took ~7 years to get done! First goal is probably to make a patch that delivers this and share it in your new feature request issue. Best of luck!)