Flask-Sqlalchemy pagination - how to paginate through a table when there are multiple tables on same-CodePudding

I would like to have multiple tables on the same page and to be able to paginate through these tables individually. Currently if I click Next to go to the next page of results for a table then all of the tables on the page go to the next page. This is an issue if the tables have a different number of pages as the one with the lower number of pages will cause a 404 error. Edit: I just realised if I set error_out=False in the paginate arguments it does not 404 on me but just provides and empty table for the shorter table.

I am using flask-sqlalchemy to query a table of support tickets and filter by support_team that the support agent is a member of. A support agent can be in more than one team.

Here is my route:

@interaction_bp.route("/my_teams_tix/")
def my_teams_tix():
    user = User.query.filter_by(full_name=str(current_user)).first()  # get the current user
    teams = user.teams # a list of all the teams the current user is in

    page = request.args.get('page', 1, type=int)
    tickets = {}

    for team in teams:
        team_ticket = (
            Ticket.query.filter(Ticket.support_team_id == team)
            .filter(Ticket.status != "Closed")
            .order_by(Ticket.ticket_number.asc())
            .paginate(page=page, per_page=current_app.config["ROWS_PER_PAGE"]) # this is 5
        )
        tickets[team.name] = team_ticket

    return render_template('interaction/my_teams_work.html', tickets=tickets

So this will list all of the tickets that are assigned to a team that the current user is in.

I'm using a jinja2 template like so (stripping all the css coding and most fields out to simplify this example):

{% for team, pagination in tickets.items() %}
<h2>{{ team }}</h2>
<table id="{{team}}-table">
    <thead>
        <tr>
            <th>Ticket ID</th>
            <th>Title</th>
            <th>Owner</th>
        </tr>
    </thead>
    <tbody>
        {% for ticket in pagination.items %}
        <tr>
            <td>{{ ticket.ticket_number }}</td>
            <td>{{ ticket.type }}</td>
            <td>{{ ticket.supporter.name }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

{% if pagination.has_prev %}
<a href="{{ url_for('interaction_bp.my_teams_tix', team_name=team, page=pagination.prev_num) }}">Previous</a>
{% endif %}

Page {{ pagination.page }} of {{ pagination.pages }}

{% if pagination.has_next %}
<a href="{{ url_for('interaction_bp.my_teams_tix', team_name=team, page=pagination.next_num) }}">Next</a>
{% endif %}

{% endfor %}

This all works fine. In this example the user is a member of two teams, so I end up with two tables of tickets like so:

ITSM
Ticket ID   Title   Owner
1006    Incident    
1012    Request 
1013    Incident    
1015    Request 
1016    Request 
Page 1 of 3 Next


Database Admin
Ticket ID   Title   Owner
1001    Incident    
1007    Request 
1008    Incident    
1010    Incident    
1014    Request 
Page 1 of 2 Next

the first "Next" link above is this

http://localhost:5000/my_teams_tix/?team_name=ITSM&page=2

and the other one is:

http://localhost:5000/my_teams_tix/?team_name=Database Admin&page=2

When I click on Next on any of the tables it moves both tables to the next page. As one has 3 pages and the other 2 pages, when I click next for the one with 3 pages I then get an 404 error.

I'd like to have all these tables on the same page (and there might be 4 or 5 of them depending on the number of teams the support agent belongs to) and paginate through them independently.

my gut tells me I'll need to do it using javascript but I'm not sure how to go about it. Any help appreciated.

CodePudding user response：

I would probably try to look at managing the state of each pagination table independently via url encoding similar to what you are currently doing.

Every time you run this the url_for wipes away all previous url encoding. If you want to avoid javascript you will need to refactor this to use the current URL since you will have multiple tables with separate pages.

<a href="{{ url_for('interaction_bp.my_teams_tix', team_name=team, page=pagination.prev_num) }}">Previous</a>

It will wipe out the state of the other table, to avoid javascript you will need to find a way to maintain the current URL so you can nest pagination state of each distinct table. A similar question was asked here: Flask- multiple paginations on same page

That being said, if your tables aren't that big I might just look at dumping the whole DB response and let a JS plugin like DataTables handle this for you: https://datatables.net/

CodePudding user response：

After much trial and error I figured out how to do this. It might not be the most eloquent way to do it, so any suggestions for improvement is appreciated as I'm still learning.

My original problem was that I had support tickets. These support tickets have a support agent. The support agent is a member of one or more teams, this can be any number, they might be in 2 or 10 different teams.

I wanted to show a single page with a separate table for all the tickets for each team the ticket was assigned to. The current user may or may not be the support agent, but they will be able to see all tickets that are assigned to teams they are a member of.

I won't show the models as you can probably figure that out. So with that understanding here is my route:

@interaction_bp.get("/_my_teams_work/")
def my_teams_work():
    """
    calls my_teams_dashboard(model) where model is Ticket defined in common_utils.py to get list
    of all open tickets owned by the current user
    :return: renders template with table of all tickets owned by logged-in user
    """
    tickets = {}
    form = TicketForm()
    page = request.args.get("page", 1, type=int)  # for pagination of table
    page_team = request.args.get("team","all")

    highest_number = get_highest_ticket_number(Ticket) - 1
    teams = my_teams()

    for team in teams:
        team_ticket = my_teams_dashboard(Ticket, 1, team)
        tickets[team.name] = team_ticket
        
    for team in teams:
        if page_team == team.name:
            team_ticket = my_teams_dashboard(Ticket, page, team)
            # |= is the Update operator for dictionaries. This will update the value of ticket[team] with the new pagination
            # whilst keeping the existing entries as they are.
            tickets |= {team.name: team_ticket}

    return render_template(
        "/interaction/my-team.html",
        title="My Teams Work",
        subtitle=f"Open tickets for all {current_user}'s teams",
        teams=teams,
        teams_tickets=tickets,
        highest_number=highest_number,
        search_model="interaction",
        form=form,
    )

And here are the functions that provide the tickets:

def my_teams():
    user = User.query.filter_by(
        full_name=str(current_user)
    ).first()  # get the current user

    teams = user.teams
    return teams


def my_teams_dashboard(model, page, team):
    team_ticket = (
            model.query.filter(
                and_(
                    model.supporter == current_user,
                    model.status != "Closed",
                    model.status != "Resolved",
                    model.support_team == team,
                )
            )
            .order_by(model.ticket_number)
            .paginate(page=page, per_page=current_app.config["ROWS_PER_PAGE"], error_out=False)
    )
    return team_ticket

I hope this helps someone who has this same issue.