Android Question B4XTable number of table rows after a search

R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
The B4XTable class (in the B4X lib file) has the variable mCurrentCount, indicating the total number of rows of the table.
However it seems this is not available when you have run a filter (SQL with where clause). For this reason the last page
button (>|) is always greyed out as the last page can't be calculated without the total number of table rows.
Of course it is easy to get the total number of table rows with a simple SQL eg:

B4X: 
Sub GetCountOfRowsFromSearch As Int
    mCurrentCount = sql1.ExecQuerySingleResult2("select count(*) from data " & strCurrentWhereClause, marrSearchArgs)
    Return mCurrentCount
End Sub

To be run perhaps from the DataUpdated event.
I just want to make sure that I am not overlooking something and add unnecessary code.

RBS
 
Sort by date Sort by votes
teddybear

teddybear

Well-Known Member
Licensed User
How do you run a filter? do you use B4XTable1.CreateDataView?
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
teddybear said:
How do you run a filter? do you use B4XTable1.CreateDataView?
Click to expand...
I am referring to the provided example project that came with the first post from Erel regarding B4XTable. So just type in the search text and apply the filter. No user code involved there.

RBS
 
Upvote 0
teddybear

teddybear

Well-Known Member
Licensed User
RB Smissaert said:
I am referring to the provided example project that came with the first post from Erel regarding B4XTable. So just type in the search text and apply the filter. No user code involved there.

RBS
Click to expand...
Do you have the example link ?
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
DonManfred said:
Maybe it´s another than we would expect.

Lets wait for an answer fro @RB Smissaert with a link ;-)
Click to expand...
This is the link to the original post I was referring to:

[B4X] B4XTable - Cross platform, sortable, searchable, customizable table

