read24 - Admin UI: Listing Books Part 2

August 06, 2020

Roger Ngo

Before I dive into implementing the ListBooks page, there will need to be more modifications to the backend to support querying for books by title, and author. I essentially want the /books/search/title, and /books/search/author to behave the same way as /books/search/all, except filtered by either title, or author.

I will first need to add a FULLTEXT index to the database on the title, and author columns within the books table:

alter table books ADD fulltext (title);
alter table books ADD fulltext (author);

To read more about FULLTEXT indices, you can refer to this part of the MySQL documentation:

Basically FULLTEXT index is used to be able to speed up queries on columns containing string data. It is an inverted index design. So, we can imagine a huge table of words pointing to a list of rows in which the word will appear in.‌

To perform a query using FULLTEXT, you can refer to this part of the MySQL documentation:‌

A query would then look like this using a full-text function MATCH ... AGAINST.

SELECT * FROM books WHERE MATCH(author) AGAINST ('rawls');

Once I have modified the database to include a FULLTEXT index, it is time to add a new SelectOption property to handle FULLTEXT filtering.

export interface FullTextMatchOption {
    columns: string[];
    value: string
}

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

There can be many FullTextMatchOption to match against multiple string values.

WHERE MATCH(col1, col2) AGAINST (str1) AND MATCH(col2) AGAINST (str2);

Is a good example.‌

Now I can write some additional logic in the SQL database connector’s select method to take into consideration, the fullTextMatch value set within the SelectOptions.

It is just another conditional check which will append to the query currently being built:

if (opts && opts.fullTextMatch && opts.fullTextMatch.length > 0) {
    const matchers = opts.fullTextMatch.map(f => 
        ` MATCH(${f.columns.join(', ')}) AGAINST ('+"${f.value}"' IN BOOLEAN MODE) `).join(' AND ');
    queryString += ` AND ${matchers}`;
}

Notice that in this case I would like all searches to be performing full-text searches in BOOLEAN MODE. That means I want the full-text search to be able to match the phrase completely. So for example, if the query is red fern grows, then all strings in the form of * red fern grows * will match.

The books resource model will need new methods that will provide similar functionality as with searching for all books, but for the title, and author search mode.

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

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

public static async lastBookTitleByRelevantTitle(title: string) {
    const books = await DatabaseConnector.select('books', () => true, {
        limit: 1,
        orderBy: {
            column: 'title',
            ascending: false
        },
        fullTextMatch: [
            {
                columns: ['title'],
                value: title
            }
        ]
    });

    return books[0].title;
}

