CODEWITHSUNDEEP
coldfusion
Brian Fleishman
Brian Fleishman

Reputation: 1257

Loop through a ColdFusion query and sorting results

I have two tables: Users (2 columns): ID, DisplayName, Active

Ticket_Followups (4 colums): id, requested_by, requested_date, ticket_id

I am tryiwng to group all the similar records in the ticket_followup table, first by recordcount and then by displayName.

Here is what I have so far:

<cfquery name="active_users" datasource="#datasource#">
    select * from users
    where active='1'
</cfquery>
  
<cfloop query="active_users">
    <cfquery name="get_followups" datasource="#datasource#">
        select date_of_followup_request, requested_by, ticket_id
        from ticket_followup
        where requested_by = '#active_users.displayName#'
    </cfquery>
    
    <cfoutput>
        
        <tr>
            <td>#active_users.displayName#</td>
            <td>#get_followups.recordcount#</td>
        </tr>
      
    </cfoutput>
</cfloop>

I am able to successfully show the output for the total records by user, but there is no order to the output. I would like to group it so that it shows the DisplayName with the highest recordcount first, descending in order.

How can I do that?

Upvotes: 0

Views: 355

Answers (1)

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

  1. This is a SQL issue, CF is just displaying data after the data is gathered.
  2. You need to do this in one query.
  3. You need to associate the ticket follow ups by user ID, not by name (Name could change, but not the ID).
  4. There's a table of tickets I assume, but we'll stick to your two tables.

First, the tables:

Users
----------
id 
DisplayName
Active

Ticket_Followups
----------
id 
requested_by_id (Users.id)
requested_date
ticket_id

You can technically join by name, but it's a much slower query and I've no idea how much data you have.

This query joins the two tables and gives you a count of ticket follow ups by user. You can add an ORDER BY statement before the GROUP BY depending on your needs.

SELECT
    a.DisplayName
    , count(*) AS requested_count
FROM 
    Users AS a 
INNER JOIN 
    Ticket_Followups b ON b.requested_by_id = a.id 
WHERE 
    a.active = 1
GROUP BY 
    a.id

If you don't do this in one query, then for every user that has an active ticket, you're making another query.

  • 10 users, 11 queries
  • 20 users, 21 queries
  • etc.

Updated 2022-02-15

Query using DisplayName with an ORDER BY clause. This should make it clearer that you're counting the tickets per user and not the number of users.

SELECT
    a.DisplayName
    , count(a.*) AS ticket_count
FROM 
    Ticket_Followups AS a 
INNER JOIN 
    Users AS b ON b.DisplayName = a.DisplayName
WHERE 
    a.active = 1
ORDER BY 
    a.DisplayName DESC
GROUP BY 
    a.DisplayName

Output:

<cfoutput query="queryName">
    <li>#queryName.DisplayName# - #queryName.ticket_count#</li>
</cfoutput>

Upvotes: 2

Related Questions