Search, match and sort sqlite db

Getting Started Newbie Questions
#1

It’s been a while since I worked with corona so I’m a bit of a noob again.

I’m trying to create an app that search an sqlite db for matching entries depending what the user enter into a textfield. My db has one table ,NAMES, with two fields, NAME_ID and NAME, so if the user type “AOD” then the app sort through the db for words starting with A but contains an O and a D in that order and insert them into a scrollview starting with the longest name.

Thanks
C.

[import]uid: 65840 topic_id: 35342 reply_id: 335342[/import]

#2 
local query = "SELECT \* from NAMES where NAME LIKE '%" .. stringToSearchFor .. "%'"

will search for AOD anywhere in NAME. If it must start with AOD, leave off the first % sign.

Execute the query and follow the examples for grabbing the data and the widget.newTableView() examples for inserting the data.

[import]uid: 199310 topic_id: 35342 reply_id: 140477[/import]

#3

Thanks Rob for the help, I followed a link from the sqlite tutorial and found that out. It’s my first time with sqlite but I’m getting there… slowly…

I’ve made som progress, getting the result into a tableview or scrollview was easy but my problem is how do I match the the rest of the letters?

I sort through the db words starting with the first letter of the input field string was easy but now I want to sort that result if they contain the other letters.

So in my mind I think;

Find all words starting with “first letter” --> within that result find all words containing letter 2 and 3 --> within that result, find all words where letter 2 occur before letter 3 --> sort that result by length of word --> print result/ display result.
I’ve stripped out the widget stuff etc but here’s what I got so far.

[code]
local sqlite = require(“sqlite3”)
local path = system.pathForFile(“name_list.sqlite”, system.DocumentsDirectory)
local db = sqlite3.open(path)

local first = string.sub(inputString, 0, 1)

– LIMIT is only there to because it’s just too many results atm…
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%’ LIMIT 10"

for row in db:nrows(sql) do

nameArray =
{
name = row.name,
num = string.len(row.name)
}
print( "Names: "…nameArray[“name”])

print("Count: "…nameArray[“num”])

end
[/code] [import]uid: 65840 topic_id: 35342 reply_id: 140494[/import]

#4

I guess I don’t understand what you’re trying to accomplish.

Give me everything that starts with A… fine, that easy but then filter on AO well that’s the same as finding AO in the first place. Then looking for words that start with AO that has a D in the 3rd position seem to be the same as finding AOD in the first place.

Unless you’re trying to do something like a search program where your tableview shows all the A options as soon as the person types an A in to a search box, then filters that down and if thats the case it’s kind of a different question. But I think it’s the same answer.

They enter an A, you do the DB query, build the tableView.
They now add an O to the search screen, you do the DB query for AO, delete all the table rows, re-insert the new results.
They now type in a D, query for AOD, delete the table rows, insert the new results.
[import]uid: 199310 topic_id: 35342 reply_id: 140502[/import]

#5

This seem to work but is it the right way?
I realized that there are not only names in the db but also a few words with less that 3 characters, how do I sort them out? I don’t plan on doing any mods of the db cuz it’s +150k names/words.

If I want to sort the result by the length of the words, how do I go about doing that?

local sql = "SELECT \* FROM names WHERE name LIKE '"..first.."%"..second.."%"..third.."%'" [import]uid: 65840 topic_id: 35342 reply_id: 140507[/import]

#6

I just realized, my db query do sort out words less than 3char. Now I just need to figure out how to sort the results by word length.

Getting there… :slight_smile: [import]uid: 65840 topic_id: 35342 reply_id: 140509[/import]

#7 
local sql = "SELECT \* FROM names WHERE name LIKE '"..first..second..third.."%'"

I think will work better for you. Is where you’re getting first, second and third from already a string? You can just search for the word with a % on the end to match the first X number of characters.
[import]uid: 199310 topic_id: 35342 reply_id: 140511[/import]

#8

Yes, first, second and third are the letters entered in the textfield and saved as a storyboard variable.

Your way just return words starting with those letters in that order, my way results in 4 times more entries and the letters can occur anywhere in the word/names.

I just can’t get a grasp on how to sort the entries by word length…

this returns the length but how to sort?
string.len(row.name) [import]uid: 65840 topic_id: 35342 reply_id: 140515[/import]

#9

Have you tried slapping on a "ORDER BY string.len(row.name)" at the end of the SQL statement? [import]uid: 70847 topic_id: 35342 reply_id: 140539[/import]

#10

Here’s what I have tried but they all throw error because of string.len()

