Skip to content

Listing

One of the most common tasks in web development is displaying data from your database. SØAD makes this simple with ActiveJDBC models and Handlebars templates. This beginner-friendly recipe shows you how to fetch and display data in various ways.

Simple Data Listing

book_list.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
from utils import render
from models import Book

class Book_list(object):
    def view(self, ctx):
        # Get all books from database
        books = Book.findAll()

        # Pass books to the template
        ctx.output["books"] = books

        # Render the view
        ctx.go_to = render.as_view(ctx, "book_list")
book_list.html
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Book List</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-4">
        <h1>My Book Collection</h1>

        {{#if books}}
            <div class="row">
                {{#each books}}
                <div class="col-md-4 mb-3">
                    <div class="card">
                        <div class="card-body">
                            <h5 class="card-title">{{title}}</h5>
                            <p class="card-text">
                                <strong>Author:</strong> {{author}}<br>
                                <strong>Genre:</strong> {{genre}}<br>
                                <strong>Pages:</strong> {{pages}}
                            </p>
                            <small class="text-muted">Published: {{year_published}}</small>
                        </div>
                    </div>
                </div>
                {{/each}}
            </div>
        {{else}}
            <div class="alert alert-info">
                <h4>No books found</h4>
                <p>Your book collection is empty. Add some books to get started!</p>
            </div>
        {{/if}}
    </div>
</body>
</html>

CREATE TABLE book (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(200) NOT NULL,
    author varchar(100) NOT NULL,
    genre varchar(50),
    pages int,
    year_published int,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO book (title, author, genre, pages, year_published) VALUES
('To Kill a Mockingbird', 'Harper Lee', 'Fiction', 281, 1960),
('1984', 'George Orwell', 'Dystopian Fiction', 328, 1949),
('Pride and Prejudice', 'Jane Austen', 'Romance', 279, 1813),
('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 180, 1925),
('Harry Potter and the Sorcerer\'s Stone', 'J.K. Rowling', 'Fantasy', 309, 1997),
('The Lord of the Rings', 'J.R.R. Tolkien', 'Fantasy', 1216, 1954),
('The Catcher in the Rye', 'J.D. Salinger', 'Coming-of-age', 234, 1951),
('Brave New World', 'Aldous Huxley', 'Science Fiction', 268, 1932);

How It Works

  1. Fetch Data: Book.findAll() gets all books from the database
  2. Pass to Template: ctx.output["books"] = books makes data available in the view
  3. Display Data: Handlebars {{#each books}} loops through each book
  4. Show Fields: {{title}}, {{author}} display individual book properties

Ordered Listing

student_list.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
from utils import render
from models import Student

class Student_list(object):
    def view(self, ctx):
        # Get students ordered by name
        students = Student.findAll().orderBy("name ASC")

        # Pass to template
        ctx.output["students"] = students

        ctx.go_to = render.as_view(ctx, "student_list")

    def by_grade(self, ctx):
        # Get students ordered by grade (highest first)
        students = Student.findAll().orderBy("grade DESC, name ASC")

        ctx.output["students"] = students
        ctx.output["sort_by"] = "grade"

        ctx.go_to = render.as_view(ctx, "student_list")
student_list.html
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student List</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-4">
        <div class="d-flex justify-content-between align-items-center mb-4">
            <h1>Class Roster</h1>
            <div>
                <a href="/t/example/student_list" class="btn btn-outline-primary btn-sm">Sort by Name</a>
                <a href="/t/example/student_list/by_grade" class="btn btn-outline-success btn-sm">Sort by Grade</a>
            </div>
        </div>

        {{#if sort_by}}
            <div class="alert alert-info">
                Students sorted by {{sort_by}}
            </div>
        {{/if}}

        {{#if students}}
            <div class="table-responsive">
                <table class="table table-striped">
                    <thead class="table-dark">
                        <tr>
                            <th>Name</th>
                            <th>Age</th>
                            <th>Grade</th>
                            <th>Subject</th>
                            <th>Email</th>
                        </tr>
                    </thead>
                    <tbody>
                        {{#each students}}
                        <tr>
                            <td><strong>{{name}}</strong></td>
                            <td>{{age}}</td>
                            <td>
                                {{#if grade}}
                                    <span class="badge bg-{{#ifgrade grade}}primary{{else}}secondary{{/ifgrade}}">{{grade}}%</span>
                                {{else}}
                                    <span class="text-muted">No grade</span>
                                {{/if}}
                            </td>
                            <td>{{subject}}</td>
                            <td>{{email}}</td>
                        </tr>
                        {{/each}}
                    </tbody>
                </table>
            </div>

            <div class="mt-3">
                <small class="text-muted">Total students: {{students.length}}</small>
            </div>
        {{else}}
            <div class="alert alert-warning">
                <h4>No students found</h4>
                <p>The class roster is empty.</p>
            </div>
        {{/if}}
    </div>
</body>
</html>

CREATE TABLE student (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(100) NOT NULL,
    age int,
    grade decimal(5,2),
    subject varchar(50),
    email varchar(150),
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO student (name, age, grade, subject, email) VALUES
('Alice Johnson', 20, 85.5, 'Computer Science', '[email protected]'),
('Bob Smith', 19, 92.0, 'Mathematics', '[email protected]'),
('Carol Davis', 21, 78.5, 'Physics', '[email protected]'),
('David Wilson', 20, 88.0, 'Chemistry', '[email protected]'),
('Emma Brown', 19, 95.5, 'Biology', '[email protected]'),
('Frank Miller', 22, 82.0, 'History', '[email protected]'),
('Grace Lee', 20, 90.5, 'English', '[email protected]'),
('Henry Taylor', 19, 87.0, 'Art', '[email protected]');

Key Features

  • Sorting: orderBy("name ASC") sorts data before displaying
  • Multiple Sort Options: Different sorting methods in different actions
  • Table Display: Clean table layout for structured data
  • Conditional Display: Show different content based on data availability

Filtered Listing

product_catalog.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
from utils import render
from models import Product

class Product_catalog(object):
    def view(self, ctx):
        # Show all products
        products = Product.findAll().orderBy("name ASC")

        ctx.output["products"] = products
        ctx.output["title"] = "All Products"

        ctx.go_to = render.as_view(ctx, "product_catalog")

    def electronics(self, ctx):
        # Show only electronics
        products = Product.where("category = ?", "Electronics").orderBy("name ASC")

        ctx.output["products"] = products
        ctx.output["title"] = "Electronics"
        ctx.output["filter"] = "Electronics"

        ctx.go_to = render.as_view(ctx, "product_catalog")

    def books(self, ctx):
        # Show only books
        products = Product.where("category = ?", "Books").orderBy("name ASC")

        ctx.output["products"] = products
        ctx.output["title"] = "Books"
        ctx.output["filter"] = "Books"

        ctx.go_to = render.as_view(ctx, "product_catalog")

    def affordable(self, ctx):
        # Show products under $50
        products = Product.where("price < ?", 50.00).orderBy("price ASC")

        ctx.output["products"] = products
        ctx.output["title"] = "Affordable Products (Under $50)"
        ctx.output["filter"] = "affordable"

        ctx.go_to = render.as_view(ctx, "product_catalog")
product_catalog.html
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{{title}} - Product Catalog</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-4">
        <h1>{{title}}</h1>

        <!-- Filter Navigation -->
        <nav class="mb-4">
            <div class="btn-group" role="group">
                <a href="/t/example/product_catalog" class="btn btn-outline-primary">All Products</a>
                <a href="/t/example/product_catalog/electronics" class="btn btn-outline-info">Electronics</a>
                <a href="/t/example/product_catalog/books" class="btn btn-outline-success">Books</a>
                <a href="/t/example/product_catalog/affordable" class="btn btn-outline-warning">Under $50</a>
            </div>
        </nav>

        {{#if filter}}
            <div class="alert alert-info">
                <i class="fas fa-filter"></i> Showing filtered results: <strong>{{filter}}</strong>
            </div>
        {{/if}}

        {{#if products}}
            <div class="row">
                {{#each products}}
                <div class="col-lg-4 col-md-6 mb-4">
                    <div class="card h-100">
                        <div class="card-body d-flex flex-column">
                            <h5 class="card-title">{{name}}</h5>
                            <p class="card-text flex-grow-1">{{description}}</p>
                            <div class="mt-auto">
                                <div class="d-flex justify-content-between align-items-center mb-2">
                                    <span class="badge bg-secondary">{{category}}</span>
                                    <h6 class="text-primary mb-0">${{price}}</h6>
                                </div>
                                <div class="d-flex justify-content-between">
                                    <small class="text-muted">Stock: {{stock}}</small>
                                    {{#if (gt stock 0)}}
                                        <small class="text-success">Available</small>
                                    {{else}}
                                        <small class="text-danger">Out of Stock</small>
                                    {{/if}}
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                {{/each}}
            </div>

            <div class="alert alert-light mt-4">
                <strong>{{products.length}}</strong> product(s) found
            </div>
        {{else}}
            <div class="alert alert-warning">
                <h4>No products found</h4>
                <p>No products match the current filter criteria.</p>
                <a href="/t/example/product_catalog" class="btn btn-primary">View All Products</a>
            </div>
        {{/if}}
    </div>
</body>
</html>

CREATE TABLE product (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(200) NOT NULL,
    description text,
    category varchar(50),
    price decimal(10,2),
    stock int DEFAULT 0,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_category (category),
    KEY idx_price (price)
);

INSERT INTO product (name, description, category, price, stock) VALUES
('Laptop Computer', 'High-performance laptop for work and gaming', 'Electronics', 899.99, 15),
('Wireless Mouse', 'Ergonomic wireless mouse with long battery life', 'Electronics', 29.99, 50),
('Programming Book', 'Learn Python programming from basics to advanced', 'Books', 39.99, 25),
('Smartphone', 'Latest model smartphone with great camera', 'Electronics', 699.99, 8),
('Cookbook', 'Delicious recipes for everyday cooking', 'Books', 24.99, 30),
('Tablet', 'Lightweight tablet perfect for reading and browsing', 'Electronics', 299.99, 12),
('Novel', 'Bestselling fiction novel', 'Books', 14.99, 40),
('Headphones', 'Noise-canceling wireless headphones', 'Electronics', 199.99, 20),
('Textbook', 'University-level mathematics textbook', 'Books', 89.99, 10),
('Phone Case', 'Protective case for smartphones', 'Electronics', 19.99, 100);

Filtering Features

  • Category Filtering: where("category = ?", "Electronics") filters by category
  • Price Filtering: where("price < ?", 50.00) filters by price range
  • Navigation: Easy switching between different filters
  • Dynamic Titles: Page title changes based on current filter

Limited Listing (Recent Items)

recent_posts.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
from utils import render
from models import Post

class Recent_posts(object):
    def view(self, ctx):
        # Get the 5 most recent posts
        recent_posts = Post.findAll().orderBy("created_at DESC").limit(5)

        # Get total count for reference
        total_posts = Post.count()

        ctx.output["posts"] = recent_posts
        ctx.output["total_posts"] = total_posts

        ctx.go_to = render.as_view(ctx, "recent_posts")

    def all(self, ctx):
        # Show all posts
        all_posts = Post.findAll().orderBy("created_at DESC")

        ctx.output["posts"] = all_posts
        ctx.output["show_all"] = True

        ctx.go_to = render.as_view(ctx, "recent_posts")
recent_posts.html
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Blog Posts</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-4">
        <div class="d-flex justify-content-between align-items-center mb-4">
            <h1>Blog Posts</h1>
            {{#if show_all}}
                <a href="/t/example/recent_posts" class="btn btn-outline-primary">Show Recent Only</a>
            {{else}}
                <a href="/t/example/recent_posts/all" class="btn btn-outline-success">Show All Posts</a>
            {{/if}}
        </div>

        {{#unless show_all}}
            {{#if total_posts}}
                <div class="alert alert-info">
                    Showing 5 most recent posts ({{total_posts}} total)
                </div>
            {{/if}}
        {{/unless}}

        {{#if posts}}
            {{#each posts}}
            <article class="card mb-4">
                <div class="card-body">
                    <h3 class="card-title">
                        <a href="#" class="text-decoration-none">{{title}}</a>
                    </h3>
                    <p class="card-text">{{excerpt}}</p>
                    <div class="d-flex justify-content-between align-items-center">
                        <div>
                            <small class="text-muted">
                                By <strong>{{author}}</strong> 
                                in <span class="badge bg-light text-dark">{{category}}</span>
                            </small>
                        </div>
                        <div>
                            <small class="text-muted">{{created_at}}</small>
                        </div>
                    </div>
                </div>
            </article>
            {{/each}}
        {{else}}
            <div class="alert alert-warning">
                <h4>No posts found</h4>
                <p>There are no blog posts to display yet.</p>
            </div>
        {{/if}}
    </div>
</body>
</html>

CREATE TABLE post (
    id int NOT NULL AUTO_INCREMENT,
    title varchar(200) NOT NULL,
    excerpt text,
    content longtext,
    author varchar(100),
    category varchar(50),
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_created (created_at)
);

INSERT INTO post (title, excerpt, content, author, category) VALUES
('Getting Started with Web Development', 'Learn the basics of building websites...', 'Complete guide to web development fundamentals...', 'John Developer', 'Tutorial'),
('Database Design Best Practices', 'Essential tips for designing efficient databases...', 'Comprehensive guide to database design patterns...', 'Jane DBA', 'Database'),
('JavaScript Tips and Tricks', 'Improve your JavaScript skills with these tips...', 'Advanced JavaScript techniques and patterns...', 'Mike Coder', 'Programming'),
('CSS Grid Layout Guide', 'Master CSS Grid for modern web layouts...', 'Complete tutorial on CSS Grid system...', 'Sarah Designer', 'CSS'),
('API Development with REST', 'Building robust REST APIs for web applications...', 'Step-by-step guide to REST API development...', 'Tom Architect', 'API'),
('Python for Beginners', 'Start your Python programming journey...', 'Introduction to Python programming language...', 'Lisa Teacher', 'Python'),
('Mobile App Development', 'Creating apps for iOS and Android...', 'Guide to cross-platform mobile development...', 'Alex Mobile', 'Mobile'),
('Security Best Practices', 'Keep your applications secure...', 'Essential security practices for web developers...', 'David Security', 'Security');

Limiting Features

  • Limited Results: limit(5) shows only the 5 most recent items
  • Count Display: Show total vs displayed count
  • View Toggle: Switch between limited and full view
  • Chronological Order: orderBy("created_at DESC") shows newest first

Key Takeaways

  • findAll() gets all records from a table
  • orderBy() sorts the results (ASC = ascending, DESC = descending)
  • where() filters records based on conditions
  • limit() restricts the number of results returned
  • ctx.output["key"] passes data from transaction to template
  • {{#each}} loops through data in Handlebars templates