View source for Cchost/developer/tutorial/Ultimate Template

[[Category:ccHost]]
[[Category:ccMixter]]
[[Category:Developer]]
[[Category:Software]]
[[Category:Technology]]
{{cchost_head}}
{{lowercase}}
=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 [[Cchost/How_Content_is_Displayed|how content is displayed]] and 
you've done the [[Cchost/Custom_Query_Templates|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.      <nowiki><b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> <br /></nowiki>
   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.      <nowiki><b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> (%(#R/num_remixes)%) <br /></nowiki>

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 [[Cchost/customize/Navigation_Tabs|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)%
      '''<nowiki><img src="%(#R/user_avatar_url)%" /></nowiki>'''
      <nowiki><b>%(#R/upload_name)%</b> ...</nowiki> 

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}
                 '''<nowiki>, CONCAT('{$base_people_url}',user_name) as artist_page_url</nowiki>
               %columns% 

Then wrap the avatar with that link:

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

=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 [[Cchost/developer/concepts/Working_With_Data#Dataview_Filtering|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:

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