SQL::Functional Cookbook: ANDs and ORs

2017-03-27

It seems like it should be easy, but I was always disappointed with how other solutions handle arbitrarily nested ANDs and ORs. Most SQL creation libraries seem to start by adding support for a list of AND statements. At some point, the authors realize they need ORs, so they slap that in. Much later, they realize they need to mix ANDs and ORs, and then all sorts of convolutions get written.

With SQL::Functional‘s approach, nesting ANDs and ORs together is as natural as straight SQL. First, we’ll back up a few steps and demonstrate the ANDs:

    my ($sql, @sql_params) = SELECT [qw{ bar baz }],
        FROM( 'foo' ),
        WHERE AND(
            match( 'bar', '=', 1 ),
            match( 'baz', '=', 2 ),
        );

Notice that unlike SQL, the AND is a prefix rather than infix. This might take some getting used to, but it does mean you can pass it an arbitrary number of statements:

    AND(
        match( 'foo', '=', 1 ),
        match( 'bar', '=', 2 ),
        match( 'baz', '=', 1 ),
        match( 'qux', '=', 8 ),
        ...
    )

In the final SQL, all of these will be joined together with AND. The OR statement works the same way:

    OR(
        match( 'foo', '=', 1 ),
        match( 'bar', '=', 2 ),
        match( 'baz', '=', 1 ),
        match( 'qux', '=', 8 ),
        ...
    )

If we need to mix the two together, we just do that:

    AND(
        OR(
            match( 'foo', '=', 1 ),
            match( 'bar', '=', 2 ),
        ),
        OR(
            match( 'baz', '=', 1 ),
            AND(
                match( 'qux', '=', 8 ),
                match( 'quux', '=', 10 ),
            ),
        ),
    )

Thus, the nesting falls naturally out of the system, just like it should be.