At some point in the not too distant future, the intarweb’s magazine consumers will be perusing a new magazines.com. I’ve got the responsibility of building it. One of the requirements is to allow our Merchandising and Marketing folk to get their jobs done without too much fuss. The system they use will consume sets of products. They might want to build a set of magazines in a certain category, under a certain price. What kind of tool am I talking about?

A Query Builder.

I can hear your eyes rolling already. We’ve all written them. But I’m going to share an extra-cool method for doing so.

Here’s the list of ingredients you’ll need.

• Your existing DBIC application.
• Experience with custom resultsets.
• An understanding of chained resultset usage.
• Moose
• MooseX::Method

Now that you’ve collected your ingredients, let’s prepare them.

First, note that we aren’t allowing our end users to build literal queries in this recipe. We are going to allow them to chain calls to our resultset. This is a much better option, in my opinion. A lot of the complexity can be removed by simplifying some heinous or dangerous SQL into a simple resultset method. With the help of Moose we will create a custom resultset that allows us to introspect the methods available for building and show them to the user automatically.

Now, we’ll be working on a Product example. Our end user needs to build a query that finds all the products in a price range, of a certain type and in a certain category.

Since you’re already versed in custom resultsets I’ll just show you the finished product:

package Our::ResultSet;
use Moose;
use MooseX::Method;
use MooseX::Util::TypeConstraints;
 
extends 'DBIx::Class::ResultSet';
 
subtype 'Currency'
	=> as 'Object'
	=> where { $_->isa('Math::Currency') };
 
coerce 'Currency'
	=> from 'Num'
		=> via { Math::Currency->new($_) };
 
method price => named (
	operator => { isa => 'Comparator', required => 1 },
	amount	=> { isa => 'Currency', required => 1, coerce => 1 }
) => sub {
	my ($self, $args) = @_;
 
	if($args->{'operator'} eq '=') {
		return $self->search({ price => $args->{'amount'}->as_float() });
	}
 
	return $self->search({
		price => {
			$args->{'operator'} => $args->{'amount'}->as_float()
		}
	});
};

That’s a lot of code, but the concept is very simple: Create your resultset’s methods with MooseX::Method and then you can do the following magic:

my $rs = $schema->resultset('Product');
my $map = $rs->meta->get_method_map();
foreach my $method (keys(%{ $map })) {
	# See MooseX::Method docs for what to do with this.
	my $signature = $method->signature();
	# Profit!
}

Having the method’s ’signature’ allows you to get a list of the parameters that the method expects, as well as the types and ‘required’ status. That sounds like the recipe for some go-getting developer to construct a query building tool!

One warning. MooseX::Method currently lacks a permanent API for acquiring this information. The current API is to call export on the signature. You can Dumper the resulting hashref and see pretty easily how to get what you need. Keep an eye on The CPAN or in irc.perl.org’s #moose for updates.

So to use this recipe, you’ll have to construct your own mechanism for taking the above information and generating a UI for your application. I got my Catalyst based query-builder working this afternoon.

If you have any questions feel free to drop into irc.perl.org and shoot a message to gphat. You can find me in #moose.

Technorati : View blog reactions
Posted at : February 28th, 2008 - 7:50 pm
Filed under : Catalyst, Code, Perl
Navigate : Previous post / Next post