Xataface provides nice list and details views for your tables, but the information displayed on these pages are limited to fields that are physically part of the table.  But what if you want to show some related information that is helpful.

For example, what if you have tables users and articles and where the articles table has a column author which stores the user_id of the user who wrote the article.  Further, suppose that we want to display the number of articles that each user has written in the list view and the details view?

Prior to Xataface 0.6.3, you would have to override the list view template and create your own list, in order to incorporate this column.  However, you are now (as of 0.6.3) able to show this information by adding the following to the beginning of the users fields.ini file:

__sql__ = "select 
             u.*, a.num_articles 
           from 
             users u
             left join 
             (
               select 
                 author_id, count(*) as num_articles 
               from 
                 authors 
               group by 
                 author_id
             ) as a 
             on 
               a.author_id=u.user_id"

Note that in PHP 4.x multiline values in ini files were not supported, so you would have to place the above on one line.

Now, when Xataface loads the records (or a record) from the users table it will use the query specified here rather than the usual "select * from users".

Explanations

 select u.*, a.num_articles

This means that we are selecting all of the columns from the users table and another column named num_articles that we define later in the query.  Any time we are redefinine the SQL query to be used to load a table, we need to all of the columns from the main table (in this case the users table) selected first, or some unexpected results will occur.

from users u left join (...) as a

We always use a left join when redefining the SQL to select records from a table (as we are doing here) because this ensures that all of the main table will be loaded regardless of the state of the secondary table.  Notice too that we use a subquery for the second table, because there are no tables that directly contain the data that we are looking for.  The results of this subquery are treated as a table in its own right and we assign it the alias a .

select author_id, count(*) as num_articles from authors group by author_id

This is the subquery that builds a table mapping user_id's (author_id) to the number of articles written by that user.  Normally the count(*) mysql function will return the total number of records matching the query, but we used the group by clause here so that the count(*) function will show the number matching each distinct author_id.

 Results

So what have we really done here?  Now the list tab of the users table will include a num_articles column that show the number of articles that each user has written.  Similarly the View tab will show this information for each record.

What did this NOT do?  So far this only helps us for the list tab and the View tab.  This extra column will not show up in related records lists.  Possibly in future versions, this support will be added.

Caveats: Minimum Requirements

The internal Xataface implementation uses subqueries to seamlessly handle custom SQL select statements such as this.  Older versions of MySQL (older than 4.1) do not support subqueries (AFAIK), so this feature will work with MySQL 4.1 or higher.