Does anyone have any ideas how to make it work?

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY string.len(row.name) DESC LIMIT 500"

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY “…string.len(row.name)…” DESC LIMIT 500"

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY “…string.len(…“row.name”…)…” DESC LIMIT 500"

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY “…string.len(…“name”…)…” DESC LIMIT 500"

#11

It has come to the point where I’m thinking of inserting a new row to the db called “count”, perform a check of word length for entire db and insert that result so I can sort easier, but since the db is so huge and is downloaded from 3rd party I may have to do that every time.

Very frustrating…aaargh!!! [import]uid: 65840 topic_id: 35342 reply_id: 140605[/import]

#12

Ooops. I had a typo in my previous reply.
SQLite should be able to process this: "ORDER BY length(name)". [import]uid: 70847 topic_id: 35342 reply_id: 140606[/import]

#13

That worked:)

Now that it get “ORDER BY length(name)” is there a way to tell sqlite only pick names <= 9 char?

I have tested with "ORDER BY length(name) <= 9 " but that breaks sorting by “DESC”.

What am I doing wrong? [import]uid: 65840 topic_id: 35342 reply_id: 140617[/import]

#14

This should do it:

local sql = "SELECT \* FROM names ".. "WHERE name LIKE '"..first.."%"..second.."%"..third.."%' ".. "AND length(name) \<= 9 ".. "ORDER BY length(name) DESC LIMIT 500" [import]uid: 70847 topic_id: 35342 reply_id: 140620[/import]

#15

That did the trick :slight_smile:

thanks [import]uid: 65840 topic_id: 35342 reply_id: 140623[/import]

#16

(^_^) [import]uid: 70847 topic_id: 35342 reply_id: 140626[/import]

#17 
local query = "SELECT \* from NAMES where NAME LIKE '%" .. stringToSearchFor .. "%'"

will search for AOD anywhere in NAME. If it must start with AOD, leave off the first % sign.

Execute the query and follow the examples for grabbing the data and the widget.newTableView() examples for inserting the data.

[import]uid: 199310 topic_id: 35342 reply_id: 140477[/import]

#18

Thanks Rob for the help, I followed a link from the sqlite tutorial and found that out. It’s my first time with sqlite but I’m getting there… slowly…

I’ve made som progress, getting the result into a tableview or scrollview was easy but my problem is how do I match the the rest of the letters?

I sort through the db words starting with the first letter of the input field string was easy but now I want to sort that result if they contain the other letters.

So in my mind I think;

Find all words starting with “first letter” --> within that result find all words containing letter 2 and 3 --> within that result, find all words where letter 2 occur before letter 3 --> sort that result by length of word --> print result/ display result.
I’ve stripped out the widget stuff etc but here’s what I got so far.

[code]
local sqlite = require(“sqlite3”)
local path = system.pathForFile(“name_list.sqlite”, system.DocumentsDirectory)
local db = sqlite3.open(path)

local first = string.sub(inputString, 0, 1)

– LIMIT is only there to because it’s just too many results atm…
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%’ LIMIT 10"

for row in db:nrows(sql) do

nameArray =
{
name = row.name,
num = string.len(row.name)
}
print( "Names: "…nameArray[“name”])

print("Count: "…nameArray[“num”])

end
[/code] [import]uid: 65840 topic_id: 35342 reply_id: 140494[/import]

#19

I guess I don’t understand what you’re trying to accomplish.

Give me everything that starts with A… fine, that easy but then filter on AO well that’s the same as finding AO in the first place. Then looking for words that start with AO that has a D in the 3rd position seem to be the same as finding AOD in the first place.

Unless you’re trying to do something like a search program where your tableview shows all the A options as soon as the person types an A in to a search box, then filters that down and if thats the case it’s kind of a different question. But I think it’s the same answer.

They enter an A, you do the DB query, build the tableView.
They now add an O to the search screen, you do the DB query for AO, delete all the table rows, re-insert the new results.
They now type in a D, query for AOD, delete the table rows, insert the new results.
[import]uid: 199310 topic_id: 35342 reply_id: 140502[/import]

#20

This seem to work but is it the right way?
I realized that there are not only names in the db but also a few words with less that 3 characters, how do I sort them out? I don’t plan on doing any mods of the db cuz it’s +150k names/words.

If I want to sort the result by the length of the words, how do I go about doing that?

local sql = "SELECT \* FROM names WHERE name LIKE '"..first.."%"..second.."%"..third.."%'" [import]uid: 65840 topic_id: 35342 reply_id: 140507[/import]