Opened 17 years ago

Closed 17 years ago

#3998 closed (wontfix)

single char wildcard search issue

Reported by: Matt Boersma Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: sql
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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})      

Change History (4)

comment:1 by Simon G. <dev@…>, 17 years ago

Keywords: boulder-oracle sql added
Summary: boulder-oracle single char wildcard search issue[boulder-oracle] single char wildcard search issue
Triage Stage: UnreviewedAccepted

comment:2 by ian.g.kelly@…, 17 years ago

Keywords: boulder-oracle removed
Summary: [boulder-oracle] single char wildcard search issuesingle char wildcard search issue
Version: other branchSVN

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.

comment:3 by Michael Radziej <mir@…>, 17 years ago

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.

comment:4 by ian.g.kelly@…, 17 years ago

Resolution: wontfix
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top