How to have multiple words contained in a search

Question

I'm trying to make a search bar that continually narrows down rows in my database depending on the string used. I want to be able to have my search contain multiple words and have each word search different column.

example.db

Make      Model      Year
------    ------     ------
Toyota    Corolla    2000
Toyota    Corolla    2002
Toyota    Camry      2000
Toyota    Camry      2002
Honda     Civic      2002

This is what I have so far...

@app.route("/search")
def search():
    q = request.args.get("q") #q is a string containing make, model, and/or year 

    car = db.execute("SELECT * FROM places WHERE Make LIKE :q\
                                              OR Model LIKE :q\
                                              OR Year LIKE :q, q=q+"%")

Right now, this works if I only search for one column. If I have /search?q=corolla, it will select the two rows with model corolla. If I have /search?q=toyota+corolla, it will select no rows, since no single column has toyota corolla.

I want to make it so that the more information I enter into q, the narrower my search results would be
ie.

/search?q=toyota returns 4 rows
/search?q=toyota+corolla returns 2 rows
/search?q=toyota+corolla+2000 returns 1 row

I've been googling around for a while, but I can't seem to word my question correctly. So far, I've tried splitting the string by spaces and querying for each word, but that was just the same as searching them individually. I also tried adding a wild card in front of q, but that didn't solve the problem of having multiple words in the search at all. I'm completely stumped on what feels to be something simple. The input will always be in the order of make, model, year if that matters. Any help appreciated.

`q= '2; drop table places; --'` see [protecting-against-sql-injection-in-python](https://stackoverflow.com/questions/10950362/protecting-against-sql-injection-in-python) — Patrick Artner, Sep 29 '18 at 18:38
you would need different statements - split q and if 2 items do a `'WHERE make = :q AND model = :q'` .. not sure about the `:q` your syntax looks weird to me. - `execute(statement using placeholders, (tuple of parameters to fill placeholders))` is the normal way I see sql statements in python — Patrick Artner, Sep 29 '18 at 18:41
from a composing standpoint it would be probably better to split your routes: `/search?make=toyota&model=corolla&year=2000` for match all 3 — Patrick Artner, Sep 29 '18 at 18:44
I believe it already sanitizes the string, but just disregard it. — Song Huang, Sep 29 '18 at 18:47
Would splitting the routes allow for any number of columns? Ideally, I'd want a general solution — Song Huang, Sep 29 '18 at 18:48

score 0 · Accepted Answer · answered Sep 29 '18 at 19:01

0

You are trying to solve Full text search problem in a wrong way. I will strongly suggest you to use full text search functionality provided by database instead of comparing with columns. Please see implementation of FTS in sqlite3 here.

answered Sep 29 '18 at 19:01

nitzien

1,117
9
23

The problem became trivial after using FTS. This was exactly the behaviour I was looking for. – Song Huang Sep 29 '18 at 23:52

score 0 · Answer 2 · answered Sep 29 '18 at 19:25

I would recommend using full text search -- assuming that you are looking for full words. But you can make this work with like. Just do the comparison the other way:

WHERE :q LIKE '%' || Make || '%' OR
      :q LIKE '%' || Model || '%' OR
      :q LIKE '%' || Year || '%'

If you need for all words to match, then you can get close with:

WHERE ( (CASE WHEN :q LIKE '%' || Make || '%' THEN 1 ELSE 0 END) +
        (CASE WHEN :q LIKE '%' || Model || '%' THEN 1 ELSE 0 END) +
        (CASE WHEN :q LIKE '%' || Year || '%'  THEN 1 ELSE 0 END)
      ) = <# of words in :q>

This is not exact, but it might be sufficient for your purposes.

score 0 · Answer 3 · answered Sep 29 '18 at 21:23

I believe this case should return basically omit the filter if a given param is null....

WHERE 
(Make = @Make OR CASE WHEN @Make IS NULL THEN 1 ELSE 0 END = 1)
AND
(Model = @Model OR CASE WHEN @Model IS NULL THEN 1 ELSE 0 END = 1)
AND
(Year = @Year OR CASE WHEN @Year IS NULL THEN 1 ELSE 0 END = 1)

How to have multiple words contained in a search

3 Answers3