Section 5.2. Listing by Category

5.2. Listing by Category

Let's change our list controller to return all the categories from the database, and then we'll iterate over that in the template. First, however, we need some data in our database so that we'll have something to see when we get this code written.

We could easily add more sample data from the command line. But it's time to introduce another set of tools that come with TurboGears. If you run tg-admin toolbox, a special TurboGears application called the Toolbox starts.

The Toolbox contains the easiest way to add data to your application during development. The CatWalk tool works with all the databases that TurboGears supports, including SQLite, MySQL, and PostgreSQL. If you start the Toolbox from the root of your project directory, it will pick up your database configuration from your Turbo-Gears config file and work with your existing database with no configuration.

The tg-admin toolbox command should automatically open a browser; if it doesn't, you can just browse to http://localhost:7654 and click CatWalk to start editing your data.

CatWalk is powerful and easy to use, but there are a couple of tips that will make adding join relationships easier.

Although it's pretty obvious how you can add information to your tables with CatWalk, the interface to create relationships for many-to-many joins isn't immediately apparent. But it's simple: Add the information you want to both tables normally, and then when the things you want to link together exist, go back and browse the category you want to add bookmarks to, and then click Manage Relationships.

Feel free to use it whenever you need to add data to your model while in development. In fact, as we are finishing up this book, work is underway to make a CatWalk-style admin interface that you can easily use and extend right within your application. So, it's possible that by the time this book comes out in print, CatWalk will make creating CRUD (Create, Read, Update, Destroy) interfaces in TurboGears even easier.

Figure 5.1. Using CatWalk to add data

As previously mentioned, you can also add categories and bookmarks from the command line. And even though we can add all the data with CatWalk with less typing, let's explore the command-line interface, because that's the same syntax we need when we want to relate bookmarks and categories in our code later on.

TurboGears, tg-admin shell, and SQLObject make this easy to do from the command line or a script. SQLObject automatically creates addCategories and removeCategories methods for our Bookmark objects. It also creates addBookmarks and removeBookmarks methods for our Categories objects. These methods are based on the name of the class on the other end of your multiple join. If we fire up tg-admin shell, we can add a new category with the following:

a=Categories(categoryName="Search")

This is the same thing we did earlier, so it ought to be familiar now. We can also grab a bookmark like this:

>>> b=Bookmarks.get(1)

Then we join them together by passing a to the addCategories method of b, or by passing b to the addBookmarks method of a:

>>> b.addCategories(a) >>> a.addBookmarks(b)

Either of these commands sets up a relationship between these two rows by generating a new row in the join table that SQLObject created to hold information about the relationships between bookmarks and categories.

After you've added some data, the next order of business is to add a controller that passes our categories in to a Kid template.

You'll want to update your import at the top of the file to pull in both bookmarks and categories from the model, and then you can create a new method in controllers that returns a SQLObject selectResult object with all the categories in the database. This is all we need because SQLObject makes it easy to iterate over all the bookmarks in a particular category:

@expose(template="bookmarker.templates.categorylist") def list_by_category(self):     c=Categories.select()     return dict(categories=c)

This controller method looks almost exactly the same as the list method. The only difference is that now we're selecting everything in the Categories table and sending that SQL selectResults object to our template, while list uses the Bookmarks table instead.

So, we could create a categorylist.kid template file that looks like this:

<body>   <span py:for="category in categories">     <h3 py:content="category.categoryName">Category</h3>   </span> </body>

This template just lists all of our categories, but that's not exactly what we want. We really want to loop over all our categories, and then for each category loop over the bookmarks in that category and display them, too. Fortunately that's easy, too:

<span py:for="category in categories">   <h3 py:content="category.categoryName">Category</h3>   <ul>     <li py:for="bookmark in category.categoryItems">       <a href="${bookmark.link}">         <span py:replace="bookmark.bookmarkName">Link to Bookmark</span>       </a>         --  <span py:replace="bookmark.description">             Description of the Bookmark goes here.             </span>     </li>   </ul> </span>

This shows a couple pieces of Kid and SQLObject that you haven't seen yet, so let's take a closer look at how it works. This template uses a span tag as the top level of its py:for loop to iterate over the contents of the category object. Inside that is a list item (li) tag that loops over each of the bookmarks in that category and creates the same link format we used in our original list page. It does this by calling the categoryItems method on our category objectand if you remember, category is a SQLObject selectResults iterator that automatically gets the categoryItems method because categoryItems is defined as a relatedJoin.

Remember: The py:for directive in a tag repeats that tag and all subtags for each item in the list (or whatever iterable object you give it).

Other than that, we are just using the same code we previously used to print the list of all bookmarks to list only those links that are members of our current category.