Conditions

Explanation

(Skip to the next section if you just want to see some examples)

Conditions, the logic statements such as those used in an SQL where clause, are a complex matter. Sea Quail's goal is to represent complex logic statements using OO conventions while allowing their construction in a concise and readable form. I will try to lay out here a nice explanation of Sea Quail's (possibly) unintuitive approach.

Take the following conditional statement:

    NOT(ManufacturerCountry = 'China')
    OR (HatType = 'Sombrero' AND BrimSize >= 80/*inches*/) 
    OR (HatType = 'Baseball' AND Resizeable = 1
         AND (Color = 'Brown' OR Color = 'Gray'))

Supposing this is in the where clause of an SQL statement selecting hats, it would mean-- show me all hat inventory not made in china, but make exceptions for 80" sombreros and resizeable, brown or gray baseball hats. (If I could find an eighty inch sombrero, I wouldn't care where it was made.)

When deconstructing this, what I believe are the components (from most to least granular):
  1. Operands, such as HatType, 'Sombrero', BrimSize, 80; Relational Operators =, >, et cetera; Connective Operators AND, OR; Value Inversion operator NOT
  2. Conditions consisting of two operands and a relational operator (HatType = 'Sombrero'), resulting in true or false when evaluated.
  3. Conditions consisting of other conditions (the parenthetical groupings). Their value is determined by evaluating their child conditions.

The rules of the logic statement:
  • Each condition, by virtue of one connective, may be followed by one other condition. I have decided that each connected condition (as well as the connective) should be said to belong to its preceding condition. I do not consider two connected conditions to be siblings. However, I'd like to discuss the idea further. Leave a comment if you have an opinion on this, please.
  • Each condition may have its evaluated value inverted (true becomes false).

Given these components and rules, I propose the following as the model for conditions.

Base Condition
Has A Value Inversion directive
Has A Connective Logic Operator
Has A Base Condition

Condition Group
Is A Base Condition
Has A Inner Base Condition

Condition
Is A Base Condition
Has A Left Operand
Has A Right Operand
Has A Relational Operator

Examples

One condition

To get things rolling, here's quick sample of a parameterized select *, with one simple condition in the where clause.

string varHatType = Adapter.Instance.CreateVariable("HatType");
SQSelect select = new SQSelect()
{
    PrimaryTable = new SQAliasableObject("Hat"),
    Condition = new SQCondition("HatType", RelationOperators.Equal, varHatType)
};
select.Columns.Add("*");
select.Parameters.Add(varHatType, "Sombrero");

/* The SQL for this in SQL Server would be:
SELECT
    *
FROM Hat
WHERE (HatType = @HatType)
*/

From the example, you can see that the SQCondition class provides a constructor accepting the first, the relational operator, and the second operand.

Notice that to add a condition to the SQSelect a single condition property is set. Query objects do not have a list of conditions as might be expected. Instead, support for multiple query conditions is provided by virtue of the condition model as discussed above. To create a query(or join) with multiple conditions, a subsequent condition is set as a property on the query's root condition, and to that second condition, another condition may be trailed. Conditions are chained one to the next, to the next.

Multiple conditions

Adding another condition:

string varHatType = Adapter.Instance.CreateVariable("HatType");
string varBrimSize = Adapter.Instance.CreateVariable("BrimSize");
SQSelect select = new SQSelect()
{
    PrimaryTable = new SQAliasableObject("Hat"),
    Condition = new SQCondition("HatType", RelationOperators.Equal, varHatType)
        .And("BrimSize", RelationOperators.GreaterThanOrEqual, varBrimSize)
};
select.Columns.Add("*");
select.Parameters.Add(varHatType, "Sombrero");
select.Parameters.Add(varBrimSize, 80);

/* The SQL for this in SQL Server would be:
SELECT
    *
FROM Hat
WHERE (HatType = @HatType) AND (BrimSize = @BrimSize)
*/

This demonstrates the overload for the "And" method in which the passed parameters are used to create a new SQCondition. "And" and "Or" methods are a part of every condition via the SQCondtionBase. The And and Or methods append the passed/created condition to the end of the chain of conditions, set the connective operator of the last condition in the chain, and return the first condition in the chain of conditions. Skip down to Nested Conditions if you don't care for more explanation.

It will help in understanding what happens when the And method is called, by looking at a portion of the class definition for the SQConditionBase

public class SQConditionBase
{
    /// <summary>
    /// The And or Or logic operator
    /// </summary>
    public LogicOperators Connective { get; set; }
    /// <summary>
    /// The subsequent condition
    /// </summary>
    public SQConditionBase NextCondition { get; set; }

