CF8 group

Get Started
1

Hi all. I’m working on a website for a record store, there are between 8 and 10 categories. On the frontpage I show the latest arrivals for which I use a tapped jQuery content slider. It works great but I would like to limit the number of records per group (category_id) . If I use maxrows in my query it limit the total number of records to the number specified. If I use maxrows in the output it limit the total number of categories to the number specified. What should I do to limit the number of records per group, please see query and output below


Query


<cfquery name="getProducts" datasource="Highfidelity">
	SELECT p.product_id, p.artist, p.title, p.publisher, p.release_date, p.price, p.category_id, c.category
    FROM products p
	INNER JOIN categories c ON p.category_id = c.category_id
    ORDER BY p.category_id
</cfquery>

Output


<cfoutput query="getProducts" group="category_id">
<div class="tabbed-content" style="margin-top:40px;">
<cfoutput>
<cfquery name="getPhotos" datasource="Highfidelity">
	SELECT photo
    FROM photos
    WHERE product_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( getProducts.product_id )#" /> )
</cfquery>
<cfquery name="getDescriptions" datasource="Highfidelity">
	SELECT SUBSTRING_INDEX(description, ' ' , 20) AS shortDesc
    FROM descriptions
    WHERE product_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( getProducts.product_id )#" /> )
</cfquery>
<div class="listing #IIf(CurrentRow Mod 2, DE('left-listing'), DE('listing'))#">
<cfif category_id EQ 1 >
<img src="photos/#getPhotos.photo#" width="65" height="90">
<cfelse>
<img src="photos/#getPhotos.photo#" width="90" height="90">
</cfif>
<span class="listingheader">#artist#</span>
#title#<br>
#publisher#, #jaar#<br>
#LSCurrencyFormat( price, 'none' )#<br style="clear:both;">
<p class="listingdescription">#ReplaceNoCase( getDescriptions.shortDesc, chr( 13 )&chr( 10 ), '<br />', 'all')#</p>
</div>
</cfoutput>
</div>
</cfoutput>

Thank you in advance

2

in that case you definitely want INNER JOINs

3

Hi rudi! thank you very very much. You’re right the order by was ambiguous indeed. It is now working like a charm.

About the joins! I have the queiries like this so that for example in the navigation only the categories are shown that have products related to them (hope this make any sense). In total I have 7 categories. If only 4 categories have products related only those categories will show in the drop down menus.

Again thank you so much :slight_smile:

4

The database is more settled now. For the new arrivals I have added two new fields (isNew int(1)) and (added(timestamp default current_timestamp)). For isNew 1 will indicate that it is a new arrival. The Owner will update database once a week on Friday. When new arrivals will be added the value for older records with value 1 will be set to 0. This is the query:


<cfquery name="getProducts" datasource="#application.dsn#">
    SELECT p.product_id, p.artist, p.title, p.publisher, p.release_date, p.condition, p.price, p.category_id, c.category, p.genre_id, g.genre
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN genres g ON p.genre_id = g.genre_id
    WHERE isNew = 1               
    ORDER BY category_id
</cfquery>

But I still don’t have a sollution for the limited group output. I would like to limit the output per group to 4 records. I tried a conditional loop:


<cfoutput query="getProducts" group="category_id">
    <ul>
    <cfset count = 1>
    <cfloop condition="count lte 3">
        <cfoutput><li>#artist#</li></cfoutput>
        <cfset count = count + 1>
    </cfloop>
    </ul>
</cfoutput>

but it isn’t working at all. Do you have any idea what to do?

Thank you in advance

5

let’s start with the query, which won’t work as written because of the ambiguous column in the ORDER BY clause

(hint: qualify all columns used in a query that involves more than one table)

is it possible that you have a product for a category that doesn’t exist? is it possible that you have a product for a genre that doesn’t exist? if the answer to these questions is no, then you should be using inner joins, not outer joins

as for your output, you don’t need the CFLOOP but you do need a CFIF to test the counter against your number 4

<cfoutput query="getProducts" group="category_id">
    <ul>
    <cfset count = 1>
    <cfoutput>
        <cfif count LT 4>
           <li>#artist#</li>
        </cfif>
        <cfset count = count + 1>
    </cfoutput>
    </ul>
</cfoutput>
6

I will rudi, thank you

7

let me know when your database design has settled down a bit

separate table for multiple language descriptions is correct

:slight_smile:

8

Hi rudi, thank you for the reply. Your absolutly right about the aditional categories query. I should have think about that.

You don’t see the additional clause for latest arrivals as yet since the client is not sure himself how he would like to handle this (adding new arrivals once a week to the database or more frequently per week is the question) so that wil be something I need to adjust later.

There will be a possibility for multple photos per product, since the store has quite a large selection of Collectors Items which the owner would like to promote as good as possible (with multiple photos)

Finally the descriptions, I have to admid your right again :slight_smile: This comes, because the site will come in three languages, and somehow I thought it would be practical to sepperate the descriptions from the main products table. If you say It is not a good idea, than I think I probably reconsider.

Leaves me with the question, I started off with. How do I limit the records per group. I realy can’t think of something

9

first of all, you need to rewrite that section of the code where you do two additional queries inside the cfoutput loop for the categories – use a single join query instead

second, i don’t see anything in your code that handles the “latest arrivals” aspect – the only ORDER BY is on category_id

finally, i’m puzzled why you would have photos and descriptions in separate tables – i could see the need for a separate photos table if you wanted to have multiple photos per product (but then you’re not handling that in the code at present), but i’m really curious why there isn’t just a description column in the main products table

you should probably resolve the above issues before thinking about limiting the number of products displayed for each category