single char wildcard search issue

[Matt Boersma]

I'm trying to implement a search feature in Django where a user can enter something like

PC-3

and the search will return matches with or without punctuation, case insensitive

results: PC-3, pc3, BxPC3, Pc 3

I've figured out how to do this so I'm getting all of the results above, but the only thing that's missing is the "Pc 3" result. It seems that when I do an Oracle search using "_" as a single-char wildcard, it doesn't consider a "space" to match that wildcard.

The query that is generated:

SELECT DISTINCT "CRYOINV_WELL"."CELL_LINE"
 FROM "CRYOINV_WELL"
 WHERE ((UPPER("CRYOINV_WELL"."CELL_LINE") LIKE UPPER('%PC-3%')
 OR UPPER("CRYOINV_WELL"."CELL_LINE") LIKE UPPER('%PC\\_3%')
 OR UPPER("CRYOINV_WELL"."CELL_LINE") LIKE UPPER('%PC3%')));

The problem is the double backslash that's put into the query on line 4.

Here's some code snippets:

models.py:

class Well(models.Model):
  box = models.ForeignKey(Box, edit_inline=models.TABULAR, num_in_admin=18)
  well_name = models.CharField(maxlength=20, core=True)
  cell_line = models.CharField(maxlength=250, null=True, blank=True)

views.py:

def search(request):

  WellFormClass = forms.form_for_model(Well)
  WellFormClass.base_fields['box'].widget = widgets.HiddenInput()
  WellFormClass.base_fields['well_name'].widget = widgets.HiddenInput()

  if request.GET:
    query = request.GET.get("cell_line", "")
    # Do a "liberal" search first
    # Search for:    PC-3
    # Returned results:   PC-3, pc3, BxPC3, pc 3
    
    # replace any punctuation in the query with a '_'
    # *** for some reason ignoring the space?
    punc_trans = string.maketrans(' -:;', '____')
    modQuery1 = query.translate(punc_trans)
    # parse out any punctuation at all
    no_trans = string.maketrans('', '')
    modQuery2 = query.translate(no_trans, '-:; ')
    # now search for any of these combinations
    results = Well.objects.filter(Q(cell_line__icontains=query) |
                                  Q(cell_line__icontains=modQuery1) |
                                  Q(cell_line__icontains=modQuery2)).values("cell_line").distinct()

    results = list(results)
    return render_to_response('cryoInv/cell_line_search_results.html',
                              {"results": results,"query": query})
  else:
    form = WellFormClass()

  return render_to_response('cryoInv/search.html', {'form': form})      

[ian.g.kelly@…]

This is actually a generic Django problem, not a bug with the boulder-oracle-sprint branch. There is currently no way AFAIK to intentionally include wildcard characters in a query without having them automagically escaped by Django.

[Michael Radziej <mir@…>]

I have a patch that provides a way to make regular LIKE queries without having the arguments escaped. I don't think it would be accepted in mainstream Django since it's too 'sql-ish', but if you are interested, let me know.

[ian.g.kelly@…]

Now that the regex field lookup is part of Django, I don't think that this is an issue any longer.