Forum for discussing general topics related to Couch.
3 posts Page 1 of 1
How to display popular article from multiple pages?
I try this code but didn't work
Code: Select all
<cms:query
    sql="SELECT p.id, p.template_id
        FROM <cms:php>echo K_TBL_PAGES;</cms:php> p
        inner join <cms:php>echo K_TBL_TEMPLATES;</cms:php> t
        on p.template_id = t.id
        WHERE (t.name='car.php' or t.name='books.php' or t.name='news.php')
        AND publish_date < '<cms:date format='Y-m-d H:i:s' />'
        AND NOT publish_date = '0000-00-00 00:00:00'
        ORDER BY publish_date desc;"
    limit='10'
    paginate='1'
    fetch_pages='1'
    orderby='page_hits'
    >

Could not successfully run query: Unknown column 'page_hits' in 'order clause'
Still didn't work use this code
Code: Select all
<cms:templates order='asc' >
    <cms:if k_template_is_clonable >
        <cms:if k_template_name!='globals.php'>
            <cms:pages masterpage=k_template_name orderby='page_hits'>
            <div class="nk-widget-post">
                <a href="<cms:show k_page_link />" class="nk-post-image">
                    <img src="<cms:thumbnail news_image width='160' height='136' crop='1' />" alt="<cms:show k_page_title />">
                </a>
                <h3 class="nk-post-title"><a href="<cms:show k_page_link />"><cms:show k_page_title /></a></h3>
                <div class="nk-post-date"><span class="fa fa-calendar"></span> <cms:date k_page_date format='j M, Y' /></div>
            </div>
            </cms:pages>
        </cms:if>
    </cms:if>
</cms:templates>
Hi,

The proposed first solution wouldn't work because the 'page_hits' field is not a part of the K_TBL_PAGES table -
Couch uses a EAV style database structure and the custom fields are actually defined in two other tables namely K_TBL_DATA_TEXT and K_TBL_DATA_NUMERIC). Anyway, creating a performant query for your use-case wouldn't be easy so let us move to your second solution.

The basic idea there is sound. I think why it is not working is because you are looping through *all* the existing templates and then trying to sort the pages of each using 'page_hits' field. I'd assume that not all the templates have this field defined so the first one that lacks it will throw an error and the script will die.

A better way would be to explicitly use only those templates that do have the 'page_hits' region defined.
So start with a single template - suppose the following piece of code fetching top two pages of a hard-coded template name works well for you -
Code: Select all
<cms:pages masterpage='cars.php' orderby='page_hits' limit='2'>
    ...
</cms:pages>

- we can then use it in a loop for three different templates this way
Code: Select all
<cms:each 'car.php|books.php|news.php' >
    <cms:pages masterpage=item orderby='page_hits' limit='2'>
        ...
    </cms:pages>
</cms:each>

Please notice that we have replaced the hard-coded template name with a variable named 'item' (provided by the enclosing <cms:each> loop for each item in its list.

Hope this helps.
3 posts Page 1 of 1