https://wiki.creativecommons.org/index.php?title=Cchost/developer/tutorial/Ultimate_Template&feed=atom&action=historyCchost/developer/tutorial/Ultimate Template - Revision history2024-03-28T23:04:50ZRevision history for this page on the wikiMediaWiki 1.30.0https://wiki.creativecommons.org/index.php?title=Cchost/developer/tutorial/Ultimate_Template&diff=19760&oldid=prevFourstones: /* Overview */2008-11-13T11:13:25Z<p><span dir="auto"><span class="autocomment">Overview</span></span></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr style="vertical-align: top;" lang="en">
<td colspan="2" style="background-color: white; color:black; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: white; color:black; text-align: center;">Revision as of 11:13, 13 November 2008</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l7" >Line 7:</td>
<td colspan="2" class="diff-lineno">Line 7:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>{{lowercase}}</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>{{lowercase}}</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>=Overview=</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>=Overview=</div></td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>ccHost employs a powerful and flexible <ins class="diffchange diffchange-inline">[[Cchost/concepts/Query_Engine|</ins>query<ins class="diffchange diffchange-inline">]] </ins>and <ins class="diffchange diffchange-inline">[[Cchost/concepts/Templates|</ins>template<ins class="diffchange diffchange-inline">]] </ins>system that makes it easy to perform simple tasks but also allows for more complicated scenarios in a graceful way<ins class="diffchange diffchange-inline">.</ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div> </div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins class="diffchange diffchange-inline">In this tutorial we go 'full throttle' on the display system and delve deep in the template, query and [[Cchost/developer/concepts/Working_With_Data#Dataviews|dataview]] subsystems</ins>.</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">In this tutorial we go 'full throttle' on the display system and delve deep in the template, query and dataview subsystems.</del></div></td><td colspan="2"> </td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>=Prerequisites=</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>=Prerequisites=</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>It is assumed that understand the basics of [[Cchost/How_Content_is_Displayed|how content is displayed]] and  </div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>It is assumed that understand the basics of [[Cchost/How_Content_is_Displayed|how content is displayed]] and  </div></td></tr>
</table>Fourstoneshttps://wiki.creativecommons.org/index.php?title=Cchost/developer/tutorial/Ultimate_Template&diff=19738&oldid=prevFourstones: New page: Category:ccHost Category:ccMixter Category:Developer Category:Software Category:Technology {{cchost_head}} {{lowercase}} =Overview= ccHost employs a powerful and flexib...2008-11-12T11:09:23Z<p>New page: <a href="/wiki/Category:CcHost" title="Category:CcHost">Category:ccHost</a> <a href="/wiki/Category:CcMixter" title="Category:CcMixter">Category:ccMixter</a> <a href="/wiki/Category:Developer" title="Category:Developer">Category:Developer</a> <a href="/wiki/Category:Software" title="Category:Software">Category:Software</a> <a href="/wiki/Category:Technology" title="Category:Technology">Category:Technology</a> {{cchost_head}} {{lowercase}} =Overview= ccHost employs a powerful and flexib...</p>
<p><b>New page</b></p><div>[[Category:ccHost]]<br />
[[Category:ccMixter]]<br />
[[Category:Developer]]<br />
[[Category:Software]]<br />
[[Category:Technology]]<br />
{{cchost_head}}<br />
{{lowercase}}<br />
=Overview=<br />
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.<br />
<br />
In this tutorial we go 'full throttle' on the display system and delve deep in the template, query and dataview subsystems.<br />
=Prerequisites=<br />
It is assumed that understand the basics of [[Cchost/How_Content_is_Displayed|how content is displayed]] and <br />
you've done the [[Cchost/Custom_Query_Templates|Custom Query Template]] walk through.<br />
=Starter Template=<br />
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.<br />
<br />
Assuming a template '''<local_files>/skins/ultimate.tpl''' that looks like:<br />
<br />
%%<br />
[meta]<br />
type = format<br />
dataview = default<br />
[/meta] <br />
%%<br />
<br />
%loop(records,R)%<br />
%(#R/uplolad_name)% %text(str_by)% %(#R/user_real_name)%<br />
%end_loop%<br />
<br />
%call(prev_next_links)%<br />
<br />
You can query this template using:<br />
<br />
'''api/query?t=ultimate'''<br />
<br />
=Custom Dataview=<br />
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. <br />
<br />
For performance reasons we want to get only columns we care about, so we'll craft a ''dataview'' that delivers just that:<br />
<br />
1. %%<br />
2. [meta]<br />
3. type = format<br />
4. dataview = ultimate<br />
5. embedded = 1<br />
6. [/meta] <br />
7. [dataview]<br />
8. function ultimate_dataview()<br />
9. {<br />
10. $sql =<<<EOF<br />
11. SELECT upload_name, user_real_name<br />
12. %columns% <br />
13. FROM cc_tbl_uploads<br />
14. JOIN cc_tbl_user ON upload_user=user_id<br />
15. %joins%<br />
16. %where%<br />
17. %order%<br />
18. %limit%<br />
19. EOF;<br />
20.<br />
21. $sql_count =<<<EOF<br />
22. SELECT COUNT(*) <br />
23. FROM cc_tbl_uploads<br />
24. JOIN cc_tbl_user ON upload_user = user_id<br />
25. %joins%<br />
26. %where%<br />
27. EOF;<br />
28.<br />
29. return array( 'sql' => $sql,<br />
30. 'sql_count' => $sql_count,<br />
31. 'e' => array()<br />
32. );<br />
33. }<br />
34. <br />
35. [/dataview]<br />
36. %%<br />
37.<br />
38. %loop(records,R)%<br />
39. <nowiki><b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> <br /></nowiki><br />
40. %end_loop%<br />
41. <br />
42. %call(prev_next_links)%<br />
<br />
Now we will go through this new template line by line:<br />
<br />
1. This is the tpl phrase for starting a comment block<br />
<br />
2. This opens the ''meta'' section of the file. The Query engine will read these properties.<br />
<br />
3. The ''type'' property is mainly used by admin tools. The ''format'' value is a generic value for query templates.<br />
<br />
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''.<br />
<br />
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.<br />
<br />
7. This is the start of the actual dataview.<br />
<br />
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.<br />
<br />
10. This is the SQL query that represents the dataview query. We only want two columns and here is where we specify that.<br />
<br />
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.<br />
<br />
13-14. These are standard SQL that specifies the tables to be used.<br />
<br />
15-17. Like %columns%, these keywords will be replaced by the query engine based on the query parameters.<br />
<br />
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.<br />
<br />
22. Our queries will be pretty generic so 'COUNT(*)' is fine.<br />
<br />
23-24. We want to make sure we have the same table structure as the normal query.<br />
<br />
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.<br />
<br />
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.)<br />
<br />
32-36. Closes off the various blocks<br />
<br />
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.<br />
<br />
39. The columns available per record are exactly those we specified in the dataview, in this case, just these two.<br />
<br />
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.<br />
<br />
=Add Simple Columns=<br />
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:<br />
<br />
11. SELECT upload_name, user_real_name''', (upload_num_remixes + upload_num_pool_remixes) as num_remixes'''<br />
<br />
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:<br />
<br />
39. <nowiki><b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> (%(#R/num_remixes)%) <br /></nowiki><br />
<br />
If we now craft our query to sort by the number of remixes:<br />
<br />
'''api/query?t=ultimate&sort=remixes&ord=desc'''<br />
<br />
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:<br />
<br />
api/query?t=ultimate&sort=remixes&ord=desc'''&sinced=1 month ago'''<br />
<br />
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:<br />
<br />
api/query?t=ultimate&sort=remixes&ord=desc&sinced=1 month ago&title=Hottest Sources<br />
<br />
==With Helpers==<br />
There are a few helpers for jamming columns. One of the more useful ones generates a URL to the user's avatar.<br />
<br />
function ultimate_dataview()<br />
{<br />
'''$user_avatar_col = cc_get_user_avatar_sql();'''<br />
<br />
$sql =<<<EOF<br />
SELECT upload_name, user_real_name, (upload_num_remixes+upload_num_pool_remixes) as num_remixes<br />
''', {$user_avatar_col}'''<br />
%columns% <br />
<br />
Now you can add the user's avatar into the template code:<br />
<br />
%loop(records,R)%<br />
'''<nowiki><img src="%(#R/user_avatar_url)%" /></nowiki>'''<br />
<nowiki><b>%(#R/upload_name)%</b> ...</nowiki> <br />
<br />
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:<br />
<br />
function ultimate_dataview()<br />
{<br />
$user_avatar_col = cc_get_user_avatar_sql();<br />
<br />
'''$base_people_url = ccl('people') . '/';'''<br />
<br />
$sql =<<<EOF<br />
SELECT upload_name, user_real_name, (upload_num_remixes+upload_num_pool_remixes) as num_remixes<br />
, {$user_avatar_col}<br />
'''<nowiki>, CONCAT('{$base_people_url}',user_name) as artist_page_url</nowiki><br />
%columns% <br />
<br />
Then wrap the avatar with that link:<br />
<br />
%loop(records,R)%<br />
'''<nowiki><a href="%(#R/artist_page_url)%"></nowiki>'''<nowiki><img src="%(#R/user_avatar_url)%" /></nowiki>'''<nowiki></a></nowiki>'''<br />
<nowiki><b>%(#R/upload_name)%</b>...</nowiki><br />
<br />
=Filtering Data=<br />
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.<br />
<br />
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:<br />
<br />
SELECT upload_name, user_real_name, (upload_num_remixes+upload_num_pool_remixes) as num_remixes<br />
, {$user_avatar_col}<br />
, CONCAT('{$base_people_url}',user_name) as artist_page_url<br />
''', upload_contest, upload_id, user_name'''<br />
%columns% <br />
<br />
and our return statement looks like:<br />
<br />
return array( 'sql' => $sql,<br />
'sql_count' => $sql_count,<br />
'e' => array( '''CC_EVENT_FILTER_DOWNLOAD_URL''' )<br />
);<br />
<br />
And we can add a 'download' link to our listing:<br />
<br />
<nowiki><b>%(#R/upload_name)%</b> %text(str_by)% <i>%(#R/user_real_name)%</i> (%(#R/num_remixes)%)</nowiki> <br />
'''<nowiki><a href="%(#R/download_url)%">download!</a></nowiki>'''<nowiki><br /></nowiki></div>Fourstones