Filtering the tableView with args from SQL

Solar2D Widgets
#1

This is a function from my gamePlay file, the event is parsed from a button to the function updateSql(event) and the arg from the button is a SQL argument. engineResponse(sql)

I.e. (button clicked: sql = “SELECT * FROM  birds WHERE PATTERN = ‘Stripes’”  – this is the buttons self.id

[LUA]

function updateSql(event)
  tableView:deleteAllRows()
–   The event parsed from the button is sql, like: “SELECT * FROM  birds WHERE PATTERN = ‘Stripes’”

count = 0
    for row in db:nrows(sql) do
       count = count +1
       dbTable[count]={}

       dbTable[count].NAME = row.NAME
       dbTable[count].LATIN = row.LATIN
       dbTable[count].INFO = row.INFO
    end

    for i = 1, #dbTable do
        local isCategory = false
        local rowHeight = 70
        local rowColor = {
            default = { colorConverter(114, 72, 30, 0) },
        }
        local lineColor = { colorConverter(20, 20, 0, 0) }

        tableView:insertRow
        {
          rowHeight = rowHeight,
          rowColor = rowColor,
          lineColor = lineColor,
        }
  

    end
end

[/LUA]

My problem is that I can’t seam to figure out how to filter out the rows that is ~= sql

So I can start up with all the rows in my sql file and filter out when clicking on different buttons

Hope this makes sense to anyone.

#2

I fixed it with a function that builds and update the sql query:

[lua]

function sqlViewBuilder(argA, argB)
  sql = "SELECT * FROM birds WHERE "  … tostring(argA) … " = " … “’” … tostring(argB) … “’”
  return sql
end

for i = 1, #dbTable do
        local isCategory = false
        local rowHeight = 70
        local rowColor = {
            default = { colorConverter(114, 72, 30, 0) },
        }
        local lineColor = { colorConverter(20, 20, 0, 0) }

        tableView:insertRow
        {
          –  isCategory = isCategory,
          rowHeight = rowHeight,
          rowColor = rowColor,
          lineColor = lineColor,
        }

    end

  return sql

[/LUA]

If I do a call just to check:

[LUA]

sqlViewBuilder(‘PATTERN’, ‘Spotted’)

[/LUA]

It´s crucial to clear the tableView before drawing a new one, so I do that before i call the sqlViewBuilder i.e. in another function

[LUA]

tableView:deleteAllRows()

[/LUA]

I get this result in the terminal:

SELECT * FROM birds WHERE PATTERN = ‘Spotted’

Now it filters my scene view nice and tidy :slight_smile:

Next thing I will test is to make a table that I feed in all the arguments in separate strings and the do an unPack()

I will have to make a function that will deal with AND, OR for the selections to be nice and complex …but that´s another story

I will post that too for those who´s interested

…Corona coding is fun!

#3

Here´s the table constructor way I mentioned earlier in the post.

Scenario: You want to construct a complex sql query based on input from buttons or other display objects that feeds the string into an array instead of just returning the string. The little knowledge I got in SQL is that when building a fairly complex query, I have to use a *start sequence that is like “SELECT * FROM  myDB WHERE”. After that I can use AND / OR operators to narrow the search more and more like:

“SELECT * FROM  myDB WHERE Shoes = ‘Red’ AND Hair = ‘Green’ OR Hair = ‘Blue’”

here is how I did it (suppose we already have inserted stuff from i.e. some button that return strings back):

[LUA]

local mySqlView =
{
  “Shoes = ‘Red’”,
  “Hair = ‘Green’”,
  “Nails = ‘Red’”
}

[/LUA]

and then we have a function that iterates over mySqlView table and puts the *start sequence first, then it puts AND between all the children of the table -(I didn´t include OR operator because I just need AND in my game ;D).

It is obvious that we use these operators between the entries in the array except the last child because an OR or an AND is always followed by something, right? This function puts the operator between every child until it reaches the end and on the last it skips the operators.

Then I use table.concat() to concatenate all the entries in the table. I have a variable called sep(separator) I use at the start and the end just because SQL requires it.

[LUA]

local function functionSqlView()
local counter = 1
local res = {}
local sep = ‘"’
table.insert(res, "SELECT * FROM  myDB WHERE ")
  for _,v in ipairs(mySqlView) do
   if counter < #mySqlView then
    table.insert( res, (v …  " AND "))
   else
    table.insert( res, v )
   end
    counter = counter + 1
end
  print( sep … table.concat( res ) … sep )
end

[/LUA]

Then, when I call the function [Lua] functionSqlView()[/LUA]

I get this in return in the terminal:

“SELECT * FROM  myDB WHERE Shoes = ‘Red’ AND Hair = ‘Green’ AND Nails = ‘Red’”

If we look at my first example over I can just replace the last command, print, in the function functionSqlView with: [LUA] return sql = sep … table.concat( res ) … sep  [/LUA]

(this is not tried yet, but I guess it´s a valid tail call :slight_smile:

Hope this can be of any help and as I said earlier, I´m not very good at sql so any comments and suggestions here are more than welcome!

Have fun

#4

Well, I have to say that it didn´t work. The problem is that I can’t seem to update the view with right information, it just change the first entries in the tableView and it won’t  filter out those I dont want to have in the list.

#5

I’m not sure I really understand the question at hand. Are you needing help with SQL as a language?

Rob

#6

Not really I think my problem is how to get the tableView to update with my new sql query (In this game I like to first list every entry in the db and then I like to filter out and narrow the search so to say)

I use composer and everything but the updatefunction works nice! Heres a stripped version of the playGame file:

[LUA]

– THE PLAYGAME FILE:

– LOCALS FWD
local composer       = require “composer”
local scene          = composer.newScene()
local path         = system.pathForFile( “Birds.sqlite”, system.ResourceDirectory ) – IM NOT WRITING TO THE DB FILE
local imagePath     = “Images/Birds/”
local bgImage        = imagePath … “bgTheme.jpg”
local text, themeText, bg, t, count, bgItem, bg, colorConverter, tableView, leftRib, woodPanel, butNext, butBack
local leftIndent     = 50
local dbTable        = {}
local rowBg          = {}
local rowName        = {}
local rowLatin       = {}
local playButton     = {}
local onRowRender    = {}
local onRowTouch     = {}
local funtionSqlView = {}
local db             = sqlite3.open( path )
local _W, _H         = display.contentWidth, display.contentHeight
local group          = {}
local containerGroup = display.newGroup( )
local dashboardGroup = display.newGroup( )

–GLOBALS FWD
mySqlView            = {} – I CHOOSE TO HAVE THIS GLOBAL FOR NOW
_G.specPool          = {}
_G.catText           = “Push one of the buttons”
updateSql            = {}

– FUNCTIONS
function functionSqlView() – THIS IS THE SQL STATEMENT BUILDER (YOU CAN SEE IN THE TERMINAL)… LAME NAME, I KNOW HAHA
  local counter = 1
  local res     = {}
  local sep     = ‘"’      – FOR LAZYNESS

  table.insert(res, "SELECT * FROM birds WHERE ")
  for _,v in ipairs(mySqlView) do
    if counter < #mySqlView then
      table.insert( res, (v …  " AND ")) – I WILL DEAL WITH ‘OR’ LATER ;D
    else
      table.insert( res, v )
    end
    counter = counter + 1
  end
  sql = sep … table.concat( res ) … sep – CONCATENATES THE WHOLE STATEMEMT STRING
  updateSql(sql) 

– HERE IS WHERE I START TO GET LOST, I HAVE THE STATEMENT AND I WANT TO UPDATE AND REMOVE
– THE ROWS THAT DOESN´T MEET THEESE CRITERIAS… FOLLOW DOWN TO UPDATESQL FUNCTION
end

local function onSystemEvent( event )
if ( event.type == “applicationExit” ) then
  db:close()
end
end

local function colorConverter(r, g, b, a)  – JUST SO I CAN USE RGB VALUES DIRECT FROM PHOTOSHOP OR ILLUSTRATOR
   local colorR, colorG, colorB, alphA
      colorR = r/256; colorG = g/256; colorB = b/256
      alphA = a or nil
   return colorR, colorG, colorB, alphA
end

function updateCatText() – FOR UPDATING THE GLOBAL CATEGORY TEXT OVER THE BUTTONS
if themeText then display.remove( themeText) end
themeText = display.newText( tostring(_G.catText), 0, 0, _G.msgFont , 34 )
    themeText.anchorX, themeText.anchorY = 0, 0
    themeText.x, themeText.y = leftIndent, (woodPanel.y - woodPanel.height) + 20
    themeText:setFillColor( colorConverter(236, 210, 135, 1) )

return true
end

–[[

SCENARIO:

I start the app with the sql query showing all rows in db without any limit, like this:

“SELECT * FROM birds”

The function over called functionSqlView() produces sql queries based on what buttons i decide to click on.

Let´s say I hit two buttons, then my sql query could look like this:

“SELECT * FROM birds WHERE WINGS = ‘Long’ AND TAIL = ‘Short’”

WHAT COMES HERE IS WHAT I REALLY DONT KNOW!!

  1. I GOT TO TRAVERSE THE RIGHT ROWS FROM THE DB
  2. THE LIST OF BIRDS IN THE TABLEVIEW WILL SHOW LESSER AND LESSER ENTRIES
        THE MORE I NARROW THE SEARCH.

–]]

function updateSql(event)
tableView:deleteAllRows() – FIRST I LIKE TO REMOVE THE ROWS AND THAT WORKS OF COURSE

    for i = 1, #dbTable do
        local isCategory = false
        local rowHeight = 70
        local rowColor = {
            default = { colorConverter(114, 72, 30, 0) },
        }
        local lineColor = { colorConverter(20, 20, 0, 0) }

        – Insert the row into the tableView
        tableView:insertRow
        {
          –  isCategory = isCategory,
          rowHeight = rowHeight,
          rowColor = rowColor,
          lineColor = lineColor,
        }
    end

end

– THIS ONE WORKS NICE AND IT TAKES YOU OVER TO A SPEC PAGE AND SO ON…
local function birdSpec(self, event)
   if event.phase == “began” then
   if _G.debug == 1 then print("** birdSpec: \n\n") end    – DEBUG
      _G.specPool.name  = self.specName
      _G.specPool.latin = self.specLatin
      _G.specPool.info  = self.specInfo
      composer.gotoScene( “playSpecScene” )
   elseif event.phase == “cancelled” or event.phase == “ended” then
   end
   return true
end

function onRowRender( event )
    local phase = event.phase
    local row = event.row

    row.bg = display.newRoundedRect( 0, 0, display.contentWidth -48, 50, 12 )
    row.bg.strokeWidth = 0
    row.bg:setFillColor( colorConverter(236, 210, 135, 1) )
    row.bg.alpha = 1
    row.bg.anchorX = 1
    row.bg.anchorY = 0
    row.bg.x = display.contentWidth
    row.bg.y = 10
    row:insert( row.bg )

    row.title = display.newText( row, dbTable[row.index].NAME, 0, 0, _G.msgFont , 24 )
    row.title.x = leftIndent + 10
    row.title.anchorX = 0
    row.title.y = row.contentHeight * 0.15+ row.bg.y
    row.title:setFillColor( colorConverter(114, 72, 30, 1) )

    row.ingress = display.newText( row, dbTable[row.index].LATIN, 0, 0, _G.msgFont , 14 )
    row.ingress.x = leftIndent + 10
    row.ingress.anchorX = 0
    row.ingress.y = row.title.y +20
    row.ingress:setFillColor( colorConverter(114, 72, 30, 1) )

    row.image = display.newImageRect(row, tostring(imagePath … dbTable[row.index].NAME) … “.jpg”, 45,45 )
    row.image:addEventListener( “touch”, row.image)
    row.image.touch = birdSpec
    row.image.anchorX = 1
    row.image.anchorY = 0.25
    row.image.x = display.viewableContentWidth - row.image.width/2
    row.image.y = row.title.y

    – PARSE VARIABLE TO THE GLOBAL SPECPOOL TABLE
    row.image.specName = dbTable[row.index].NAME
    row.image.specLatin = dbTable[row.index].LATIN
    row.image.specInfo = dbTable[row.index].INFO

end

– “scene:create()”
function scene:create( event )
   local sceneGroup = self.view

   --Bird.group = Bird.group
    bg = display.newImageRect( bgImage, _W, _H, 375, 667, true )
    bg.anchorX, bg.anchorY = 0, 0
    bg.alpha = 1
    sceneGroup:insert(bg)

    leftRib = display.newImageRect( “leftRib.png”, 48, 793, true )
    leftRib.anchorX, leftRib.anchorY = 0, 0
    leftRib.alpha = 1
    sceneGroup:insert(leftRib)

    woodPanel = display.newImageRect( “woodPanel.png”, 375, 131, true )
    woodPanel.x, woodPanel.y = display.contentWidth/2, display.contentHeight
    woodPanel.anchorX, woodPanel.anchorY = 0.5, 1
    woodPanel.alpha = 1
    sceneGroup:insert(woodPanel)

    themeText = display.newText( tostring(_G.catText), 0, 0, _G.msgFont , 18 )
    themeText.anchorX, themeText.anchorY = 0, 0
    themeText.x, themeText.y = leftIndent, (woodPanel.y - woodPanel.height) + 30
    themeText:setFillColor( colorConverter(236, 210, 135, 1) )
    sceneGroup:insert(themeText)
end

– “scene:show()”
function scene:show( event )
   local sceneGroup = self.view
   local phase = event.phase
   if ( phase == “will” ) then
   composer.removeHidden( )   
   elseif ( phase == “did” ) then

– load the data into a table array

– WHEN I FIRE IT UP THE FIRST TIME IT WILL SHOW ALL ENTRIES
if sql == nil then sql = “SELECT * FROM birds” print(“Initiated SQL *”) end

    count = 0

      for row in db:nrows(sql) do
         count = count +1
         dbTable[count]={}
         dbTable[count].NAME = row.NAME
         dbTable[count].LATIN = row.LATIN
         dbTable[count].INFO = row.INFO
      end

    tableView = widget.newTableView
    {
        top = 0,
        width = _W+1,
        height = display.contentHeight,
        hideBackground = true,
        isBounceEnabled = true,
        listener = tableViewListener,
        onRowRender = onRowRender,
    }

    for i = 1, #dbTable do
        local isCategory = false
        local rowHeight = 70
        local rowColor = {
            default = { colorConverter(114, 72, 30, 0) },
        }
        local lineColor = { colorConverter(20, 20, 0, 0) }

        – Insert the row into the tableView
        tableView:insertRow
        {
          –  isCategory = isCategory,
          rowHeight = rowHeight,
          rowColor = rowColor,
          lineColor = lineColor,
        }

    end
    sceneGroup:insert(tableView)

  end

end

– “scene:hide()”
function scene:hide( event )
   local sceneGroup = self.view
   local phase = event.phase
   if ( phase == “will” ) then
   elseif ( phase == “did” ) then
if tableView and tableView:getNumRows()>1 then tableView:deleteAllRows()
  print(“TableView iterated and deleted from scene hide in playGame”)
end

_G.catText = “”
updateCatText()

   end
end

function scene:destroy( event )
local sceneGroup = self.view

end

– Listener setup
scene:addEventListener( “create”, scene )
scene:addEventListener( “show”, scene )
scene:addEventListener( “hide”, scene )
scene:addEventListener( “destroy”, scene )

return scene

[/LUA]

Hope anyone can help me

#7

I understand the problem now. I, however; don’t see where your creating your buttons and populating mySqlView. Though I think I have enough here to answer the basic question.

You’re over complicating your “functionSqlView” function. I would drop all the table stuff and just deal with strings. Lets start with the assumption that your buttons can produce three pieces of information:   Key (“WING”, “TAIL”, etc.), Value (“Long”, “Short”, etc.) and condition (“AND”, “OR”, nil). I would create a table call attributes and for each entry, have it contain these three values:

local birdAttributesQuery = {}

birdAttributesQuery[#birdAttributesQuery + 1] = { key = “WING”, value = “Long”, condition = “AND” },

birdAttributesQuery[#birdAttributesQuery + 1] = { key = “TAIL”, value = “Short”, condition = nil }

The #birdAttributesQuery + 1 will auto increment the table.

Then you could build your functionSqlView() to do:

local function functionSqlView() &nbsp;&nbsp;&nbsp; local query = "SELECT \* FROM birds WHERE" &nbsp;&nbsp;&nbsp; for i = 1, #birdAttributesQuery do &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; query = query .. " " .. birdsAttributesQuery[i].key .. " = " .. birdAttributesQuery[i].value &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if birdAttributesQuery[i].condition then &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; query = query .. " " .. birdAttributesQuery[i].condition &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end &nbsp;&nbsp;&nbsp; end -- -- now execute your query -- delete the previous tableView rows -- loop over the returned query --&nbsp;&nbsp;&nbsp;&nbsp; repopulate the table values you use to populate the tableView row in onRowRender() --&nbsp;&nbsp;&nbsp;&nbsp; insert a new tableView row (triggering the onRowRender) -- end end

Your onRowRender() and a bunch of your functions should come above this in your code (so they are forwardly declared properly to help with the globals…

Rob

#8

Thanx for the answer Rob
I have no trouble with the building and executing sql queries. My trouble is how I remove the rows that does NOT meet my criterias and how they will be removed from the display and the respective table. I have been reading mouch about widgets and making this work in a composer environment like I want. If I´m thinking loud here I like to think like this:
I iterate through the db file in ipairs with the sql query string as argument - this gives me the rows that meet the criterias that I ask for
NOW: How do I remove the ones that shouldn´t be there?
I have really tried for some time now in composer ( I see that other coders like Ingemar use api calls that I cant find in the api list, why?)

#9

I think this is were I’m having a disconnect. You remove all the rows from the tableview. This gives you an empty tableView.  Then if your SQL query returns just the rows you want, insert the rows into the tableView using the data returned from the query.

If you see Ingemar use API calls that you can’t find, that means he’s using some library either of his creation or of someone else. He could also have taken the open source version of our widgets and modified them with extra features.

Rob

#10

This is JUST what im having trouble doing Rob, I have the query string, I have deleted all the rows, but I cant populate with the new query. It wont work for me :frowning: What and where do I put what you sugest in your most recent post?

#11

Maybe something like:
 

local function functionSqlView() local query = "SELECT \* FROM birds WHERE" for i = 1, #birdAttributesQuery do query = query .. " " .. birdsAttributesQuery[i].key .. " = " .. birdAttributesQuery[i].value if birdAttributesQuery[i].condition then query = query .. " " .. birdAttributesQuery[i].condition end end tableView:deleteAllRows() -- FIRST I LIKE TO REMOVE THE ROWS AND THAT WORKS OF COURSE count = 0 dbTable = {} for row in db:nrows( query ) do count = count +1 dbTable[count]={} dbTable[count].NAME = row.NAME dbTable[count].LATIN = row.LATIN dbTable[count].INFO = row.INFO local isCategory = false local rowHeight = 70 local rowColor = { default = { colorConverter(114, 72, 30, 0) }, } tableView:insertRow { -- isCategory = isCategory, rowHeight = rowHeight, rowColor = rowColor, lineColor = lineColor, } end end
#12

I try the new code and I must say that I like the auto increment tweak you did on birdAttributesQuery and that you define the value so clear as you did here Rob! Mine works to but Its a littlest more “foggy” haha :slight_smile:

but I just get no rows at all, is this because I have to force onRowrender to render? (My debug system tells me that it do not trigger onRowrender after I click.

What is the best practice on forcing row rendering?

It stopps just after deleting the rows and nothing more happens

#13

The on screen rows should auto render when they are inserted. any thing going on in your logs?

#14

Nothing going on in the logs, uhh, this drives me nuts really! man. I cant get this to work at all. It all stops when I try to update the rows.

I sendt you a zip here the other day, can you please see if its just me or a bug or something?

#15

Can you zip up your latest with the suggestions I made above?

#16

Heres the link: www.ferrobet.no/NatureApp.zip

I have adjusted the content so it matches the db file :slight_smile:

The way I made it is like the class: ButtonsBirds.lua -(inside the Classes folder), inserts the button args into the table mySqlView trough the function engineResponse() --this can be like if I press the Red bodycolour button it will send “BODYCOLOUR = ‘Red’” or in your way: table.insert(birdAttributesQuery, unpack({ key = “ART”, value = “Andefamilien”, condition = “AND” })) , right?

OK, I will be extremely glad if you can help with this “little thing” that stops my whole project Rob :slight_smile:

Thanx for all help

#17

Rob

Did you find anything?

#18

I’ve not gotten to it yet. With any luck I can look at it tonight.

Rob

#19

Your app comes up with a black screen and no errors in the console.  Can I get working project?

Rob

#20

Ahh, sorry Rob I uploaded a wrong version

Heres the link: www.ferrobet.no/NatureApp.zip

This is not with your piece of code because the function functionSqlView() produces the right statements when you click one of the buttons at the bottom. I have been reading one of your replies in another forum post where you discuss where to put the different components of the tableView structure when dealing with composer and going back and forth through composer pages/scenes.

So I rearranged my code and took the onRowrender function from will show over to create scene then it started to re-render my rows again :slight_smile:

Anyway, I think my real q here is: how do I sort and show the data I now have to the display?

I think the problem lies at line 61 in playGame file

When this is sorted out, I will use your approach on the functionSqlView function

…I liked it ;D