How to realize a catalog with categories

The current joodb-free version does not support linked tables. (relations) For relatively small tables its no problem to realize even complex presentations like product catalogs with categories.
Imagine you have a Video collection with different movie-genres. (Family, Horror, SCI-FI, Thriller ans so on)

If you look at the menu entry settings for the catalog you will find a option called SQL-Where expression.

There are 2 ways to realize a catalog where you...

1. ... can select only products from a special category
2. ... can put products in multiple categories

The search by LIKE %% approach

This is the way you should choose if the dabase has many categories. It should be even fast enough for many thousands of entries. But the database editors must be disciplined enough to enter te correct category keywords and the separator.
Take a field named categories of type varchar and enter the categories separated by coma or semicolon. For example "fantasy,sci-fy,action".

Create a cataloge view with the following SQL-Where expression:

The SET-Field approach
If you look at the jos_joodb_sample structure with phpmyadmin you will se a field of type SET called category.With the MYSQL-Set type you can select one ore more predefined values. This approach sould be used if you have a small set of predefined categories. It should be fast enough for millions of entries.

Create a catalog view with the following SQL-Where expression:Theoretically you could do the same with »category LIKE '%Food%'«. But like will search the whole string and would find even substrings. FIND_IN_SET or the search for the set column »category &2« will be much faster.

• dev.mysql.com/doc/refman/5.0/en/set.html

If a data entry can only be in one category you should take a ENUM type.

Selecting multiple categories or other values to tune the result
Of course you can select multiple categories or reduce the result using the AND or OR SQL-Expressions.
Example: You have a DATE-Field called release with the release date of the movie and you will show only the newly released fantasy movies of the last 90 days.

Create a cataloge view with the following SQL-Where expression: