cfquery分组，计数& maxrows [英] cfquery grouping, counts & maxrows

问题描述

我将首先解释我在做什么 - 我觉得一个真实世界的解释会使这更容易理解。我有一个类别&子类别在线商店列表。数据库表以简单的方式配置--id，category_name和subcategoryof - subcategoryof为0以表示顶级类别。

I'll start by explaining what I'm doing - I feel a 'real world' explanation will make this easier to understand. I have a Category & Subcategory list for an online store. The database table is configured in a simple fashion - id, category_name and subcategoryof - subcategoryof being a 0 to represent a top-level category.

在商店的主索引中，我列出所有类别和子类别 - 在cfoutput查询（下面粘贴的代码）中使用使用左连接的单个查询和cfoutput的组合。这很好，并允许我风格的顶级类别和子类别足够好，以区分它们。

In the main index of the store, I'm listing all categories and subcategories - using a combination of a single query using a left join, and cfoutput within the cfoutput query (code pasted below). This works well and allows me to style the top-level categories and subcategories well enough to distinguish between them.

但是，列表现在有点长，现在需要相当多的滚动才能浏览页面。我想要做的是这样的;

However, the list is getting a bit long now and a fair amount of scrolling is now required to navigate the page. What I would like to do is this;

输出所有顶级类别，如目前
只输出2个子类别
显示

Output all the top-level categories as at present Output only 2 of the Subcategories Show a count of how many extra subcategories there are (i.e +7 More Subcategories).

现在，我会输出一切，就像我现在做的 - 但是在第二个子类别之后，我将会将以下子类别设置为隐藏，并使用+7更多子类别文本切换显示（仍然与我在这里。）

Now, I will actually output everything as I do now - however after the 2nd subcategory, I'll set the following subcategories as hidden and use the '+7 More Subcategories' text to toggle the display (still with me here?).

我的问题和问题，是这个 - 我如何获得一个子类别的计数用于我的'+ x更多子类别'文本？

My problem, and question, is this - how do I get a count of subcategories for use with my '+x More Subcategories' text? I assume my current plan of using an incremental count to determine the point at which I start hiding rows is the best route?

以下现有代码

<cfquery name="getcategories">
SELECT p.ID AS CategoryID, p.Cat_Name as CategoryName, p.Cat_Shortname, c.ID AS SubCategoryID, c.Cat_Name as SubCategoryName, c.Cat_Shortname AS SubCatShortname
FROM    product_categories p LEFT JOIN product_categories c ON p.ID = c.SubcategoryOf
WHERE  p.SubcategoryOf = 0
</cfquery> 
<ul>
   <cfoutput query="getcategories" group="CategoryName">
    <li class="catli"><a href="">#CategoryName#</a></li> 
    <cfoutput><li class="subli"><a href="">#SubcategoryName#</a></li></cfoutput>
        <li class="subli moreli"><a href="">+ 7 More Subcategories</a></li>
   </cfoutput>
</ul>

推荐答案

<ul>
   <cfset SubcategoryNames = []>
   <cfoutput query="getcategories" group="CategoryName">
    <li class="catli"><a href="">#CategoryName#</a></li> 
        <cfoutput>
          <cfset arrayAppend(subcategoryNames, SubcategoryName)>
        </cfoutput>
        <cfloop from="1" to="#max(arrayLen(subcategoryNames),2)#" index="i">
            <li class="subli"><a href="">#SubcategoryName[i]#</a></li>
        </cfloop>
        <cfif arrayLen(subcategoryNames) GT 2>
          <li class="subli moreli">
            <a href="">+ #arrayLen(subcategoryNames) - 2# More Subcategories</a>
          </li>
        </cfif>
   </cfoutput>
</ul>

这篇关于cfquery分组，计数& maxrows的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！