SQLite Forum

Help with a search command
Login

Help with a search command

(1) By anonymous on 2021-01-26 18:05:17 [source]

Hi, I am using Lazarus on a Linux PC and SQLite3. I have a small home-use database for random data. Each Page has 1-Text Data Field and 10-Key Data Fields. 

With a lot of searching and reading I figured out the code below which works OK as I am only dealing with several hundred Records. 

Often I get many pages with the same Key in one Field or another and getting a lot of results to a search. I'd like to add in another Key Field with "AND" to be more specific on the returned Records. But, I cannot figure it out.

I wrote a routine to iterate through the entire Data File checking with "and" Fields, but it is slow compared to the native-SQLite search.

Could some kind soul please help out here.

The code...
    tStr1:='%'+edSearch.Text+'%';
    sqlStr:='SELECT * FROM sqlDiaryData '+
      ' WHERE ( fldKey0 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey1 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey2 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey3 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey4 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey5 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey6 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey7 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey8 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR ( fldKey9 LIKE '+QuotedStr(tStr1)+' ) '+
         ' ORDER BY '+fldKey0;
  dm.sqlDiaryData.SQL.Text:=sqlStr;

(2) By Ryan Smith (cuz) on 2021-01-26 19:02:55 in reply to 1 [link] [source]

This is always going to be a slow query, but should work just fine.

What is the wrapper or Unit you are using? i.e. what Type is "dm"?

Anyway, the code you are probably looking for is: (assuming "tstr2" is the second text to search by)

    tStr1:='%'+edSearch.Text+'%';
    tStr2:='%'+edSearch2.Text+'%';
    sqlStr:='SELECT * FROM sqlDiaryData '+
      ' WHERE (( fldKey0 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey1 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey2 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey3 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey4 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey5 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey6 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey7 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey8 LIKE '+QuotedStr(tStr1)+' ) '+
         ' OR  ( fldKey9 LIKE '+QuotedStr(tStr1)+' ) '+
         ') '+ 
         'AND (( fldKey0 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey1 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey2 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey3 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey4 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey5 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey6 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey7 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey8 LIKE '+QuotedStr(tStr2)+' ) '+
         ' OR  ( fldKey9 LIKE '+QuotedStr(tStr2)+' ) '+
         ')'+
         ' ORDER BY '+fldKey0;
  dm.sqlDiaryData.SQL.Text:=sqlStr;

A better solution would be a wrapper that allows preparing a statement and binding those fields, but the above should work fine.

May I further suggest, out of pedanticness, this algorithm:

    // Assuming tStr1 and tStr2 are the two filters and get filled from some text boxes:
    tStr1:=QuotedStr('%'+edSearch1.Text+'%');
    tStr2:=QuotedStr('%'+edSearch2.Text+'%');

    // These two nearly identical loops should really be made into a function.
    tStrP1:='';
    for f:=0 to 9 do begin
      tStrF:=Format('(fldKey%d LIKE %s)', [f,tStr1]);
      if (f>0) then tStrF := ' OR '+tStrF;
      tStrP1:=tStrP1+tStrF;
    end;
    tStrP2:='';
    for f:=0 to 9 do begin
      tStrF:= Format('(fldKey%d LIKE %s)', [f,tStr2]);
      if (f>0) then tStrF:=' OR '+tStrF;
      tStrP2:=tStrP2+tStrF;
    end;

    // Much easier to code and visualize now:
    sqlStr:='SELECT * FROM sqlDiaryData WHERE ('+tStrP1+') AND ('+tStrP2+');';

(3) By jake on 2021-01-27 01:11:12 in reply to 1 [link] [source]

Assuming performance is never going to be an issue, then something like this might appeal to you:

.mode table

CREATE TABLE sqlDiaryData(
  fldKey0 TEXT,
  fldKey1 TEXT,
  fldKey2 TEXT,
  all_fields AS (fldKey0 || fldKey1 || fldKey2)
);
INSERT INTO sqlDiaryData VALUES ('this', 'is', 'test'), ('abc', 'def', 'test'), ('123abc345', 'test', 'foo'), ('123', 'xyz', 'bar');

-- 2 rows expected
WITH search(search_term) AS(VALUES ('test'), ('abc'))
SELECT dd.*
  FROM sqlDiaryData dd
  JOIN search       s  ON dd.all_fields LIKE '%' || s.search_term || '%'
 GROUP BY dd.rowid
HAVING Count(*) = (SELECT Count(*) FROM search);
+-----------+---------+---------+------------------+
|  fldKey0  | fldKey1 | fldKey2 |    all_fields    |
+-----------+---------+---------+------------------+
| abc       | def     | test    | abcdeftest       |
| 123abc345 | test    | foo     | 123abc345testfoo |
+-----------+---------+---------+------------------+

(4) By John Dennis (jdennis) on 2021-01-27 03:47:47 in reply to 3 [link] [source]

This solution will also return rows of the form ('it', 'estimates', 'abc')

(5) By jake on 2021-01-27 04:21:28 in reply to 4 [link] [source]

Good observation. Depending on the requirements of the OP, it might be sufficient to avoid such cases by defining all_fields with a separator. E.g. using a space character : (fldKey0 || ' ' || fldKey1 || ' ' || fldKey2)

(6) By John Dennis (jdennis) on 2021-01-27 06:09:40 in reply to 5 [link] [source]

It could be used, as is, as the first step in a WITH select to restrict the number of rows, which could then subsequently be queried with the original set of OR conditions. A bit more work in the query though...

(7) By Gunter Hick (gunter_hick) on 2021-01-27 07:41:28 in reply to 1 [link] [source]

Having fields with a serial number in their name suggests that your schema is not normalized, either by omission or by design. Since this is a home-use project...

There appear to be 2 "entities" in your application: "Diary entries" and "Keywords", so lets put them into separate tables:

CREATE TABLE entry (id INTEGER PRIMAY KEY, content TEXT);
CREATE TABLE keyword (id INTEGER PRIMARY KEY, word TEXT COLLATE NOCASE UNIQUE);

The "diary entries" and the "keywords" have an N:M relationship, meaning that each "diary entry" may have 0 or more kewords, and each keyword my refer to 0 or more "diary entries". This goes into another table

CREATE TABLE entry_keyword (entry_id INTEGER, key_id INTEGER, UNIQUE(entry_id, key_id));

Your query then becomes a join:

SELECT e.text FROM keyword k JOIN entry_keyword ek ON (k.id = ek.key_id) JOIN entry e ON (e.id = ek.entry_id) WHERE k.word LIKE '%<text>%';

You can look into foreign keys, referential integrity actions, and ON CONFLICT clauses later.

(8) By anonymous on 2021-01-28 15:58:50 in reply to 1 [link] [source]

WOW, thanks to everyone who responded. I will copy all replies and work through the options to expand my knowledge. Some options are way beyond my skill level (probably obvious with my code above {grin}) 

For now though I will use the "AND" by Ryan Smith (cuz). I had tried several versions of that approach before posting here, but could not quite get my 84-year old brain around the logic. So simple now that I see it. 

Thanks again for all being so willing to share.