Opened 9 years ago

Closed 8 years ago

#3998 closed (wontfix)

single char wildcard search issue

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


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


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:


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

Here's some code snippets:

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)

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

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

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

Change History (4)

comment:1 Changed 9 years ago by Simon G. <dev@…>

  • Keywords boulder-oracle sql added
  • Summary changed from boulder-oracle single char wildcard search issue to [boulder-oracle] single char wildcard search issue
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 9 years ago by ian.g.kelly@…

  • Keywords boulder-oracle removed
  • Summary changed from [boulder-oracle] single char wildcard search issue to single char wildcard search issue
  • Version changed from other branch to SVN

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 Changed 9 years ago by 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.

comment:4 Changed 8 years ago by ian.g.kelly@…

  • Resolution set to wontfix
  • Status changed from new to closed

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