Friday, October 12, 2012

JQuery Validate: Change Option Value

In my current project I have a default set of options that every form gets validated with. In fact, .validate(options) is called for every form.

Today I needed to change one of the options that my form validator was initialized with. Sounds pretty simple, but for the life of me I couldn't find anything like setOption() or .validate("option", {}) like other plugins have.

Turns out that Validate actually makes it too simple. Where other plugins give you non-standardized functions to update the options, Validate gives you the options object directly... but they call it settings.

I can disable the auto onsubmit validation by doing the following:
$("#myForm").validate().settings.onsubmit = false;
This works because .validate() returns the current validator for the form, and the validator neatly exposes the settings collection.

Wednesday, April 25, 2012

Extending QueryOver With "Or"

QueryOver (introduced in NHibernate 3.0) offers type safe, Linq-esque, syntax for writing Nhibernate queries in your DAO. However, writing a multiple column disjunction with anything other than simple operators can easily become ugly and unwieldy.

Tuesday, March 13, 2012

T-SQL: Aggregate a column to a comma delimited list

I commonly encounter situations where it is useful to select a comma delimited list as an aggregate in a grouped query. This is a well known problem and yet each time I run across it I have to look something up to solve it.

The following is a detailed breakdown of the solution from the msdn archive.

The scenario I'm covering here is the need to aggregate all the IDs in a joined table into a single column on the master table. We'll use a table structure like this:
CREATE TABLE Parent (
  Id int NOT NULL identity,
  Name varchar(50) NOT NULL,
  Children varchar(256) NULL,
)
CREATE TABLE Child (
  Id int NOT NULL identity,
  ParentId int NOT NULL,
  Name varchar(50) NOT NULL
)
Create some sample data:
INSERT INTO Parent (Name)
VALUES ('Parent 1'), ('Parent 2'), ('Parent 3')
INSERT INTO Child (ParentId, Name)
VALUES (1, 'Child 1'), (1, 'Child 2'), (1, 'Child 3'), (2, 'Child 4'), (2, 'Child 5'), (3, 'Child 6'), (3, 'Child 7'), (3, 'Child 8'), (3, 'Child 9')
Now we can update the Children column on Parent with this:
;
WITH
t AS (SELECT p1.Id, Children = (
    SELECT (',' + convert(varchar, c2.Name ))
    FROM Parent p2
      JOIN Child c2 ON p2.Id = c2.ParentId
    WHERE p2.Id = p1.Id
    ORDER BY c2.Id
    FOR XML PATH( '' )
  ) + ','
  FROM Parent p1
    JOIN Child c1 ON p1.Id = c1.ParentId
  GROUP BY p1.Id)
UPDATE p
SET Children = t.Children
FROM Parent p
  JOIN t ON t.Id = p.Id
And the results look like:
Id | Name | Children
1 | Parent 1 | ,Child 1,Child 2,Child 3,
2 | Parent 2 | ,Child 4,Child 5,
3 | Parent 3 | ,Child 6,Child 7,Child 8,Child 9,

Thursday, June 9, 2011

ActiveReports: Grouping

We've used Grape City's (formerly Data Dynamics) ActiveReports off and on for many years. I'm not truly a fan since personally I've never found it that intuitive, but for flexibility they're pretty good.

Recently I ran into a quirk I've encountered before but had forgotten. I won't call it a bug since we're using it in a manner that is undocumented and most likely not supported.

Since AR uses (or appears to use) DataBinder.Eval to evaluate the DataField on each object in the DataSource of a report, we have always used collections of domain objects for our report sources. This works well for textboxes, however, when you try to do a standard report grouping (with header and footer) you'll get stuck wondering why the report only seems to recognize one group. Specifically this occurs when you use a property path of depth greater than one.

The following works:
myTextbox.DataField = "Property1.Property2";
myGroupheader.DataField = "Property1";
This does not:
myTextbox.DataField = "Property1.Property2";
myGroupheader.DataField = "Property1.Property2";
The textbox will populate correctly but the report will only discover one group of records.

Friday, May 6, 2011

NHibernate: Group By Case Statement

Today I wanted to speed up some statistics tables which had been tossed into our application dashboard awhile back. The queries were originally written quick and dirty to just pull back all of the records in the table then run some linq counts on them. Terrible of course and there was a nice little comment above the section saying something about "TODO: make this better".

The quickest query I could think of to run these statistics looks something like this:
select 
IsJourneyman,
case when Accepted is null then 1 else 0 end,
Count(*)
from
students
group by
IsJourneyman,
case when Accepted is null then 1 else 0 end
My next thought was "Can I implement this in ICriteria?" Well it turns out the answer is Yes!
var cr = Session.CreateCriteria<Student>();

cr.SetProjection(Projections.ProjectionList()
.Add(Projections.RowCount(), "Count")
.Add(Projections.Group<Student>(s => s.IsJourneyman), "IsJourneyman")
.Add(Projections.GroupProperty(
Projections.SqlProjection("case when Accepted is null then 1 else 0 end",
new[] { "IsApplicant" }, new[] { NHibernateUtil.Boolean })),
"IsApplicant")
);

// transform the results into a strong typed object
cr.SetTransformer(Transformers.AliasToBean(typeof(CountResult)));

return cr.List<CountResult>();
For which I created this simple class. The aliases passed to .Add() are used to match up the properties.
public class CountResult
{
public bool IsJourneyman { get; set; }
public bool IsApplicant { get; set; }
public int Count { get; set; }
}
I tried using the Projections.Conditional, but ran into a NHibernate bug on mixing named and ordered parameters.

I'm sure there are faster ways to accomplish this, but since I wanted to do it in NHibernate without adding a calculated field this will work for me.