Filtering the tableView with args from SQL

Solar2D Widgets
#41

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

#42

Rob

Did you find anything?

#43

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

Rob

#44

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

Rob

#45

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

#46

Where does your database exist? I didn’t see it in system.DocumentsDirectory. I don’t see where you’re creating/populating it.

Rob

#47

You are right, when I check the proj sandbox and it´s nothing there? but it fires up the db that lies in the root dir: ‘Birds.sqlite’ in line 250 (where I iterate over the sql statement), in playGame if I´m not mistaken

but I´m not shure

If you like to se how I like it to be you can first run the code in sim and see (you see all entries), then you can put i.e. this statement in line 245 right before count = 0: 

[LUA]“SELECT * FROM birds WHERE COLOR1 = ‘Sort’”[/LUA]

and save. what you now see in the simulator is a narrow selection just like I want right?

#48

I made a video that describes better my situation:

https://www.dropbox.com/s/z16sowhc2gtmpfq/BirdAppComposer.mp4?dl=0 

This shows how I want it to work in the update process :slight_smile:

#49

There is an issue with the SQL going to the db:nrows() call that’s not returning anything from the query.  Still investigating.

#50

I will like to thank you, Rob for all help you have done for me

Everything is working nicely now. Here´s the trick from your code that helped me out :slight_smile:

[LUA]

function functionSqlView() 

  local sql = nil
  tableView:deleteAllRows()

  local counter = 1
  local res     = {}

– NOW WE BUILD THE SQL QUERY IN A FORLOOP FROM THE mySqlView TABLE, INSERTED FROM THE SUBBUTTONS VALUES
  sql = "SELECT * FROM birds WHERE "
  for k, v in ipairs(mySqlView) do
    sql = sql … v … " "
    if counter < #mySqlView then
      sql = sql … " AND "
    end
    counter = counter + 1
  end

      dbTable = {}  – START WITH A FRESH TABLE

      count = 0  – AND FRESH COUNTER

– PREPARE THE ROWS SELLECTION BASED ON THE DATABASE ROWS THROUGH THE SQL QUERY
      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
– STYLING FOR THE ROWS ON SCREEN
    for i = 1, #dbTable do
        local isCategory = false
        local rowHeight = 95
        local rowColor = {
            default = { colorConverter(114, 72, 30, 0) },
        }
        local lineColor = { colorConverter(20, 20, 0, 0) }

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

[/LUA]

Again, thanx a lot Rob

#51

Quite welcome!