B4J Question [ABMaterial] how to make table's pagination correctly?

[peacemaker]

HI, All

How to use ABMPagination correctly?

I have:

Private tableNames As ABMTable, pagNames As ABMPagination
Public const TABLE_STEP As Int = 5    'qty of the pagination page

Sub Recalc_Pagination
    Dim Total As Long = Refresh_tableNAMES(0)
    pagNames.Initialize(page, "pagNames", TABLE_STEP, True, True, "")
    pagNames.SetTotalNumberOfPages(Ceil(Total/TABLE_STEP))
End Sub

public Sub ConnectPage()
    Recalc_Pagination
    '....
End sub

Sub Refresh_tableNAMES(first As Int) As Long
    Dim SQLtable As String = "names"
    Dim WEBtable As ABMTable = tableNames
    WEBtable.SetHeaders(Array As String("id", "name", "source_name", "status", "rounds", "stamp", "comment", "actions"))
    Dim all As Long = DBUtils.GetLongFromSQL(db.sql, SQLtable, "SELECT count(*) FROM " & SQLtable)
    'Dim names As List = DBUtils.ExecuteMemoryTable(db.sql, "SELECT * FROM (SELECT id, name, source_name, status, rounds, stamp, comment, '✎' FROM " & SQLtable & " WHERE id >= '" & first & "' LIMIT " & TABLE_STEP & ") AS bb ORDER BY bb.id DESC", Null, TABLE_STEP)  - WRONG PAGINATION WORK :()
    Dim names As List = DBUtils.ExecuteMemoryTable(db.sql, "SELECT id, name, source_name, status, rounds, stamp, comment, '✎' FROM " & SQLtable & " WHERE id >= '" & first & "'", Null, TABLE_STEP)
    If names.Size > 0 Then
        WEBtable.Clear
        For i = 0 To names.Size - 1
            Dim L() As String = names.Get(i)
            Dim S As List = L.As(List)
            s = ReplaceNullinList(S)
            WEBtable.AddRow(i, s)
        Next

    End If
    WEBtable.SetActiveRow(-1)
    WEBtable.Refresh
    Return all
End Sub

Sub ReplaceNullinList(L As List) As List
    For i = 0 To L.Size - 1
        Dim s As String = L.Get(i)
        If s.EqualsIgnoreCase("null") Then
            L.Set(i, "")
        End If
    Next
    Return L
End Sub

Sub pagNames_PageChanged(OldPage As Int, NewPage As Int)
    Log("pagNames = " & OldPage & " - " & NewPage)
    pagNames.SetActivePage(NewPage)
    Refresh_tableNAMES((NewPage - 1) * TABLE_STEP)
    pagNames.Refresh
End Sub

So, pagination here is based on id order in the table.
But if i need to change the ORDER BY id DESC - the pagination stopped to work.

Is there any universal way of correct pagination work at any SQL table requests ?
And noting that the table records qty may be changed any time.

 

[MichalK73]

I have never had any problems with Pagination. I see I think you have the wrong SQL query structure in line 17.
Let me give you an example of how it works for me in one of my projects:

Public Sub Initialize(page0 As ABMPage,id As String, theme As ABMTheme, pageID As String, ws0 As WebSocket)
...
...
        padcount=1
        Dim l As List = DBM.SQLSelect("select * from user", Null)
        Dim padall As Int = l.Size-1
        Dim a As Int=padall/10
        If a*10<padall Then a =a+1
        padmax=a

...
End Sub

Sub User As ABMContainer
...
...

    Dim pad As ABMPagination
    pad.EventHandler = Me
    pad.Initialize(page, "pad", 10, True,True,"")
    pad.SetTotalNumberOfPages(padmax)
    pad.SetActivePage(padcount)
    View0.Cell(2,1).AddComponent(pad)
    Dim duze As Int
    If padcount =1 Then
        duze=0
    Else
        duze = (padcount-1)*10
    End If
    
    Dim lista As List = DBM.SQLSelect( "select * from user limit ?, 10", Array As String(duze))
    Dim wiersz As Int = 3
    For Each m As Map In lista
    ...
    ...
    Next


End Sub

Sub pad_PageChanged(OldPage As Int, NewPage As Int)
    If OldPage<NewPage And NewPage<=padmax Then
        padcount=NewPage
    End If
    
    If OldPage > NewPage And NewPage>2 Then
        padcount = NewPage
    End If
    If OldPage = 0 And NewPage =1 Then
        padcount = 1
    End If
    
    padcount=NewPage
    View.Row(1).RemoveAllComponents
    View.Cell(1,1).AddComponent(User)
    View.Refresh
    
End Sub

 

[peacemaker]

example of how it works for me

And if to sort your users by any order ? Say ORDER BY id DESC, or any other field.
Try, pls, does it work OK ?

 

[MichalK73]

It certainly works, it all depends on whether you build the SQL query correctly.
Pull up the query built in the project and try to run it in the database client to see if it works. There is no reason or limitation that your ORDER BY does not work.
The only difference is that I use a module attached to ABM called DBM and not DBUtils like you to handle the database, but I don't think that has any bearing on this,

 

[peacemaker]

Do you mean something like:

Dim names As List = DBUtils.ExecuteMemoryTable(db.sql, "SELECT id, name, source_name, status, rounds, stamp, comment, '✎' FROM " & SQLtable & " ORDER BY id DESC LIMIT " & first & "," & TABLE_STEP, Null, 0)

Seems it works now, but ...

Spoiler

But i have found that
pagNames_PageChanged(OldPage As Int, NewPage As Int)
always returns OldPage = 0

 

[MichalK73]

What is this ??
" '✎' " in your SQL??

And did you set the pagination position active?

 

[peacemaker]

'✎' is just extra column "actions" - delete\update menu for each row.
Position is OK, ABMPAgination is added into the table footer.

This topic is really solved - you were right, SQL request must be just with "LIMIT x, y" for pagination.
Next trouble with pagination is moved into a new topic.