B4XTable is a paged based table. The UI is made of a horizontal xCLV. The data is stored in an in-memory SQLite database. How to use? 1. Add a B4XTable with the designer. 2. Add the columns: B4XTable1.AddColumn("US County", B4XTable1.COLUMN_TYPE_NUMBERS) B4XTable1.AddColumn("Name"...
www.b4x.com

mCurrentCount is a module level variable in the B4XTable class. It is available but doesn't give you the number of table rows. I think it gives -1.

RBS
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Log(B4XTable1.mCurrentCount) will not work and will always yield -1 if you have a search string in the search box. If you want the record count with a search string in the search box, you can use the below snippet. It works:
B4X: 
Sub GetCountOfRowsFromSearch As Int
    Dim s As String = "%" & B4XTable1.SearchField.Text & "%"
    Dim o() As Object = B4XTable1.BuildQuery(False)   
    Dim strQuery As String ="SELECT count(*) FROM data WHERE c1 LIKE ? OR c2 LIKE ? OR c3 LIKE ?"  'use your columns
    o(0)=strQuery
    o(1)=Array As String(s, s, s)
    Return B4XTable1.sql1.ExecQuerySingleResult2(o(0), o(1))
End Sub
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Mahares said:
Log(B4XTable1.mCurrentCount) will not work and will always yield -1 if you have a search string in the search box. If you want the record count with a search string in the search box, you can use the below snippet. It works:
B4X: 
Sub GetCountOfRowsFromSearch As Int
    Dim s As String = "%" & B4XTable1.SearchField.Text & "%"
    Dim o() As Object = B4XTable1.BuildQuery(False) 
    Dim strQuery As String ="SELECT count(*) FROM data WHERE c1 LIKE ? OR c2 LIKE ? OR c3 LIKE ?"  'use your columns
    o(0)=strQuery
    o(1)=Array As String(s, s, s)
    Return B4XTable1.sql1.ExecQuerySingleResult2(o(0), o(1))
End Sub
Click to expand...
> you can use the below snippet.

Yes, I know that and mentioned that already. I just wondered if there was no need for such code as that information
was already somewhere in that B4Xtable class.

RBS
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
RB Smissaert said:
Yes, I know that and mentioned that already.
Click to expand...
No. You did not mention it anywhere in your thread. Show me where in your code you mention it. If you are referring to the code in post #1, either you did not explain what those variables are or the variables you use do not exist in the class module
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Mahares said:
No. You did not mention it anywhere in your thread. Show me where in your code you mention it. If you are referring to the code in post #1, either you did not explain what those variables are or the variables you use do not exist in the class module
Click to expand...
In the first post:

B4X: 
Sub GetCountOfRowsFromSearch As Int
    mCurrentCount = sql1.ExecQuerySingleResult2("select count(*) from data " & strCurrentWhereClause, marrSearchArgs)
    Return mCurrentCount
End Sub

strCurrentWhereClause is the where clause of the mentioned search.
marrSearchArgs are the search parameter arguments of that same search.

I thought this was quite obvious.

RBS
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
RB Smissaert said:
I thought this was quite obvious.
Click to expand...
That is not obvious at all. That is why people have a hard time following and understanding your threads. You put variables without explaining what they are and stifle everyone. Compare my code in post #9 and yours in post #1. They are not even close. I am sorry, but I am no longer interested in addressing your threads or attempt to answer them.
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Mahares said:
That is not obvious at all. That is why people have a hard time following and understanding your threads. You put variables without explaining what they are and stifle everyone. Compare my code in post #9 and yours in post #1. They are not even close. I am sorry, but I am no longer interested in addressing your threads or attempt to answer them.
Click to expand...
Sorry to have upset you, but can't agree with you.
Never mind.

RBS
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
RB Smissaert said:
Sorry to have upset you, but can't agree with you.
Never mind.

RBS
Click to expand...
Let me just explain those unexplained variables.
As mentioned in a previous thread I am making some changes to the B4XTable class, mainly to get more search options.
I will upload a little demo project when I have this all fully worked out.

There are 2 variables added to Sub Class_Globals of the the B4XTable class:

B4X: 
Sub Class_Globals
    Private strCurrentWhereClause As String
    Private marrSearchArgs() As String

Then I made changes to Public Sub BuildQuery and Private Sub ImplUpdateDataFromQuery:

B4X: 
Public Sub BuildQuery(WithPageLimit As Boolean) As Object()
    
    Dim c As Int
    Dim n As Int
    Dim SortColumn As String
    Dim strFind As String
    Dim sb As StringBuilder
    Dim lstCurrentArgs As List
    Dim lstCurrentArgsIsText As List
    Dim TotalCount As Int

    sb.Initialize
    sb.Append("SELECT rowid")
    
    For Each col As B4XTableColumn In VisibleColumns
        If col.ColumnType = COLUMN_TYPE_VOID Then Continue
        sb.Append(",").Append(col.SQLID)
        If col.InternalSortMode <> "" Then
            SortColumn = " ORDER BY " & col.SQLID & " " & col.InternalSortMode
        End If
    Next
    
    sb.Append(" FROM ").Append(SQLTableName).Append(" ")

    lstCurrentArgs.Initialize
    lstCurrentArgsIsText.Initialize
    
    Log("B4XTable, BuildQuery, miSearchConditions: " & miSearchConditions)
    
    If miSearchConditions = 0 Then
        TotalCount = CountAll
        mCurrentCount = CountAll
        'Log("B4XTable, BuildQuery, mCurrentCount: " & mCurrentCount)
        miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
    Else
        For Each col As B4XTableColumn In VisibleColumns
            If col.Searchable Then
                For n = 0 To arrCheckConditions(c) - 1
                    lstCurrentArgsIsText.Add(arrConditions(c, n).bIsTextArgument)
                    strFind = arrConditions(c, n).strSearchText
                    'Log("BuildQuery, strFind: " & strFind)
                    If lstCurrentArgs.Size = 0 Then
                        sb.Append(" WHERE ")
                    Else
                        If arrConditions(c, n).bSQL_OR Then
                            'this used to be all OR
                            sb.Append(" OR ")
                        Else
                            sb.Append(" AND ")
                        End If
                    End If
                    If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE Then
                        If arrConditions(c, n).strOperator.Length = 0 Then
                            sb.Append(col.SQLID).Append(" = ? ") 'this may not always work with double values
                            lstCurrentArgs.Add(strFind)
                        Else 'If arrConditions(c, n).strOperator.Length = 0
                            If arrConditions(c, n).strOperator.StartsWith("between") Then
                                Dim arrFind() As String = Regex.Split(",", strFind)
                                If col.ColumnType = COLUMN_TYPE_DOUBLE Then
                                    Dim arrFindDouble(2) As Double
                                    arrFindDouble(0) = arrFind(0)
                                    arrFindDouble(1) = arrFind(1)
                                    sb.Append(col.SQLID).Append(" between cast(? as real) and cast(? as real)")
                                    lstCurrentArgs.Add(arrFindDouble(0))
                                    lstCurrentArgs.Add(arrFindDouble(1))
                                Else
                                    Dim arrFindInt(2) As Int
                                    arrFindInt(0) = arrFind(0)
                                    arrFindInt(1) = arrFind(1)
                                    sb.Append(col.SQLID).Append(" between cast(? as integer) and cast(? as integer)")
                                    lstCurrentArgs.Add(arrFindInt(0))
                                    lstCurrentArgs.Add(arrFindInt(1))
                                End If
                            Else
                                If arrConditions(c, n).strOperator.StartsWith("length") Then
                                    Select Case arrConditions(c, n).strOperator
                                        Case "length ="
                                            sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) = cast(? as integer) ")
                                        Case "length >"
                                            sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) > cast(? as integer) ")
                                        Case "length <"
                                            sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) < cast(? as integer) ")
                                        Case "length <>"
                                            sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) <> cast(? as integer) ")
                                    End Select
                                Else
                                    sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
                                End If
                                lstCurrentArgs.Add(strFind)
                            End If
                        End If 'If arrConditions(c, n).strOperator.Length = 0
                    Else 'If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE
                        If arrConditions(c, n).strOperator.StartsWith("between") Then
                            Dim arrFind() As String = Regex.Split(",", strFind)
                            sb.Append(col.SQLID).Append(" between ? and ?")
                            lstCurrentArgs.Add(arrFind(0))
                            lstCurrentArgs.Add(arrFind(1))
                        Else
                            If arrConditions(c, n).strOperator.StartsWith("length") Then
                                Select Case arrConditions(c, n).strOperator
                                    Case "length ="
                                        sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) = cast(? as integer) ")
                                    Case "length >"
                                        sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) > cast(? as integer) ")
                                    Case "length <"
                                        sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) < cast(? as integer) ")
                                    Case "length <>"
                                        sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) <> cast(? as integer) ")
                                End Select
                                lstCurrentArgs.Add(strFind)
                            Else
                                If arrConditions(c,n).bWildCard Then
                                    If arrConditions(c,n).bCaseSensitive Then
                                        sb.Append(col.SQLID).Append(" GlOB ? ")
                                        If arrConditions(c,n).bPrefixSearch Then
                                            lstCurrentArgs.Add(strFind & "*")
                                        Else
                                            lstCurrentArgs.Add("*" & strFind & "*")
                                        End If
                                    Else
                                        sb.Append(col.SQLID).Append(" LIKE ? ")
                                        If arrConditions(c,n).bPrefixSearch Then
                                            lstCurrentArgs.Add(strFind & "%")
                                        Else
                                            lstCurrentArgs.Add("%" & strFind & "%")
                                        End If
                                    End If
                                Else
                                    If arrConditions(c, n).bCaseSensitive Then
                                        If arrConditions(c, n).strOperator.Length = 0 Then
                                            sb.Append(col.SQLID).Append(" = ? ")
                                        Else
                                            sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
                                        End If
                                        lstCurrentArgs.Add(strFind)
                                    Else
                                        If arrConditions(c, n).strOperator.Length = 0 Then
                                            sb.Append("lower(").Append(col.SQLID).Append(") = ? ")
                                        Else
                                            sb.Append("lower(").Append(col.SQLID).Append(") ").Append(arrConditions(c, n).strOperator).Append(" ? ")
                                        End If
                                        lstCurrentArgs.Add(strFind.ToLowerCase)
                                    End If
                                End If
                            End If
                        End If
                    End If 'If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE
                Next
            End If
            c = c + 1
        Next
        TotalCount = -1 'so TotalCount will be -1 if we are doing a search
    End If
    
    Log("B4XTable, BuildQuery, SortColumn.Length: " & SortColumn.Length)
    
    If SortColumn <> "" Then
        sb.Append(SortColumn)
    End If
    
    If sb.ToString.ToLowerCase.Contains(" where ") Then
        strCurrentWhereClause = GetWhereClauseFromQuery(sb.ToString)
        Log("BuildQuery, strCurrentWhereClause: |" & strCurrentWhereClause & "|")
    End If
    
    If WithPageLimit Then
        Dim limit As Int = mRowsPerPage
        If TotalCount < 0 Then limit = limit + 1 'this will be the case if we are doing a search
        sb.Append($" LIMIT ${mFirstRowIndex}, ${limit}"$) 'LIMIT offset, row_count
    End If
    
    Log("B4XTable, BuildQuery, sb.ToString: " & sb.ToString)
    
    #if B4A
    Dim aargs(lstCurrentArgs.Size) As String
    For i = 0 To lstCurrentArgs.Size - 1
        aargs(i) = lstCurrentArgs.Get(i)
    Next
    #else
    Dim aargs As List = args
    #End If
    
    Return Array(sb.ToString, aargs, TotalCount)
    
