DBIC::API, Extjs EditorGridPanel, and mapping data from multiple database tables

I’ve recently started using the DBIC::API plugin for catalyst. It does so much right out of the box that I had a basic RESTful interface up and running in a matter of minutes. I also use extjs almost exclusively for creating my front-ends at work. The two are quite a good fit. In fact, all you need for basic CRUD is to create an extjs EditorGridPanl, give its store the restful:true config option, and point its HttpProxy at the DBIC::API URL.

Everything works great in the standard situation where the extjs grid has a one to one mapping with a database table. However, I often find myself wanting data from related tables to be included in my grids. Just populating the grid isn’t a problem. My first attempt looked something like this:

store:new Ext.data.Store({
	proxy:new Ext.data.HttpProxy({
		url:'/rest/person?prefetch=["address"]',
		restful:true,				
		reader:new Ext.data.JsonReader({
    			totalProperty:'totalcount',
    			successProperty:'success',
    			idProperty:'person_id',
    			root:'data',
    			messageProperty:'message'  // <-- New "messageProperty" meta-data
		},[
			{name:'person_id',  type:'int'},
		   	{name:'first_name', type:'int'},
		   	{name:'last_name',  type:'int'},
		   	{name:'address'}    // Object
		])
	)}
)}

The HttpProxy calls the URL /rest/person?prefetch=[“address”]. This tells DBIC::API to return a list of people in the database with each person including the related address information. After the call the address information is available in the grids store as an object. So far so good.

The problem arises when you add a writer to you store so you can create and update data in the grid. DBIC::API doesn’t prefetch when doing a POST or PUT operation. That means, after an update or create, related rows aren’t returned causing the grids to lose any of that related information we’re relying on. To solve the problem just override the following functions in your class that extends Catalyst::Controller::DBIC::API::REST.

package MyApp::BaseController::DBIC::API::REST;
use Moose;
BEGIN {extends 'Catalyst::Controller::DBIC::API::REST'; }

sub update_or_create {
	my ($self, $c) = @_;
	
	$self->next::method($c);
	
	# Get the newly created/updated object's primary key
	# Here we assume that the object has just 1 primary key.
	# This is possibly a limitation of DBIC::API anyway.
	my $pk = [$c->req->current_result_set->result_source->primary_columns]->[0];

	# Get the objects from the request context then clear the request context	
	my $objects = [$c->req->all_objects];
	$c->req->clear_objects();
	
	# Lookup each created/updated object using the passed in search_attributes.
	# This could cause up to n extra mysql calls where n is the number of created/updated
	# rows.
	$c->req->add_object([$self->object_lookup($c, $_->$pk), {}]) for map { $_->[0] } @$objects;
}

sub object_lookup
{
    my ($self, $c, $id) = @_;

    die 'No valid ID provided for look up' unless defined $id and length $id;
    
    # Make sure that objects use the passed in search attributes.
    # Most importantly the prefetch attribute.
    my $object = $c->req->current_result_set->find($id,
       	$c->req->search_attributes
    );
        
    die "No object found for id '$id'" unless defined $object;
    
    return $object;
}

sub each_object_inflate
{
    my ($self, $c, $object) = @_;
   
	my $hash = $self->next::method($c, $object);

	# Inflate the prefetched rows as well and add them into our hash
    foreach my $related ( @{$c->req->search_attributes->{prefetch}} ) {
    	next unless defined ($object->$related);
    	
    	# If a has_many is encountered then get it's rows but go no further.
    	if ($object->$related->isa('DBIx::Class::ResultSet')) {
    		push(@{$hash->{$related}}, { $_->get_columns }) for ($object->$related->all);
    	}
    	else {
    		$hash->{$related} = { $object->$related->get_columns };
    	}
    }
    
    return $hash;
}

1;

These overrides make it so search attributes, like prefetch are honored in POST and PUT requests. Once a new row has been created or updated, instead of just returning the row, DBIC::API now does another query with the given search options. This makes extjs happy because now it has the full set of data that it expects.

To make this really work one more override is required. This time on the extjs side. Inside your HttpProxy is a property called api. It allows you to specify the URL and method for each action (read/update/create/destroy). Unfortunately it doesn’t allow the specification of params for each call. Here is an override to change that.

/* Allows the use of params inside the api config option of
 * Ext.data.HttpProxy.
 * 
 * This is handy when extra params need to be sent to the
 * controller (prefetch for example).
 */

Ext.override(Ext.data.HttpProxy, {
    doRequest : function(action, rs, params, reader, cb, scope, arg) {
	    var  o = {
	        method: (this.api[action]) ? this.api[action]['method'] : undefined,
	        request: {
	            callback : cb,
	            scope : scope,
	            arg : arg
	        },
	        reader: reader,
	        callback : this.createCallback(action, rs),
	        scope: this
	    };
	
	    // If possible, transmit data using jsonData || xmlData on Ext.Ajax.request (An installed DataWriter would have written it there.).
	    // Use std HTTP params otherwise.
	    if (params.jsonData) {
	        o.jsonData = params.jsonData;
	        // Edit, make use if api params if they exist
	        Ext.applyIf(o.jsonData, this.api[action].params);
	    } else if (params.xmlData) {
	        o.xmlData = params.xmlData;
	        // Edit, make use if api params if they exist
	        Ext.applyIf(o.xmlData, this.api[action].params);
	    } else {
	        o.params = params || {};
	        // Edit, make use if api params if they exist
	        for (var param in this.api[action].params) {
	        	o.params[param] = Ext.encode(this.api[action].params[param]);
	        }
	    }
	    // Set the connection url.  If this.conn.url is not null here,
	    // the user must have overridden the url during a beforewrite/beforeload event-handler.
	    // this.conn.url is nullified after each request.
	    this.conn.url = this.buildUrl(action, rs);
	
	    if(this.useAjax){
	
	        Ext.applyIf(o, this.conn);
	
	        // If a currently running request is found for this action, abort it.
	        if (this.activeRequest[action]) {
	            ////
	            // Disabled aborting activeRequest while implementing REST.  activeRequest[action] will have to become an array
	            // TODO ideas anyone?
	            //
	            //Ext.Ajax.abort(this.activeRequest[action]);
	        }
	        this.activeRequest[action] = Ext.Ajax.request(o);
	    }else{
	        this.conn.request(o);
	    }
	    // request is sent, nullify the connection url in preparation for the next request
	    this.conn.url = null;
	}
});

With that taken care of we can now change our HttpProxy to use the new params config.

store:new Ext.data.Store({
	proxy:new Ext.data.HttpProxy({
		url:'/rest/person',
		restful:true,
		api:{
			read:{
				url:'/rest/person',
				params:{
					prefetch:["address']
				}
			},
			create:{
				url:'/rest/person',
				params:{
					prefetch:["address']
				}
			},
			update:{
				url:'/rest/person',
				params:{
					prefetch:["address']
				}
			}
		},			
		reader:new Ext.data.JsonReader({
    			totalProperty:'totalcount',
    			successProperty:'success',
    			idProperty:'person_id',
    			root:'data',
    			messageProperty:'message'  // <-- New "messageProperty" meta-data
		},[
			{name:'person_id',  type:'int'},
		   	{name:'first_name', type:'int'},
		   	{name:'last_name',  type:'int'},
		   	{name:'address'}    // Object
		])
		...
	)}
)}

That’s all there is to it. You can now prefetch in POST and PUT requests.

I know some of this code is quite messy. I’m still getting the hang of how a lot of this stuff works. Feedback of any kind is welcome.