CFWheels – Model Not Returning Correct Results in Loop

The other day I was working on a project for a client and they wanted an import functionality that would allow them to upload an  Excel file and have the system save the data in the database.  The Excel file  contained duplicates and of course I don’t want duplicate records so I would check whether it already exists in the database before I insert it.
Each time I cleared the data and imported the file it would ALWAYS add all data including duplicates. I would convert the file to a query using a function that I got from here by Silverink Consulting since CFScript didn’t have a way to use CFSpreadSheet. Then I would loop through the query and check whether or not the record exists before adding it.   All the logic makes sense and I couldn’t figure out what I did wrong.

Here’s a simplified version of the code:

// convert file data to query
data = ExcelToQuery(params.theFile);

// loop through each record
for(person in data){
// check if person exists
    person = model(“Person”).findOne(where=”name = ‘#trim(person.name)#'”);
if(NOT isObject(person){
// add person
person = model(“Person”).new();
person.name = trim(person.name);
person.save();
}
}

Turns out that CFWheels caches duplicate queries during a lifetime of the request. Since the query is in the loop and in the same request, it cached.

So if the name was “Bob” obviously it would return false initially since it doesn’t exist in the database.

When it encounters “Bob” again you would think it would find it but since the query is cached it still returns false, therefore, the duplicate record is inserted.

The way to get around this is to ensure the query is not cached. The way to do this is add reload=true in the .findOne()

  person = model(“Person”).findOne(where=”name = ‘#trim(person.name)#’, reload=true”);

Now if we import the file it correctly detects the duplicate and does not insert it. Problem SOLVED!

Amazing Date Range Picker

I use Twitter Bootstrap with my project to quickly build visually appealing websites.

When displaying reports I usually use two separate date pickers to define the “from” and “to date ranges.

Today I found out about an amazing date range picker made by Dan Grossman.

Usability wise it’s really awesome. You can customize pre-defined ranges or allow the user to select their own range.

The code is simple. Just like any other date picker:

$('#element').daterangepicker(options, callback);

It also allows you to define a time range along with the date range.

I really recommend that you check it out. You can download it here and check out more usages.