End Sub

Private Sub ImplUpdateDataFromQuery (Query As String, Args As Object, TotalCount As Int, DontSleep As Boolean)

    Dim i As Int
    Dim iCol As Int
    Dim arrArgs() As String = Args
    
    SQLIndex = SQLIndex + 1
    
    If mRowsPerPage = 0 Then Return
    
    Dim MyIndex As Int = SQLIndex
    If DontSleep = False Then
        Sleep(40)
    End If
    
    If MyIndex <> SQLIndex Then
        Return
    End If
    
    VisibleRowIds.Clear
    
    Dim rs As ResultSet = sql1.ExecQuery2(Query, arrArgs)
    'Log("B4XTable, ImplUpdateDataFromQuery, rs.RowCount: " & rs.RowCount)

    Dim success As Boolean = True

    If success Then
        Dim RowNumber As Int = 1
        Do While RowNumber <= mRowsPerPage And rs.NextRow
            VisibleRowIds.Add(rs.GetLong("rowid"))
            iCol = 0
            For Each c As B4XTableColumn In VisibleColumns
                Dim lbl As B4XView = GetLabelFromColumn(c, RowNumber)
                Select c.ColumnType
                    Case COLUMN_TYPE_TEXT
                        SetTextToCell(rs.GetString(c.SQLID), lbl, c.Searchable, iCol)
                    Case COLUMN_TYPE_INTEGER
                        'c.Formatter.FormatLabel(rs.GetInt(c.SQLID), lbl)
                        lbl.Text = rs.GetInt(c.SQLID)
                    Case COLUMN_TYPE_DOUBLE
                        c.Formatter.FormatLabel(rs.GetDouble(c.SQLID), lbl)
                    Case COLUMN_TYPE_DATE
                        lbl.Text = DateTime.Date(rs.GetLong(c.SQLID))
                End Select
                iCol = iCol + 1
            Next
            RowNumber = RowNumber + 1
        Loop
        
        Dim bAreThereMoreResults As Boolean = RowNumber = mRowsPerPage + 1 And rs.NextRow
        
        rs.Close
        
        For i = RowNumber To mRowsPerPage
            iCol = 0
            For Each c As B4XTableColumn In VisibleColumns
                #if B4J
                SetTextToCell("", GetLabelFromColumn(c, i), c.Searchable, iCol)
                #else
                GetLabelFromColumn(c, i).Text = ""
                #End If
                iCol = iCol + 1
            Next
            VisibleRowIds.Add(0)
        Next
        
        mLastRowIndex = mFirstRowIndex - 2 + RowNumber
            
        If TotalCount > -1 Then
            'as the previous and next page views are not done in the custom view anymore we are just dealing with Boolean variables here
            '---------------------------------------------------------------------------------------------------------------------------
            bCanDoNextPage = mLastRowIndex < TotalCount - 1
            mCurrentCount = TotalCount 'this used to be just past this If block, but that meant mCurrentCount could be -1 (seems to indicate that current row count is unknown)
        Else
            'TotalCount -1 means we ran a search and mCurrentCount will be set later by SetCurrentCountAfterSearch (run from the DataUpdated Event)
            '--------------------------------------------------------------------------------------------------------------------------------------
            bCanDoNextPage = bAreThereMoreResults
            'this is to get the total number of rows obtained in the search (user initiated search) and pass this to the B4XPage
            '-------------------------------------------------------------------------------------------------------------------
            marrSearchArgs = arrArgs
        End If
        
        bCanDoPreviousPage = mFirstRowIndex > 0
        
        'This used to be here, see above comment!
        '----------------------------------------
        'mCurrentCount = TotalCount
        
        Log("B4XTable, ImplUpdateDataFromQuery, mCurrentCount: " & mCurrentCount)
        miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
        mCurrentPage = Ceil(mFirstRowIndex / mRowsPerPage) + 1
        
        rs.Close
    Else
        Log(LastException)
    End If
    
    If xui.SubExists(mCallBack, mEventName & "_DataUpdated", 0) Then
        CallSub(mCallBack, mEventName & "_DataUpdated")
    End If
    
