Skip to content
May 28, 2010 / Andy

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:

    			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) = @_;
	# 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];
	# 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,
    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;


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
 * This is handy when extra params need to be sent to the
 * controller (prefetch for example).

Ext.override(, {
    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);
	        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?
	        this.activeRequest[action] = Ext.Ajax.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.

    			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.



Leave a Comment
  1. john napiorkowski / May 29 2010 6:06 AM

    DBIC::API seems pretty complete, but I guess I haven’t had time to wrap my head around it. Something I’d like to see is a how to combine DBIC store with other underlying models so for example you could do a login form that included a reCaptcha element.

    I keep thinking at some point all the templating stuff will be totally client side, in which case something like this to bridge between the client and server is going to be really important

  2. Moritz Onken / May 30 2010 7:19 AM


    did you think about using a DBIC view to aggregate the data you need and then use DBIC::API to deliver the data?


    • Andy / May 30 2010 9:36 AM

      I hadn’t considered using a DBIC view. Do you mean DBIx::Class::ResultSource::View? Seems like it may be possible to hook something like that in. Though I’m not sure how to do it and still keep things general.

  3. Madhava / Jul 4 2012 6:03 AM


    Thanks for the efforts.

    I am using ExtJs 3.0 as front end. And struts2, spring and hibernate at server side.

    Would you please clarify me:

    1) Though I send parameters to HttpProxy’s api using ‘params’, I observed that they ain’t sent to server. I confirmed the same using Firebug. Are there any configs
    Note: I have configured [method: ‘POST’] to HttpProxy. For security purpose I must not send parameters with URL.

    2) Are there any alternatives to send parameters to server component (Struts Action class).


  4. tabletops / Apr 11 2013 1:15 AM

    I recognized my close friend Lor has commented as well.
    If she gave you five forks then I understand it is reasonable.

  5. Agustin / Apr 18 2013 3:32 PM

    I was wondering if you ever considered changing the structure of your
    website? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people
    could connect with it better. Youve got an awful lot of text for only having
    1 or 2 pictures. Maybe you could space it out better?

  6. Lanora / Jul 16 2013 9:01 PM

    You can select the type you want or get an
    interior designer to aid you with the detailing. It&rsquos just an illusion,
    of program, but it performs,&rdquo she says.

  7. relationship therapy / Aug 1 2013 11:49 AM

    Undeniably consider that that you said. Your favorite reason seemed
    to be at the net the easiest thing to understand of. I say
    to you, I certainly get irked while people consider worries that they just do not realize about.
    You controlled to hit the nail upon the top and defined out the entire thing without
    having side effect , other people could take a signal.

    Will probably be back to get more. Thank you

  8. Gregory / Aug 1 2013 4:56 PM

    I quite like reading a post that can make men and women think.
    Also, many thanks for allowing for me to comment!

  9. Home Page / Aug 5 2013 9:59 PM

    Fantastic beat ! I wish to apprentice whilst you amend your
    site, how could i subscribe for a blog website? The account helped me a acceptable deal.
    I have been a little bit familiar of this your broadcast provided
    vivid clear idea

  10. / Mar 8 2014 9:39 PM

    It will ake connecting with girls a whole lot more stressful once you have massive doubts in your thoughts.
    She is also giver and for her sex iss special, something she can’t give to anyone.
    These methods of attraction are certainly not rational.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: