read24 - Admin UI: Listing Books

August 04, 2020

Roger Ngo

With a new navigation bar created, it is now motivating to make new pages to be navigated. A navigable page that I can now get started in creating is the ability to list books.

This page is useful because it will be a way to allow myself to drill down to a book, and either edit, or delete the book.

Here are the requirements for a potential ListBooks page:

  1. Be able to list all the books that are active in the database (where dateDeleted = 0.
  2. Provide a searchable filter for a book by title, or author using the /book/search/title/:bookTitle, and /book/search/author/:authorName endpoints.
  3. Paginate the results so that only a subset appear at once, and the user can navigate through more books should they wish.

The list of books can be rendered through a table. A table can look like this:

Title Author Year Total Questions Actions
Clifford the Big Red Dog Norman Bridwell 1985 4 [Edit]‌

The last column should provide an Edit button so that the book can be drilled down to the edit form — similar to adding a book.‌

The results should also be displayed in a paginated fashion. Right now, there is actually no server API route to handle retrieval of all books in a paginated manner. So, as a first task, I will build that out.‌

In order to ensure code is working, and that things will eventually be paginated, the default number of results per page should be 2. It is enough to allow myself to be convinced that pagination is working.

API Route

The additional API route should be a GET request. The route name can be /book/search/all/:page to get a list of all books at a particular offset. The response of this endpoint should detail the information about the books, and the information needed to make the next query to get the next page of books.‌

For example, if a request was /book/search/all/2, I would get a list of books at the offset with what would be considered the “second page”, and information whether or not, one would be able to retrieve more results through the hasMore flag. If so, then one can linearly traverse through the next page by providing the value in nextPage in the next GET request.

{
    books: [...],
    _meta: {
        hasMore: true,
        pages: 5,
        nextPage: 3
    }
}

Care will be taken whenever the page parameter in the URL is not a proper value such as 0. In that case, a HTTP 400 should be returned. For the page value that is greater than the number of available pages, it is implicit then that this value will correspond to the last page.

Filtered Searches‌

As mentioned before, for filtered searches through searching by title, and author, the /book/search/title and /book/search/author endpoints may be reused. I can do some stuff to cache searches as the database of books isn’t really expected to change that much. However, I will cross that bridge when I get there.

Implementation

Finding paginated results for let’s say 2 entries per page can be modeled by this type of SQL query:

SELECT * FROM books
  WHERE dateDeleted = 0
  ORDER BY title
  LIMIT 0, 2

The above query will get the first 2 results. To retrieve the next two results, the query will then be:

SELECT * FROM books
  WHERE dateDeleted = 0
  ORDER BY title
  LIMIT 2, 2

From observation, it is clear that the current database connector does not natively support queries like this. That is okay, because the database connector’s select function can be updated to do this using the passed in options: SelectOptions parameter.

export interface OrderByOptions {
    column: string;
    ascending: boolean;
}

export interface SelectOptions {
    includeDeleted?: boolean;
    orderBy?: OrderByOptions;
    offset?: number;
    limit?: number;
    columns?: string[]
}

SelectOptions will now have additional properties aside from includeDeleted. These will give greater flexibility in building out SELECT queries – and is specifically targeted towards creating SELECT queries with LIMIT clauses. ‌

The orderBy property is an object that will allow the database connector to build out a query that will order results by a column, and whether the results should be ordered by ascending, or descending order.

LIMIT clauses are of two parameters: the offset, and the number of results to return. The offset property is the first parameter, declaring the query to start a specific place in the table, and the limit property will only return that number of results.

Finally the database connector should support more than always querying for all columns through *. Instead, columns can also be selected on-demand. One specific use for this is to create SELECT count(*) ... queries. Then columns will be ['count(*}'].

Now, let’s modify the database connector’s select function to accommodate to these changes.

public select(tableName: string, whereFunc?: (o: DataRow) => boolean, opts?: SelectOptions): Promise<DataRow[]> {
    const promise = new Promise<DataRow[]>((resolve, reject) => {
        let queryString = '';

        if (opts.columns && opts.columns.length > 0)
            queryString = `SELECT ${opts.columns.join(',')} FROM ${TableMapping[tableName]} `;
        else
            queryString = `SELECT * FROM ${TableMapping[tableName]} `;

        // By default, do not include rows where deletedAt is set. This is a virtual deletion.
        if (!opts || !opts.includeDeleted)
            queryString += ' WHERE dateDeleted = 0 ';

        if (opts && opts.orderBy)
            queryString += ` ORDER BY ${opts.orderBy.column} ${opts.orderBy.ascending ? 'ASC': 'DESC'}`;
        
        if (opts && opts.limit && isValue(opts.offset))
            queryString += ` LIMIT ${opts.offset}, ${opts.limit}`;

        const query = connection.query(queryString, (err, results) => {
            if (err)
                return reject(err);

            console.log(query.sql);
            console.log('SELECT results', results);

            const resultRows = results.map(r => Object.assign({}, r));

            return resolve(resultRows.filter(whereFunc) as DataRow[]);
        });
    });

    return promise;
}

Since opts is an optional parameter, there must be care taken to check for whether or not certain properties exist. I am basically building up a SELECT statement dynamically based on the options being provided.

Modifying the memoryconnector is a bit trickier, and will be left as a separate “TODO” item. My plan is to actually deprecate this memory connector, and create a new one that behaves much more similar to the sql database connector.‌

Now, I will need to extend the books model to include additional logic that will be needed to list all the books. The functions numberOfPages, and lastBookTitle serve as utility functions to help with pagination and detecting whether or not more results can be obtained. The listAllBooks function calls select with specific options that will result in a SELECT * FROM .. LIMIT query.

public static async numberOfPages() {
    const results = await DatabaseConnector.select('books', () => true, {
        columns: ['count(id)']
    });

    console.log(results);

    return results[0]['count(id)'];
}
public static async lastBookTitle() {
    const results = await DatabaseConnector.select('books', () => true, {
        limit: 1,
        orderBy: {
            ascending: false,
            column: 'title'
        }
    });

    return results[0].title;
}

public static async listAllBooks(offset: number, limit: number) {
    const books = await DatabaseConnector.select('books', () => true, {
        limit,
        offset,
        orderBy: {
            ascending: true,
            column: 'title'
        }
    });

    return await Promise.all(books.map(async b => await new Book().load(b.id)));
}

Now, let’s see this in action by implementing the endpoint in the books route itself.

app.get('/book/search/all/:page', async (req, res) => {
    const defaultLimit = 2;
    const page = parseInt(req.params.page || '1');

    if (page === 0)
        return res.status(400).json({message: 'Invalid page number'});

    const offset = (page - 1) * 2;
    
    const pages = Math.ceil((await Book.numberOfPages()) / defaultLimit);
    const results = await Book.listAllBooks(offset, defaultLimit);

    if (results.length === 0)
        return res.status(200).json({
            books: [],
            _meta: {
                hasMore: false,
                pages: 0
            }
        });

    const lastBookTitle = await Book.lastBookTitle();
    const hasMore = !!!results.find(r => r.title === lastBookTitle);
    
    return res.status(200).json({
        books: results.map(r => r.json()),
        _meta: {
            hasMore,
            pages,
            nextPage: hasMore ? page + 1: undefined
        }
    })
});

Okay, there is quite a bit going on here. First the page parameter is converted to an integer, and is checked to see if it is a valid page. If not, then an HTTP 400 is returned. Otherwise, the proper offset is calculated using this formula:

offset = (page - 1) * defaultLimit;

So if page = 2, then the offset will be starting at result 2, and will retrieve defaultLimit results.

Once the offset is calculated, the number of pages available must be calculated. This will use the numberOfPages utility method from the model, and the ceiling of this value of the defaultLimit will give the number of pages.‌

The result set is retrieved by simply calling listAllBooks. If there are no results returned, then prefer an early returned by sending a valid, but empty result set with hasMore being false telling the client to stop retrieving further results by calling the endpoint with the specific page parameter.

Otherwise, the result set is processed, and the JSON response is built and send back to the client.

{
    "books": [
        {
            "id": 3,
            "title": "Clifford the Big Red Dog",
            "fiction": 1,
            "author": "Norman Bridwell",
            "year": "1985",
            "publisher": "Scholastic, Inc",
            "genre": 1,
            "isbn": "0-590-44297-X",
            "lexile": 370,
            "wordCount": 236
        },
        {
            "id": 1,
            "title": "Where the Red Fern Grows",
            "fiction": 1,
            "author": "Wilson Rawls",
            "year": "1974",
            "publisher": "Bantam Books",
            "genre": 0,
            "isbn": "978-0-553-12338-8",
            "lexile": 700,
            "wordCount": 75528
        }
    ],
    "_meta": {
        "hasMore": false,
        "pages": 2
    }
}

Okay, now how about getting the number of questions in the book? Here I will need to obtain more data, and perform another count query. Let’s modify the route to get those results, and further shape the API response.

First connector.ts will receive another modification to SelectOptions. This time it is a new property called filters which is an array of FilterOption type. This basically serves as a way to create queries that filter results using some column and value. It is more simple than a whereFunc and really just constructs a resulting SELECT statement with a WHERE clause that looks like this:

SELECT ... WHERE 1=1 AND filterCol1=filterVal1 AND filterCol2=filterVal2 ...
export interface FilterOption {
    column: string;
    value: string;
}

export interface SelectOptions {
    includeDeleted?: boolean;
    orderBy?: OrderByOptions;
    offset?: number;
    limit?: number;
    columns?: string[];
    filters?: FilterOption[];
}

The SQL connector can now use some refactoring to take into account of the FilterOption being passed. Here is the updated code implementation:

public select(tableName: string, whereFunc?: (o: DataRow) => boolean, opts?: SelectOptions): Promise<DataRow[]> {
    const promise = new Promise<DataRow[]>((resolve, reject) => {
        let queryString = '';

        if (opts.columns && opts.columns.length > 0)
            queryString = `SELECT ${opts.columns.join(',')} FROM ${TableMapping[tableName]} `;
        else
            queryString = `SELECT * FROM ${TableMapping[tableName]} `;

        queryString += ' WHERE 1=1 '
        // By default, do not include rows where deletedAt is set. This is a virtual deletion.
        if (!opts || !opts.includeDeleted)
            queryString += ' AND dateDeleted = 0 ';

        if (opts && opts.filters && opts.filters.length > 0) {
            const filters = opts.filters.map(f => `${f.column} = ${f.value}`).join(' AND ');
            queryString += ` AND ${filters}`
        }

        if (opts && opts.orderBy)
            queryString += ` ORDER BY ${opts.orderBy.column} ${opts.orderBy.ascending ? 'ASC': 'DESC'}`;
        
        if (opts && opts.limit && isValue(opts.offset))
            queryString += ` LIMIT ${opts.offset}, ${opts.limit}`;

        const query = connection.query(queryString, (err, results) => {
            if (err)
                return reject(err);

            console.log(query.sql);
            console.log('SELECT results', results);

            const resultRows = results.map(r => Object.assign({}, r));

            return resolve(resultRows.filter(whereFunc) as DataRow[]);
        });
    });

    return promise;
}

Since each book will need to have the total number of questions associated with it, the Question resource model should provide a helper method to get the count of questions for a particular book ID.

public static async totalQuestionsForBookId(bookId: number) {
    const data = await DatabaseConnector.select('questions', () => true, {
        columns: ['count(id)'],
        filters: [{
            column: 'bookId',
            value: String(bookId)
        }]
    });

    return data[0]['count(id)'];
}

Now I can begin to modify the route to include the total number of questions. For now, I can simply obtain the total count as I iterate through the number of books. I will worry about writing a filter for IN clause later to do this in one shot.

The main loop looks like this:

const books = [];
for(let r of results) {
    const totalQuestions = await Question.totalQuestionsForBookId(r.id);
    const bookSerialized = r.json();
    
    books.push({...bookSerialized, totalQuestions});
}

Then the response returned is just:

return res.status(200).json({
    books,
    _meta: {
        hasMore,
        pages,
        nextPage: hasMore ? page + 1: undefined
    }
})

I’m running out of time here now, but the backend changes are mostly done now. UI related things will be for the next session, but I will also have to take care in cleaning up a lot of things in the backend and make it more robust.

I’m not sure if I should do it all in one shot, but for sure I am in the mood for some front-end work in my next sit down. For now, I’ll make myself feel better by hooking up the page to list books in Navigation.tsx.

<Link to="/book/list" className="nav-item nav-link">List Books</Link>

And then the ListBooks component itself:

import React from 'react';

export default function ListBooks() {
    return (
        <div>Test</div>
    );
}

Finally, the index page needs updating:

<Route path="/book/list">
  <ListBooks />
</Route>