End Sub

I also added a little Sub to get the current Where clause of a user search:

B4X: 
Private Sub GetWhereClauseFromQuery(strQuery As String) As String
    
    Dim iStartPos As Int = strQuery.ToLowerCase.IndexOf(" where ")
    Dim iEndPos As Int = strQuery.ToLowerCase.IndexOf(" limit ")
    
    If iEndPos = -1 Then
        iEndPos = strQuery.ToLowerCase.IndexOf(" order by ")
    End If
    
    If iEndPos = -1 Then
        iEndPos = strQuery.Length
    End If
    
    Return SubStr2(strQuery, iStartPos + 1, iEndPos - 0)
    
End Sub

The above code does contain other unexplained variables, eg bCanDoNextPage, bCanDoPreviousPage, arrConditions and arrCheckConditions,
but these are not to do with counting the table rows.
When I upload a the mentioned demo project all those will be fully explained and all will hopefully be clear.
I have it all working perfectly fine but that is in a very large project and I need to boil it down to a much smaller demo project.

RBS
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Erel
  • Article
B4A Library [B4X] B4XTableSelections - extended selection modes for B4XTable
2
Replies
21
Views
18K
aeric
aeric
Erel
  • Article
Android Code Snippet [B4X] B4XTable - Delete Row
Replies
2
Views
9K
Erel
Erel
Erel
  • Article
Android Tutorial [B4X] B4XTable - Load data from SQL database
Replies
4
Views
25K
Erel
Erel
Erel
  • Article
Android Tutorial [B4X] B4XTable - Multiple rows selection
Replies
0
Views
11K
Erel
Erel
Erel
  • Locked
  • Article
Android Tutorial [B4X] B4XTable with custom cells layout
Replies
4
Views
25K
William Lancee
William Lancee
Top