Help with a search command
(1) By anonymous on 2021-01-26 18:05:17 [link]
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]
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]
Assuming performance is never going to be an issue, then something like this might appeal to you: ```sql .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); ``` ```bash +-----------+---------+---------+------------------+ | 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
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]
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]
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]
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]
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.