theme Advanced theme customization: custom database queries

[Tech Support]

KVS theme customization engine provides a variety of ways to customize theme design and layout, but in some cases it is limited to sets of data it can select. The most important limitation is that you can't use blocks inside other blocks.

There are 2 workarounds for doing this: adjust page layout structure (will not work in all cases) and pass block HTML into another block.

 

Passing HTML from one block into another

Let's take a look at the 2nd option, as it provides much more flexibility. Imagine that you have a list of videos and you want to render a drop down with categories there for fast navigation or to allow AJAX filtering on the selected categories. List of videos can be rendered only by list_videos block, which does not provide you list of categories to show a drop down inside. From the other hand list of categories can be rendered by list_categories block, however as it was said before you can't use one block inside another.

For example this is how you insert list_videos on your page:

{{insert name="getBlock" block_id="list_videos" block_name="Index Videos"}}

In order to build category drop down and pass it to this list_videos block you should add list_categories block before it and assign its HTML result into a variable and then pass this variable to list_videos block using special var_ prefix:

{{insert name="getBlock" block_id="list_categories" block_name="Categories Drop Down" assign="categories_dropdown_html"}}
{{insert name="getBlock" block_id="list_videos" block_name="Index Videos" var_categories_dropdown_html=$categories_dropdown_html|smarty:nodefaults}}

We added assign="categories_dropdown_html" to list_categories block in order to prevent it from printing its HTML into the place where it is inserted (since we need this HTML inside list_videos block). Then we passed $categories_dropdown_html into list_videos: var_categories_dropdown_html=$categories_dropdown_ html|smarty:nodefaults.

Finally inside list_videos block template we can now display this HTML generated by list_categories block:

{{$var_categories_dropdown_html|smarty:nodefaults}}

This will print HTML code from list_categories block inside list_videos block. Looks like what we need.

But in fact this will not work with AJAX pagination and other block-level AJAX functionality. So this approach can only be used with non-AJAX navigation.

 

Using custom database queries from within blocks

For advanced customization you may need to submit additional queries into database in order to select additional data, which is not selected by default in KVS. This will give you almost unlimited design customization abilities and at the same time will keep all KVS site engine benefits such as caching and high performance.

This is not intended for basic users, in order to use it you should understand KVS database structure (since we do not provide any documentation for that, you can ask support if you have specific needs). You can use this in any cases where you want to display some data which is not provided by KVS.

Here is the basic syntax:

{{query_kvs select="list|single|count" table="table_name" [assign="var_name"] [sort_by="sort_by expression"] [limit="number"] [where_xxx="filter value"]}}

Supported options:

• select: required, one of the following: list (to select a list of items), single (to select a single item), count (to select a number of items in database)
• table: required, table name to select from, should be specified in the following syntax: table="categories"
• assign: required if you select list or single item, because you need to put it into a variable, but not required if selecting count; if omitted the output will be printed right a way
• sort_by: optional, sorting SQL expression, should be a valid SQL expression for the specified table, example: sort_by="title asc"
• limit: optional, specify the number of rows you want to select, if not specified then all rows will be selected

You can also specify list of filters using 3 types of where parameters:

• where_xxx: you should replace xxx with column name and specify value for "equals" match, for example: where_is_private="1" (will result in selecting only rows where is_private field = 1)
• wheregt_xxx: you should replace xxx with column name and specify value for "greater than or equals" match, for example: wheregt_post_date="2017-05-23" (will result in selecting only rows where post_date field >= "2017-05-23")
• wherelt_xxx: you should replace xxx with column name and specify value for "less than or equals" match, for example: wherelt_post_date="2017-05-23" (will result in selecting only rows where post_date field <= "2017-05-23")
• wherelike_xxx: you should replace xxx with column name and specify value for LIKE match, for example:
  wherelike_title="test%" (will result in selecting only rows where title field starts with "test")
• whereregexp_xxx: you should replace xxx with column name and specify value for regular expression match, for example:
  whereregexp_title="test.*" (will result in selecting only rows where title field matches regular expression "test.*")

NOTE: where filters are protected from SQL injection, so you can safely use values passed from HTTP variables there, e.g.: where_is_private=$smarty.get.is_private, but sort_by expression is not protected, so you cannot use dynamic sorting here.

Selecting data, which may have limited visibility, such as videos, albums, posts and playlists, will force default filtering (for example only active videos will be selected and etc). If for any specific reason you need to disable this filtering you should add default_filtering="false" option.

You should be aware that submitting heavy selects may drop your project's performance. If used inside list_xxx blocks they can be cached pretty well and will not affect much. If used inside xxx_view blocks or outside blocks (at page level) their performance impact will be as much as many users log into your memberzone.

 

Examples

Take a look at these simple examples to see how powerful this feature can be for building an outstanding site design with very few lines.

 

1. Show a drop down with all categories in list_videos block:

{{query_kvs select="list" table="categories" assign="list_categories" sort_by="title asc"}}
<ul>
{{foreach from=$list_categories item="category"}}
   <li>{{$category.title}}</li>
{{/foreach}}
</ul>

Note that we use assign="list_categories" here and then render this list using standard Smarty {{foreach}}.

 

2. Show the number of videos added for the last year (last 365 days):

{{assign var="where_post_date" value=$smarty.now-365*86400|date_format:"%Y-%m-%d"}}
Videos added last year: {{query_kvs select="count" table="videos" wheregt_post_date=$where_post_date}}

Note that we first form a $where_post_date variable in the needed date format and then pass it to query using wheregt_post_date=$where_post_date. Also we are not using assign with query function, so it will print result right a way:

Videos added last year: 1276

 

3. Show the number of videos that are in process now, e.g. processing queue:

Videos processing right now: {{query_kvs select="count" table="videos" where_status_id="3" default_filtering="false"}}

Note that here we disabled default filtering via default_filtering="false" since by default videos that are in process status will be filtered out from the query.

Using this query can be useful if you want to prevent users from uploading content when there are too many videos being processed. For example you can add this code to video_edit block, which renders upload form:

{{query_kvs select="count" table="videos" where_status_id="3" default_filtering="false" assign="videos_processed_now"}}
{{if $video_processed_now>100}}
Sorry, the upload is limited as we have too many videos in process right now. Please come back later!
{{else}}
... show upload form as usual
{{/if}}

Note that here we are using assign="videos_processed_now" to assign the count of currently processed videos into a variable, so that we can further use it in IF statement.

 

4. In model view page show how many other models with the same hair color:

{{if $data.hair_id>0}}
    More models with the same hair color: {{query_kvs select="count" table="models" where_hair_id=$data.hair_id}}
{{/if}}

Note here we first check if hair color is specified for this model and then show count of models with the same hair color, we stick to the current model's hair color with using where_hair_id=$data.hair_id.