Overview

ccHost employs a powerful and flexible query and template system that makes it easy to perform simple tasks but also allows for more complicated scenarios in a graceful way.

In this tutorial we go 'full throttle' on the display system and delve deep in the template, query and dataview subsystems.

Prerequisites

It is assumed that understand the basics of how content is displayed and you've done the Custom Query Template walk through.

Starter Template

Let's create a chart that displays the most remixed uploads to our site. Doing the sort will be easy enough because the Query API already has a sort criteria called remixes that will give us the records we want.

Assuming a template <local_files>/skins/ultimate.tpl that looks like:

  %%
    [meta]
      type     = format
      dataview = default
    [/meta]      
  %%

  %loop(records,R)%
     %(#R/uplolad_name)% %text(str_by)% %(#R/user_real_name)%
  %end_loop%

  %call(prev_next_links)%

You can query this template using:

 api/query?t=ultimate

Custom Dataview

A quick peek at the default dataview (you can dump it live by adding &dpreview=1 to the query URL) and you'll see that there are whole bunch of columns that we don't need and causing a huge SQL expensive.

For performance reasons we want to get only columns we care about, so we'll craft a dataview that delivers just that:

   1.  %%
   2.   [meta]
   3.     type     = format
   4.     dataview = ultimate
   5.     embedded = 1
   6.   [/meta]      
   7.   [dataview]
   8.     function ultimate_dataview()
   9.     {
  10.        $sql =<<<EOF
  11.           SELECT upload_name, user_real_name
  12.              %columns% 
  13.           FROM cc_tbl_uploads
  14.           JOIN cc_tbl_user ON upload_user=user_id
  15.           %joins%
  16.           %where%
  17.           %order%
  18.          %limit%
  19.  EOF;
  20.
  21.        $sql_count =<<<EOF
  22.           SELECT COUNT(*) 
  23.           FROM cc_tbl_uploads
  24.           JOIN cc_tbl_user ON upload_user = user_id
  25.           %joins%
  26.           %where%
  27.  EOF;
  28.
  29.           return array( 'sql' => $sql,
  30.                  'sql_count' => $sql_count,
  31.                  'e'  => array()
  32.             );
  33.       }
  34. 
  35.     [/dataview]
  36.  %%
  37.
  38.  %loop(records,R)%
  39.      <b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> <br />
  40.  %end_loop%
  41.  
  42.  %call(prev_next_links)%

Now we will go through this new template line by line:

1. This is the tpl phrase for starting a comment block

2. This opens the meta section of the file. The Query engine will read these properties.

3. The type property is mainly used by admin tools. The format value is a generic value for query templates.

4. Every query template must have a dataview. If not specified the Query engine will use default (found in ccskins/ccdataviews). Here we tell the Query engine to use a dataview called ultimate.

5. The embedded property tells the Query engine that the dataview will be embedded into this file and not to look for another dataview file.

7. This is the start of the actual dataview.

8. A dataview is basically a PHP function with the name of the dataview prepended to _dataview. The Query engine will execute this function and then perform the SQL query.

10. This is the SQL query that represents the dataview query. We only want two columns and here is where we specify that.

11. The Query engine will search and replace certain keywords in the SQL statement. The %columns% keyword will expand to any extra columns required by parameters passed to the query engine.

13-14. These are standard SQL that specifies the tables to be used.

15-17. Like %columns%, these keywords will be replaced by the query engine based on the query parameters.

21. When you want to do paging ('previous', 'next', etc.) it helps to know when you've reached the end. We need a SQL way to count the total number of records that meet the criteria of the query.

22. Our queries will be pretty generic so 'COUNT(*)' is fine.

23-24. We want to make sure we have the same table structure as the normal query.

25-26. We do not want to include the %limit% keyword because that would defeat the purpose of getting a total count. We do not want to include the %order% keyword because that would be wasted calculation. We just want the total and it's the %where% that decides that.

29-31. Here we return the SQL statements in an array. The 'e' element tells the Query engine to trigger filter events after the query but we don't have any of those, we just want to the two columns, raw and unfiltered. (More about filtering later.)

32-36. Closes off the various blocks

38. Starting from this point on is what the template engine cares about. In fact, everything between lines 1-36 will be removed when the files get 'translated' from TPL to normal PHP. There is a magic array called 'records' which is the result of the query that we loop over.

39. The columns available per record are exactly those we specified in the dataview, in this case, just these two.

42. We call the template that emit the 'prev' and 'next' buttons for paging through the records. That template relies on the sql_count data we crafted in the dataview.

Add Simple Columns

By now it should be obvious that if we wanted to add a column to our display that we have to add it to our dataview. If we edit the SELECT statement in our dataview to something like this:

  11. SELECT upload_name, user_real_name, (upload_num_remixes + upload_num_pool_remixes) as num_remixes

then we have a new column called num_remixes that displays the number of times this upload has been remixed and we can include that in the template code:

  39.      <b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> (%(#R/num_remixes)%) <br />

If we now craft our query to sort by the number of remixes:

 api/query?t=ultimate&sort=remixes&ord=desc

then all of a sudden we have a chart that displays the all-time remixed champion on the site. To make the chart more current we can add a sinced parameter to limit the results to just the last month:

 api/query?t=ultimate&sort=remixes&ord=desc&sinced=1 month ago

If you give this query title and put this query into a navigator tab you site now has a new feature:

 api/query?t=ultimate&sort=remixes&ord=desc&sinced=1 month ago&title=Hottest Sources

With Helpers

There are a few helpers for jamming columns. One of the more useful ones generates a URL to the user's avatar.

    function ultimate_dataview()
    {
       $user_avatar_col = cc_get_user_avatar_sql();

       $sql =<<<EOF
         SELECT upload_name, user_real_name, (upload_num_remixes+upload_num_pool_remixes) as num_remixes
                , {$user_avatar_col}
              %columns% 

Now you can add the user's avatar into the template code:

  %loop(records,R)%
     <img src="%(#R/user_avatar_url)%" />
     <b>%(#R/upload_name)%</b> ... 

Wouldn't it be nice to make that avatar into a link that points back to the user's profile page? For that we can use the 'ccl URL building helper. This function will construct a ccHost command URL regardless if the installation is using pretty URLs or alternate virtual roots. We don't want to do that calculation for every row however, so we'll do the base part once and tell mySQL to CONCAT that with the user's name:

    function ultimate_dataview()
    {
       $user_avatar_col = cc_get_user_avatar_sql();

       $base_people_url = ccl('people') . '/';

       $sql =<<<EOF
         SELECT upload_name, user_real_name, (upload_num_remixes+upload_num_pool_remixes) as num_remixes
                , {$user_avatar_col}
                , CONCAT('{$base_people_url}',user_name) as artist_page_url
              %columns% 

Then wrap the avatar with that link:

  %loop(records,R)%
     <a href="%(#R/artist_page_url)%"><img src="%(#R/user_avatar_url)%" /></a>
     <b>%(#R/upload_name)%</b>...

Filtering Data

Add a download button is not such a trivial task. ccHost does not store an actual download URL in the table, it jumps through quite a few hoops to calculate it on the fly taking many variables into account. Since this is a relatively popular piece of data we want some common code that adds the 'download_url' column. That's where filtering comes to the rescue.

We will add the CC_EVENT_FILTER_DOWNLOAD_URL to our events returning from the dataview. The only requirement is that we add three more columns to the SELECT statement to make that work. These columns are required for the filter to calculate the proper download URL. So the SELECT statement now looks like:

         SELECT upload_name, user_real_name, (upload_num_remixes+upload_num_pool_remixes) as num_remixes
                , {$user_avatar_col}
                , CONCAT('{$base_people_url}',user_name) as artist_page_url
                , upload_contest, upload_id, user_name
              %columns% 

and our return statement looks like:

   return array( 'sql'        => $sql,
                  'sql_count' => $sql_count,
                  'e'         => array( CC_EVENT_FILTER_DOWNLOAD_URL )
               );

And we can add a 'download' link to our listing:

     <b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> (%(#R/num_remixes)%) 
       <a href="%(#R/download_url)%">download!</a><br />