#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    16
    Rep Power
    0

    CF Nested Loop Problem


    Hello forum I am having a little problem with nested query loops that I figured I would turn to the forum to ask for help on. I have been racking my brain trying to figure this one out and drawing a blank. So the situation is I have two queries one for an outer loop and the other for an inner loop. The outer loop gets information about a project or job that has been inserted into the Database previously by the owner and the other get information about the user that has placed a bid on the same project or job. When everything works like it supposed to there should be a table header row with the project details and beneath the header should be a row with people who placed a bid on that particular project or job.
    The outer loop works fine the inner loop is where I am having the issue. Its basically repeating the same row under each column in the second loop which makes sense as to why it would do that but not getting the results as intended. Below is the Html I am using:

    Code:
    <cfif gJobsCreated.recordCount>
    	    <table id="mytable" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">	
    		 <cfoutput query="gJobsCreated" group="jobid">   
    	      <tr>
    	        <th scope="col" abbr="Configurations" class="startBox">#title#</th>
    	        <th scope="col" abbr="Dual 2GHz">#Type#</th>
    	        <th scope="col" abbr="Dual 2GHz">#DateFormat(StartDate,'mm/dd/yyy')#</th>
    	        <th scope="col" abbr="Dual 2GHz">#DateFormat(endDate,'mm/dd/yyy')#</th>
    	        <th scope="col" abbr="Dual 2GHz"><cfif Budget NEQ ''>#DollarFormat(budget)#<cfelseIf Hourly NEQ ''>#DollarFormat(hourly)#/hr<cfelse>#dollarFormat(Fixed)#</cfif></th>
    	        <th scope="col" abbr="Dual 2GHz"><a href = "?app=job&view=details&projectID=#JobID#">View Job</a></th>
    	        <th scope="col" abbr="Dual 2GHz">Award Project</th>
    	      </tr>
    			<cfif guserBids.recordCount>
    			 <cfloop query="guserBids"> 
    		        <tr>
    		            <th scope="row" class="spec"></th>
    		            <td>#dollarformat(price)#</td>
    		            <td ></td>
    		            <td >#iif(Session.subscript EQ 'Free',DE('#Left(email, 5)#...'),de('#email#'))#</td>
    		            <td >
    			          <cfif Round(rating)is 0><img src="#application.path.img#/star-zero.png">
                            <cfelseif Round(rating)is 1 ><img src="#application.path.img#/starrating1.png">
                            <cfelseif Round(rating)is 2 ><img src="#application.path.img#/starrating2.png">
                            <cfelseif Round(rating)is 3 ><img src="#application.path.img#/starrating3.png">        
                            <cfelseif Round(rating)is 4 ><img src="#application.path.img#/starrating4.png"> 
                            <cfelseif Round(rating)is 5 ><img src="#application.path.img#/starrating5.png">
                          </cfif>
    					</td>
    		            <td ><a href = "javascript:void(0);">View Profile</a</td>
    		            <td ><a href = "##" title = "Click here to award this project" onclick = "return confirm('Are you sure you want to award this project to this user?')">Award</a></td>
    		        </tr>
    		    </cfloop>
    		  <cfelse>
    		        <table id="mytable" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">
    			        <tr>
    			           <th scope="row" align="center" style="color:red" class="empty">NO RECORDS AVAILABLE!</th>
    			        </tr>
                    </table>
    		   </cfif>
    	     </cfoutput>
    	    </table>
    </cfif>
    Am I going about this the wrong way???

    here are the queries as well:

    Jobs created:

    Code:
    SELECT     j.JobID, j.City, j.stateID, j.zipcode, j.countryID, j.dateCreated, j.Jobdescription, j.certificationNeeded, j.skillNeeded, j.languagePref, j.experienceNeeded, j.type, j.budget, 
                          j.hourly, j.fixed, j.startDate, j.endDate, a.FullTime, a.partTime, a.AsNeeded, a.WeekendsOnly, a.Contract, jc.title, js.name, s.stateName, c.countryName
    					FROM         tbl_job AS j INNER JOIN
                          tbl_availability AS a ON j.availabilityID = a.availabilityID INNER JOIN
                          tbl_job_category AS jc ON j.categoryID = jc.categoryID INNER JOIN
                          tbl_job_status AS js ON j.statusID = js.statusID INNER JOIN
                          tbl_state AS s ON j.stateID = s.stateID INNER JOIN
                          tbl_country AS c ON j.countryID = c.countryID
    					WHERE     j.userID = ?
    Bids Created:

    Code:
    SELECT     jb.bidID, jb.price, jb.description, jb.dateCreated, jb.dateUpdated, jb.isAccepted, jb.isActive, jb.isArchived, up.firstName, up.lastName, 
                          up.firstName + '  ' + up.lastName AS FullName, jb.userID, up.address1, up.address2, up.phone1, up.phone2, up.city, up.zip, up.stateID, up.countryID, up.license, 
                          up.UrlFacebook, up.profileimage, up.ServiceDesc, up.views, j.userID AS ProjectOwnerID, up.bestContact, up.email, up.title, j.jobID, j.isActive AS JobIsActive, 
                          j.isComplete, COALESCE (B.TotalVotes, 0) AS Votes, CAST(COALESCE (B.totalRate, 0) AS Real) AS Rating, ISNULL(up.views, 0) AS Views, COALESCE (p.totalCost, 0) 
                          AS Total
                FROM      tbl_job_bid AS jb INNER JOIN
                          tbl_user_profile AS up ON jb.userID = up.userID INNER JOIN
                          tbl_state AS s ON up.stateID = s.stateID INNER JOIN
                          tbl_country AS c ON up.countryID = c.countryID INNER JOIN
                          tbl_job AS j ON jb.jobID = j.jobID LEFT OUTER JOIN
                              (SELECT     feedbackID, jobID, profileID, SUM(total_votes) AS TotalVotes, SUM(professionalism + performance + completion + timeliness + overall) 
                                                       / CONVERT(decimal(8, 4), COUNT(total_votes)) / 5 AS totalRate
                                FROM          tbl_user_rate AS UR
                                GROUP BY jobID, feedbackID, profileID, total_votes) AS B ON B.jobID = jb.jobID LEFT OUTER JOIN
                              (SELECT     b.profileID, SUM(a.price) AS totalCost
                                FROM          tbl_job_invoice_charge AS a LEFT OUTER JOIN
                                                       tbl_job_invoice AS b ON a.invoiceID = b.invoiceiD
                                GROUP BY b.profileID, a.price) AS p ON p.profileID = up.profileID
                WHERE   (j.userID = ?)
                AND (j.isActive = ?)
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,240
    Rep Power
    967
    I would need to see an example of the actual query results and an example of what the table actually looks like when it is generated.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    16
    Rep Power
    0
    Originally Posted by kiteless
    I would need to see an example of the actual query results and an example of what the table actually looks like when it is generated.
    Well the results set shows the correct header column in each project but when the inner loop Loops it repeats its seld on each iteration of the outer query, so if the outer query shows three iterations of the project well the inner loop iterates 3 times as well in each row of the outer query.

    Here is an example of the rows returned.
    ________________________________________________
    102456 |Plummer| 02/15/2004 |Active|
    ________________________________________________
    Bidid 12 | Job | 10/25/2005 | Yes |
    ________________________________________________
    Bidid 11 | nJob | 10/18/2011 | No |

    if there were 3 projects created lets say the first project was 102456 and the next project would be 102457 the rows beneath the header row would show up 3 times based on the iteration of the outer query.

    It essence what I am trying to do is Grab all the jobs the user created from the DB and display it with all the users that placed a bid on that particular job beneath the job display.In this case more than one user can bid on the job so I am having trouble coding that out and displaying it in a table considering the inner loop.

    I hope that explains more.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,240
    Rep Power
    967
    Because you're using cfoutput group=, it is expecting you to have a nested cfoutput within the outer cfoutput. You're using two separate queries, so that isn't going to work. You can either combine them so all the data is returned in one query (ordered by jobid), you try using DISTINCT on your outer query so you only get one of each job, and remove the group="jobid".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    16
    Rep Power
    0
    Originally Posted by kiteless
    Because you're using cfoutput group=, it is expecting you to have a nested cfoutput within the outer cfoutput. You're using two separate queries, so that isn't going to work. You can either combine them so all the data is returned in one query (ordered by jobid), you try using DISTINCT on your outer query so you only get one of each job, and remove the group="jobid".
    Combining the Two queries sound like the best alternative but how would I do that given the queries are so large. Also how would I get the info I am looking for based on the query presented. Would a Store proc suffice.. IDK drawing a blank as to how I go about combining these queries....
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,240
    Rep Power
    967
    Afraid I can't offer much on the combined SQL since I have no understanding of the actual data. Just like any complex SQL, you'd have to start with a basic query and slowly build it up, testing at each step.

    The other, less ideal option would be to query for the jobs, then loop over each job and do another query for the specific data for that job. It will be less efficient, but as long as the number of records isn't huge, it should probably work. If you're not comfortable with merging the SQL into one query that may be your best bet.

IMN logo majestic logo threadwatch logo seochat tools logo