Help with a search command
(1) By anonymous on 2021-01-26 18:05:17 [link] [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 [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.