CODEWITHSUNDEEP
pythonsqlalchemyjinja2
Roman
Roman

Reputation: 3941

Python Flask SQLalchemy count rows of certain user

I have a table rooms where user save rooms in. Each room has a foreign key "users_id" so I know which room belongs to which user.

I need to count all rooms of each user and display the number for my admin dashboard.

I have a solution but it feels not correct.

I am making two queries for all rooms and all users:

users = db_session.query(User).order_by(desc('id'))

rooms = db_session.query(Zimmer)

In my jinja2 I am the making loops to check which rooms belongs to which user. I am creating a list and append the rooms there for each user and then I use the jinja2 count method on the list variable, I also have to make the append variable display: none;:

{% for user in users %} 
    {% set total_rooms = [] %}
    {% for room in rooms %}
    {% if room.users_id == user.id %}
        <span class="make-display-none">
        {{ total_rooms.append(room) }}
        </span>
    {% endif %}
    {% endfor %}
    <p> Anzahl Objekte: {{ total_rooms|count }} </p>    
{% endfor %}

It works but I feel like there must be a better solution!

Upvotes: 0

Views: 645

Answers (1)

Max Paymar
Max Paymar

Reputation: 708

You should use a join, and then count. First join the users table with the rooms table using the key userId. Then once you have have joined the tables, do a count on all of the rows.

You can find joins in SQLAlchemy here.

Upvotes: 2

Related Questions