public static async listAllBooksByRelevantTitles(offset: number, limit: number, title: string) {
    const books = await DatabaseConnector.select('books', () => true, {
        offset,
        limit,
        orderBy: {
            column: 'title',
            ascending: true
        },
        fullTextMatch: [
            {
                columns: ['title'],
                value: title
            }
        ]
    });

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

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

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

public static async lastBookTitleByRelevantAuthor(author: string) {
    const books = await DatabaseConnector.select('books', () => true, {
        limit: 1,
        orderBy: {
            column: 'author',
            ascending: false
        },
        fullTextMatch: [
            {
                columns: ['author'],
                value: author
            }
        ]
    });

    return books[0].title;
}

public static async listAllBooksByRelevantAuthor(offset: number, limit: number, author: string) {
    const books = await DatabaseConnector.select('books', () => true, {
        offset,
        limit,
        orderBy: {
            column: 'author',
            ascending: true
        },
        fullTextMatch: [
            {
                columns: ['author'],
                value: author
            }
        ]
    });

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

The implementations are fairly straightforward, and the notable thing I just want to show is how fullTextMatch can be set in the SelectOptions to achieve a full text search using the database connector.

Route changes will also be needed. Since now searching books without a filter, by author, and title all need to know its paging, and offset information to form the limit query, I can have a generic route to handle this.

app.all([
    '/book/search/all/page/:page',
    '/book/search/title/:bookTitle/page/:page',
    '/book/search/author/:authorName/page/:page'
], (req, res, next) => {
    if (req.params.page) {
        const page = parseInt(req.params.page || '1');

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

        const offset = (page - 1) * DEFAULT_LIMIT;
        res.locals.offset = offset;
    } else {
        res.locals.page = 1;
        res.locals.offset = 0;
    }

    return next();
});

Here, I apply this logic to the three routes. I am setting the page, and offset parameter into res.locals so that the next handlers will be able to access these values.

For example, the /book/search/title/:bookTitle/page/:page route can be implemented just like the /book/search/all/page/:page route!

app.get('/book/search/title/:bookTitle/page/:page', async (req, res) => {
    const page = res.locals.page;
    const offset = res.locals.offset;
    const bookTitle = req.params.bookTitle.trim().toLowerCase();

    const results = await Book.listAllBooksByRelevantTitles(offset, DEFAULT_LIMIT, bookTitle);

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

    const pages = Math.ceil((await Book.numberOfPagesByRelevantTitle(bookTitle)) / DEFAULT_LIMIT);
    const lastBookTitle = await Book.lastBookTitleByRelevantTitle(bookTitle);
    const hasMore = !!!results.find(r => r.title === lastBookTitle);

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

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

It looks just like the /book/search/all/page/:page route, except now I am using logic specific to searching by the title!‌

I won’t show how to search by author since it is similar to the other two routes, anyway.

Now it is time for the main event… actually creating the ListBooks page!

First thing’s first, the page should display a table of results similar that was mentioned in the previous article. Given a result set, I want each book to be displayed with the following properties:

The page should have the results be paginated and display the default number of results in each page. How it should be done is that the _meta property returned by the response to the API call to search should be inspected to determine how to display the pagination controls.‌

A filter is also needed to either search by Title, or Author. The mode can be represented by an enum.

enum SearchBy {
    Title = 'TITLE',
    Author = 'AUTHOR'
};

The following state variables are needed:‌

const [page, setPage] = useState(1);
const [totalPages, setTotalPages] = useState(0);
const [bookData, setBookData] = useState([]);
const [searchQuery, setSearchQuery] = useState('');
const [searchBy, setSearchBy] = useState(SearchBy.Title);

Now, I can write some of the handler functions to change the state value of the searchQuery, and searchBy values:

const searchOptions = [
    {label: 'Title', value: SearchBy.Title},
    {label: 'Author', value: SearchBy.Author}
];

function onSearchByChange(e: SyntheticEvent) {
    const select = e.target as HTMLSelectElement;

    const selected = select.options.item(select.selectedIndex)?.value;

    if (!selected)
        return;
    
    setSearchBy(selected === SearchBy.Title ? SearchBy.Title : SearchBy.Author);
}

function onSearchQueryChange(e: SyntheticEvent) {
    const input = e.target as HTMLInputElement;
    const query = input.value;

    setSearchQuery(query);
}

Of course, we can imagine that these will be handlers for a SelectBox (onSearchByChange), and TextField (onSearchQueryChange).

Now, how about the search action itself? If the user hypothetically hits a “search” button, the UI must be triggered to perform an API call to the server to retrieve the relevant results. I can write a search handler to take care of the logic.

I can check the value of the searchBy state variable to see what type of endpoint should be hit based on the type of search to be performed. This is in precondition that there is a value stored in searchQuery. If there is no value, then it is assumed that the user wants to search for ALL books.

function search() {
    let endpoint = '';
    if (searchBy === SearchBy.Title && searchQuery.length > 0)
        endpoint = `${API_HOST}/book/search/title/${searchQuery}/page/${page}`;
    else if(searchBy === SearchBy.Author && searchQuery.length > 0)
        endpoint = `${API_HOST}/book/search/author/${searchQuery}/page/${page}`;
    else
        endpoint = `${API_HOST}/book/search/all/page/${page}`;


    const dataFetch = async () => {
        const data = await (await fetch(endpoint)).json();

        setBookData(data.books);
        setTotalPages(data._meta.pages);
    };

    dataFetch();
}

When the user potentially pages through the results, the page state variable is changing. This is an opportunity to grab the next set of results by calling the search function. useEffect hook is handy here.

useEffect(search, [page]);

A bunch of page links can be rendered with event handlers to set the new page value is created by the createPageLinks. When setPage is called, the page state variable is changed, and then the search is called again to update the table with the current set of results found.

Page links
function createPageLinks() {
    const pageLinks = [];

    if (page - 1 >= 1)
        pageLinks.push(<button className='page-nav-link'
            key={'arrow-back'}
            onClick={() => setPage(page - 1)}
        >{'<<'}</button>);

    for(let i = 1; i <= totalPages; i++) {
        pageLinks.push(<button className={`page-nav-link ${i === page ? 'active' : undefined}`}
            key={i} 
            onClick={() => setPage(i)}
        >{i}</button>);
    }

    if(page + 1 <= totalPages)
        pageLinks.push(<button className='page-nav-link'
            key={'arrow-next'}
            onClick={() => setPage(page + 1)}
        >{'>>'}</button>)

    return pageLinks;
}

Finally, the contents of the page can be rendered with the search filter on top, the table as the main content, and the links to page through the next set of results in the bottom.

return (
    <div className="list-books container">
        <div className="row">
            <div className="col col-6">
                <TextField id="search-input" label="Search query" onChange={onSearchQueryChange} />
            </div>
            <div className="col col-4">
                <SelectBox id="search-type" label="Search by" onChange={onSearchByChange} options={searchOptions} />
            </div>
        </div>
        <div className="row">
            <div className="col col-4">
                <button type="button" className="btn btn-primary" onClick={search}>Search</button>
            </div>
        </div>
        <div className="row">
            <div className="col col-12">
                <table className="table">
                    <thead>
                        <tr>
                            <th>Title</th>
                            <th>Author</th>
                            <th>Year</th>
                            <th>Lexile</th>
                            <th>Total Questions</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        {
                            bookData.map((b: any) =>
                                <tr key={b.id}>
                                    <td>{b.title}</td>
                                    <td>{b.author}</td>
                                    <td>{b.year}</td>
                                    <td>{b.lexile}</td>
                                    <td>{b.totalQuestions}</td>
                                    <td><button type="button" className="btn btn-primary">Edit</button></td>
                                </tr>
                            )
                        }
                    </tbody>
                </table>
            </div>
        </div>
        <div className="row justify-content-center">
            <div className="col col-4">
                {
                    createPageLinks()
                }
            </div>
        </div>
        <div className="row">
            <div className="col col-12">
                <em>Result Information</em>
            </div>
        </div>
        <div className="row">
            <div className="col col-12">
                <strong>Total Pages {totalPages}</strong>
            </div>
        </div>
    </div>
);
Rendered page