    public SQConditionBase And(string operandA, RelationOperators op, string operandB)
    {
        AppendCondition(new SQCondition(operandA, op, operandB), LogicOperators.AND);
        // return the condition from which this And was called, so that And can be called
        // from it again, only the second time, as you can see, the new condition will be
        // set as the subsequent condition to the condition first new condition. um, how
        // about like this: 
        // conditions: (A) is the current condition on which And is called; (B) is the condition
        // created the first time And is called; (C) is the condition that is created the second
        // time. 
        // A.And(B) = B becomes the next condition to A. A is returned
        // A.And(C) = C becomes the next condition to B. A is returned
        return this;
    }

    private void AppendCondition(SQConditionBase condition, LogicOperators connective)
    {
        // If a subsequent condition has not been set yet, then the passed condition will 
        // become this condition's subsequent condition and the passed connective will
        // be adopted
        if (NextCondition == null)
        {
            NextCondition = condition;
            Connective = connective;
        }
        // If this condition already has a condition following it, then descend into that 
        // condition and try to append the passed condition to it
        else
        {
            NextCondition.AppendCondition(condition, connective);
        }
    }
}

I'm not sure if this method of connecting conditions is the best way; I chose to do it this way mainly for the sake of readability and brevity. I'm open to suggestions.

That covers condition chaining or "multiple conditions." However, simply chaining conditions will not allow for a where clause as complex as the one in the explanation at the beginning. To add more dimensions to a condition statement, a condition would need to be nested within another.

Nested Conditions

Here's how the entire condition from the explanation might be constructed for Sea Quail.

    NOT(ManufacturerCountry = 'China')
    OR (HatType = 'Sombrero' AND BrimSize >= 80/*inches*/) 
    OR (HatType = 'Baseball' AND Resizeable = 1
         AND (Color = 'Brown' OR Color = 'Gray'))
// Create variables for the parameters
string varCountry = Adapter.Instance.CreateVariable("Country");
string varHatType1 = Adapter.Instance.CreateVariable("HatType1");
string varBrimSize = Adapter.Instance.CreateVariable("BrimSize");
string varHatType2 = Adapter.Instance.CreateVariable("HatType2");
string varResizeable = Adapter.Instance.CreateVariable("Resizeable");
string varColor1 = Adapter.Instance.CreateVariable("Color1");
string varColor2 = Adapter.Instance.CreateVariable("Color2");

// Set up the select statement
SQSelect select = new SQSelect()
{
    PrimaryTable = new SQAliasableObject("Hat"),
    // NOT(ManufacturerCountry = 'China')
    Condition = new SQCondition("ManufacturerCountry", RelationOperators.Equal, varCountry, true)
        // OR (HatType = 'Sombrero'
        .Or(new SQConditionGroup(new SQCondition("HatType", RelationOperators.Equal, varHatType1)
             // AND BrimSize >= 80/*inches*/) 
            .And("BrimSize", RelationOperators.GreaterThanOrEqual, varBrimSize)))
        // OR (HatType = 'Baseball' 
        .Or(new SQConditionGroup(new SQCondition("HatType", RelationOperators.Equal, varHatType2)
            // AND Resizeable = 1
            .And("Resizeable", RelationOperators.Equal, varResizeable)
            // AND (Color = 'Brown'
            .And(new SQConditionGroup(new SQCondition("Color", RelationOperators.Equal, varColor1)
                //  OR Color = 'Gray'))
                .Or("Color", RelationOperators.Equal, varColor1))))
};
select.Columns.Add("*");

// Adding variables and their values to the parameters
select.Parameters.Add(varCountry, "China");
select.Parameters.Add(varHatType1, "Sombrero");
select.Parameters.Add(varBrimSize, 80);
select.Parameters.Add(varHatType2, "Baseball");
select.Parameters.Add(varResizeable, true);
select.Parameters.Add(varColor1, "Brown");
select.Parameters.Add(varColor2, "Gray");

/* The SQL for this in SQL Server would be:
SELECT
    *
FROM Hat
WHERE 
    NOT(ManufacturerCountry = @Country)
    OR (HatType = @HatType1 
        AND BrimSize >= @BrimSize) 
    OR (HatType = @HatType2 
        AND Resizeable = @Resizeable
        AND (Color = @Color1 
            OR Color = @Color2))
*/

In the example, SQConditionGroup's constructor sets the passed condition to the group's "InnerCondition" property. Another thing to note is that passing "true" in the fourth parameter of the constructor for the ManufacturerCountry condition is what causes the "NOT" to show up in the result. True, in this case, means "yes, invert the meaning of the condition."

Last edited Aug 15, 2010 at 2:46 AM by sledgebox, version 42

Comments

No comments yet.