Experienced CF Developer needed
ColdFusion, SQL Server 2005, JobsMy job is up for grabs, and it's the best development job I've ever had (and I've had quite a few ;-).
I'm *only* leaving it because I'm moving to Holland (more on that in another post). I have no complaints, but want to make it clear that the job is for people who want to work in a small ".com style" team. The owners are so nice to work for, and it's truly been a pleasure to have the freedom to design the code creatively, use the new "Enterprise" CF 8 features, see my work directly increase revenue, and get things done quickly without corporate red tape and umpteen levels of middle-management. Below is the job description.
"We are looking hard for an experienced ColdFusion Developer opening we have to fill immediately. This would be for the lead programmer for the LandsofAmerica.com Network which is the nation’s largest rural real estate website. We have many cutting-edge applications we are working on and implementing with the latest in ColdFusion 8 in exciting and new ways.
This position needs experience in high traffic websites to manage our 16 million page views across 300 websites including a new partner that has the potential to add another 30% to our traffic numbers in the next 3 months. We have started powering the Farm & Ranch sections on the Houston Chronicle and Texas Monthly as well as new Hearst properties soon. High traffic website management as well as web server/database server optimization experience is a must. Work experience with CF8 and SQL Server 2005 is a big plus.
We are a hard working, small team that gets things done quick and can see results fast. It’s a lot of fun! We are growing at a phenomenal rate with our number of listings at 63,000 and just two years ago they were at 2,500.
If you are interested please send your resume and any work you’ve done in the past two years. A detailed description of the exact work done will be very beneficial in moving this process forward ASAP. If you know of anyone this opportunity might sound interesting to, please forward this on to them.
Great pay, full health coverage, and monthly bonuses. "
send resumes to allen@landsofamerica.com
No comments for this entry. || 
Add Comment
Add Comment
Paginating Records in CF with One SQL Server 2005 call (update 1)
ColdFusion, SQL Server 2005I've been meaning to update folks on the excellent use I've been getting out of this way of paginating records with SQL Server 2005 via CF, and James Allen's comment today gave me an excuse to finally update folks:
- Note that I've been using this method in production at www.LandsofAmerica.com - see some example search results, as well as all our sister sites: www.landsofTexas.com, www.landsofCalifornia.com, etc. Our combined network gets upwards of 15 ColdFusion requests per second, and this dynamic stored procedure has held up extremely well, despite the enormous load and wide variety of possible WHERE clauses in the search.
- And now to answer James's comment, "I've been using your SP extensively around my new site and it's working great. However, I am now tring to get it to work with a table join and I just can't get it to work with a table or alias prefix (in the columns parameter)." - The "sample DAO CFC method" code below should give you what you need to fix the issue you refer to. :)
First, the Stored Procedure SQL again:
USE [propertyControlCenter]
GO
/****** Object: StoredProcedure [dbo].[sproc_pagination] Script Date: 06/09/2008 11:28:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author: Aaron Longnion
-- Create date: 10/18/2007
-- Description: A gereral-use way of dynamically
-- creating SQL to easily paginate the results;
-- also includes a total record count based on
-- the search criteria in the WHERE clause
-- ==================================================
CREATE PROCEDURE [dbo].[sproc_pagination]
-- Add the parameters for the stored procedure here
@SqlColumns VARCHAR(MAX),
@SqlFriendlyColumns VARCHAR(MAX),
@SqlTableClause VARCHAR(MAX),
@StartRow INT,
@EndRow INT,
@SqlWhere VARCHAR(MAX),
@SqlRowNumOrderBy VARCHAR(MAX),
@SqlOuterOrderBy VARCHAR(MAX)
AS
DECLARE @rsSQL NVARCHAR(MAX)
DECLARE @rcSQL NVARCHAR(MAX)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- build pagination SQL, using StartRow and EndRow to determine
-- which results to output
SET @rsSQL = N' WITH tempTable AS ( ' +
N' SELECT ' +
@SqlColumns +
N' , ROW_NUMBER() OVER(ORDER BY ' +
@SqlRowNumOrderBy +
N' ) AS RowNumber ' +
N' FROM ' +
@SqlTableClause
IF @SqlWhere + '' <> ''
BEGIN
SET @rsSQL = @rsSQL +
N' WHERE ' +
@SqlWhere
END
SET @rsSQL = @rsSQL +
N' ) SELECT ' +
@SqlFriendlyColumns +
N' FROM tempTable ' +
N' WHERE RowNumber >= ' +
CAST(@StartRow AS NVARCHAR(32)) +
N' AND RowNumber <= ' +
CAST(@EndRow AS NVARCHAR(32)) +
N' ORDER BY ' +
@SqlOuterOrderBy
-- uncomment PRINT to debug
--PRINT @rsSQL
EXEC sp_executesql @rsSQL
-- build second recordset simple for the count
SET @rcSQL =
N'SELECT COUNT(*) AS CountAll FROM ' +
@SqlTableClause
IF @SqlWhere + '' <> ''
BEGIN
SET @rcSQL = @rcSQL +
N' WHERE ' +
@SqlWhere
END
EXEC sp_executesql @rcSQL
SET NOCOUNT OFF;
END
PRINT 'sproc_pagination created'Second, some sample Gateway CFC method code:
<cffunction name="searchInventory" output="false" access="public" returntype="struct" hint="Returns total count and recordset based on search criteria"> <cfargument name="SearchCrit" required="true" type="struct" hint="Search criteria, usually passed in from the URL scope" /> <cfset var retStruct = StructNew() /> <cfparam name="Arguments.SearchCrit.SiteStateID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.State_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Region_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.County_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Style" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Style_ID" type="string" default="" /> <cfparam name="Arguments.SearchCrit.SqFt" type="string" default="" /> <cfparam name="Arguments.SearchCrit.Price" type="string" default="" /> <cfparam name="Arguments.SearchCrit.AuctionDateStart" type="string" default="#DateFormat(Now(), 'yyyy-mm-dd')#" /> <cfparam name="Arguments.SearchCrit.AuctionDateEnd" type="string" default="" /> <cfparam name="Arguments.SearchCrit.ListingType" type="string" default="" /> <cfparam name="Arguments.SearchCrit.PageNum" type="numeric" default="1" /> <cfparam name="Arguments.SearchCrit.RecordsPerPage" type="numeric" default="15" /> <cfparam name="Arguments.SearchCrit.sortby" type="string" default="sqft" /> <cfparam name="Arguments.SearchCrit.sortbyDirection" type="string" default="DESC" /> <!--- derive start and end rows ---> <cfset Arguments.StartRow = ( (Arguments.SearchCrit.PageNum-1) * Arguments.SearchCrit.RecordsPerPage )+1 /> <cfset Arguments.EndRow = (Arguments.StartRow + Arguments.SearchCrit.RecordsPerPage)-1 /> <cfset retStruct = DAO.searchInventory( ArgumentCollection=Arguments ) /> <cfset retStruct.PagingData = Server.Utilities.Toolbox.getPagingLinks( RecordsPerPage = Arguments.SearchCrit.RecordsPerPage , RecordCount = retStruct.RC , NumberOfLinks = 10 , RecordLimit = 1000000 , QSItemsToStrip = "messageCode,searchIt" ) /> <cfreturn retStruct /> </cffunction>
Third, a sample DAO CFC method. This is the key: see how the "friendly" (no table aliases) names of the columns are in the "local.SqlFriendlyCols" section. Notice how the column names in the "local.SqlCols" clause contain references to table aliases in "local.SqlTableClause". The only caveat for this, which I haven't found a work-around yet, is that you'll get an "ambiguous column" SQL error if you have a JOIN and try to sort by a column that does not have a unique name :
<cffunction name="searchInventory" access="package" output="false" returntype="struct">
<cfset Var local = StructNew()>
<!--- columns --->
<cfsavecontent variable="local.SqlCols">
I.listing_city,
ST.State_name,
ST.State_shortCode,
S.listing_type,
I.listing_price,
I.listing_photo,
I.listing_id,
I.listing_state_id,
I.listing_lot_size,
I.listing_county_id,
C.county_name
</cfsavecontent>
<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
<cfsavecontent variable="local.SqlFriendlyCols">
listing_city,
State_name,
State_shortCode,
listing_type,
listing_price,
listing_photo,
listing_id,
listing_state_id,
listing_lot_size,
listing_county_id,
county_name
</cfsavecontent>
<!--- tables --->
<cfsavecontent variable="local.SqlTableClause">
listing_(nolock) I
INNER JOIN
listing_types(NOLOCK) S
ON S.listing_type_ID = I.listing_type_ID
INNER JOIN
counties(nolock) C
ON C.County_ID = I.listing_County_ID
INNER JOIN
states(NOLOCK) ST
ON ST.State_ID = I.listing_State_ID
</cfsavecontent>
<!--- conditions --->
<cfoutput>
<cfsavecontent variable="local.SqlWhere">
I.listing_Active = 1
AND I.listing_Acct_Active = 1
AND I.listing_Status = 0
<!--- dynamic info --->
<cfif Len(Arguments.SearchCrit.siteStateID)>
AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
</cfif>
<cfif ListLen(Arguments.SearchCrit.State_ID)>
AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.Region_ID)>
AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.County_ID)>
AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_type)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.SqFt)>
AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_Price)>
AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
</cfif>
</cfsavecontent>
</cfoutput>
<!--- order --->
<cfoutput>
<cfsavecontent variable="local.SqlRowNumOrderBy">
#Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
</cfsavecontent>
<!--- used for order column that has an alias prefix --->
<cfsavecontent variable="local.SqlOuterOrderBy">
#ListLast(local.SqlRowNumOrderBy, ".")#
</cfsavecontent>
</cfoutput>
<!--- dynamically get the data based on the SQL built above --->
<cfset local.returnStruct = Server.DAO.Common.getPaginatedData( SqlCols = local.SqlCols,
SqlFriendlyCols = local.SqlFriendlyCols,
SqlTableClause = local.SqlTableClause,
StartRow = Arguments.StartRow,
EndRow = Arguments.EndRow,
SqlWhere = local.SqlWhere,
SqlRowNumOrderBy = local.SqlRowNumOrderBy,
SqlOuterOrderBy = local.SqlOuterOrderBy ) />
<cfreturn local.returnStruct />
</cffunction>
And finally, the getPaginatedData() method:
<cffunction name="getPaginatedData" access="public" output="false" returntype="struct" hint="generic way to get pagination data from the dB">
<cfargument name="SqlCols"
type="string"
required="true"
hint="list of table columns ('col1, col2, col3', 'a.col1, b.col2, c.col3', or 'a.col1 AS acol, b.col2 AS bcol, (SELECT c.col3 FROM c_table) AS subCol3' are all acceptable" />
<cfargument name="SqlFriendlyCols"
type="string"
required="true"
hint="list of table columns ('col1, col2, col3' is acceptable" />
<cfargument name="SqlTableClause"
type="string"
required="true"
hint="list of table columns (most JOINs and sub-SELECTs are acceptable" />
<cfargument name="StartRow"
type="numeric"
required="true"
hint="beginning row number to display" />
<cfargument name="EndRow"
type="string"
required="true"
hint="last row number to display " />
<cfargument name="SqlWhere"
type="string"
required="true"
hint="WHERE clause filters" />
<cfargument name="SqlRowNumOrderBy"
type="string"
required="true"
hint="usually the PK of the primary table" />
<cfargument name="SqlOuterOrderBy"
type="string"
required="true"
hint="chose from the columns from SqlFriendlyCols" />
<cfset var returnStruct = StructNew() />
<cfstoredproc procedure="sproc_pagination" datasource="#Variables.DSNs.primary#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlColumns" value="#Trim(Arguments.SqlCols)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlFriendlyColumns" value="#Trim(Arguments.SqlFriendlyCols)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlTableClause" value="#Trim(Arguments.SqlTableClause)#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="SqlStartRow" value="#Arguments.StartRow#">
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="EndRow" value="#Arguments.EndRow#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlWhere" value="#Trim(Arguments.SqlWhere)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlRowNumOrderBy" value="#Trim(Arguments.SqlRowNumOrderBy)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="SqlOuterOrderBy" value="#Trim(Arguments.SqlOuterOrderBy)#">
<!--- return data + count --->
<cfprocresult name="rs" resultset="1">
<cfprocresult name="recordcount" resultset="2">
</cfstoredproc>
<cfset returnStruct.rs = rs />
<cfset returnStruct.rc = recordcount.countAll />
<cfreturn returnStruct />
</cffunction>
Let me know what you think.
<cffunction name="searchInventory" access="package" output="false" returntype="struct">
<cfset Var local = StructNew()>
<!--- columns --->
<cfsavecontent variable="local.SqlCols">
I.listing_city,
ST.State_name,
ST.State_shortCode,
S.listing_type,
I.listing_price,
I.listing_photo,
I.listing_id,
I.listing_state_id,
I.listing_lot_size,
I.listing_county_id,
C.county_name
</cfsavecontent>
<!--- friendly cols (if no JOINs/aliased table names, inherit from SqlCols above) --->
<cfsavecontent variable="local.SqlFriendlyCols">
listing_city,
State_name,
State_shortCode,
listing_type,
listing_price,
listing_photo,
listing_id,
listing_state_id,
listing_lot_size,
listing_county_id,
county_name
</cfsavecontent>
<!--- tables --->
<cfsavecontent variable="local.SqlTableClause">
listing_(nolock) I
INNER JOIN
listing_types(NOLOCK) S
ON S.listing_type_ID = I.listing_type_ID
INNER JOIN
counties(nolock) C
ON C.County_ID = I.listing_County_ID
INNER JOIN
states(NOLOCK) ST
ON ST.State_ID = I.listing_State_ID
</cfsavecontent>
<!--- conditions --->
<cfoutput>
<cfsavecontent variable="local.SqlWhere">
I.listing_Active = 1
AND I.listing_Acct_Active = 1
AND I.listing_Status = 0
<!--- dynamic info --->
<cfif Len(Arguments.SearchCrit.siteStateID)>
AND I.listing_state_id = #Arguments.SearchCrit.siteStateID#
</cfif>
<cfif ListLen(Arguments.SearchCrit.State_ID)>
AND I.listing_state_id IN(#Arguments.SearchCrit.State_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.Region_ID)>
AND C.listing_Region_id IN(#Arguments.SearchCrit.Region_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.County_ID)>
AND I.listing_County_id IN(#Arguments.SearchCrit.County_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_type)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type#)
<cfelseif ListLen(Arguments.SearchCrit.listing_type_ID)>
AND S.listing_type_id IN(#Arguments.SearchCrit.listing_type_ID#)
</cfif>
<cfif ListLen(Arguments.SearchCrit.SqFt)>
AND I.listing_size >= #Val(ListFirst(Arguments.SearchCrit.listing_size))#
AND I.listing_lot_size <= #Val(ListLast(Arguments.SearchCrit.listing_size))#
</cfif>
<cfif ListLen(Arguments.SearchCrit.listing_Price)>
AND I.listing_Price >= #Val(ListFirst(Arguments.SearchCrit.listing_Price))#
AND I.listing_Price <= #Val(ListLast(Arguments.SearchCrit.listing_Price))#
</cfif>
</cfsavecontent>
</cfoutput>
<!--- order --->
<cfoutput>
<cfsavecontent variable="local.SqlRowNumOrderBy">
#Arguments.SearchCrit.sortby# #Arguments.SearchCrit.sortbyDirection#
</cfsavecontent>
<!--- used for order column that has an alias prefix --->
<cfsavecontent variable="local.SqlOuterOrderBy">
#ListLast(local.SqlRowNumOrderBy, ".")#
</cfsavecontent>
</cfoutput>
<!--- dynamically get the data based on the SQL built above --->
<cfset local.returnStruct = Server.DAO.Common.getPaginatedData( SqlCols = local.SqlCols,
SqlFriendlyCols = local.SqlFriendlyCols,
SqlTableClause = local.SqlTableClause,
StartRow = Arguments.StartRow,
EndRow = Arguments.EndRow,
SqlWhere = local.SqlWhere,
SqlRowNumOrderBy = local.SqlRowNumOrderBy,
SqlOuterOrderBy = local.SqlOuterOrderBy ) />
<cfreturn local.returnStruct />
</cffunction>
Loading....