talkingCode

Using Arel without Rails

Arel is a Relational Algebra for Ruby – a handy way to create SQL queries programmatically without requiring a full ORM. I’ve been doing some work (generating reports) that requires complex queries but between which there’s enough shared functionality to warrant some modularity in the query generation.

Cutting to the chase… Arel requires a database connection. It prefers ActiveRecord (which is probably overkill), but you do at least not have to create all the Model objects normally associated with Rails:

#!/usr/bin/ruby
require 'thread'  # RubyGems/ActiveRecord compatibility fix
require 'rubygems'
require 'active_record'
require 'arel'

@config = # Load some DB config

# Connect to the database
ActiveRecord::Base.establish_connection(
  :adapter => 'mysql',
  :host => @config[:host],
  :username => @config[:username],
  :password => @config[:password],
  :database => @config[:database]
)

# Setup the engine for Arel
Arel::Table.engine = Arel::Sql::Engine.new(ActiveRecord::Base)

# Create an Arel table - should match a table in your database
@users = Arel::Table.new(:users)

all of which is easy enough, completely undocumented, and only requires reading the code of most of the library.

Now, though, you can go ahead and write some fun queries:

@gadgets = Arel::Table.new(:gadgets)
query = @users.project(@users[:id].count).
        join(@gadgets).on(@gadgets[:creator_id].eq(@users[:id])).
        project(@gadgets[:name]).
        group(@gadgets[:name]).
        order(@gadgets[:name].asc)

puts query.to_sql

which outputs the very reassuring “SELECT COUNT(`users`.`id`), `gadgets`.`name` FROM `users` INNER JOIN `gadgets` ON `gadgets`.`creator_id` = `users`.`id` GROUP BY `gadgets`.`name` ORDER BY `gadgets`.`name` ASC”. You can even execute it:

puts "Gadget\tUsers"
ActiveRecord::Base.connection.execute(query.to_sql).each_hash do |row|
  puts [ row["name"], row["count"] ].join("\t")
end

Simple as that. The library has some issues, and no documentation, but I’ve been playing with it on my fork over at Github and it seems pretty okay.

Enjoy

Editable table with Javascript, TableKit, AJAX and Rails

Me and my tables. First drag and drop, then drag-select, and now click-to-edit values with date parsing magic. It’s like having a spreadsheet in a webpage, but less pointful. You will need:

• Prototype
• Fastinit (Optional, and can also be obtained from Tablekit)
• Tablekit
• DateJS

… and a table of data:

As you can see, by clicking the cells, you can edit the data. The table data is generated by an RHTML template using appropriate ActiveRecord models:

<table class="editable">
<thead>
  <tr>
    <th>Hardware Config</th>
    <th id="odm_id">ODM</th>
    <th id="brand_id">Brand</th>
    <th id="model_name">Model</th>
    <th id="date">Date</th>
    <th><!-- actions --></th>
  </tr>
</thead>
<tbody>
<% hwconfigs_by_id = Hash.new %>
<% @hwconfigs.each { |hwc| hwconfigs_by_id[hwc.product_code] = hwc } %>
<% for i in (1..200) %>
   <% code = 1024 - i%>
   <% hwconfig = hwconfigs_by_id[code.to_s] %>
     <tr class="<%= cycle("odd", "even")%>" id="<%= code %>">
        <td><%= code %></td>
        <% if hwconfig %>
          <td><%= hwconfig.odm.name if hwconfig.odm %></td>
          <td><%= hwconfig.brand.name if hwconfig.brand %></td>
          <td><%= hwconfig.model_name %></td>
          <td><%= hwconfig.date %></td>
        <% else %>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
        <% end %>
    </tr>
<% end %>
</tbody>
</table>

In the same template, the following code adds the Javascript that we're going to need to make the table editable:

<%= javascript_include_tag "tablekit" %>
<%= javascript_include_tag "fastinit" %>
<%= javascript_include_tag "date-en-GB" %>
<script>
TableKit.options.editAjaxURI = '<%= url_for :controller => "hwconfigs", :action => "table_edit"%>';
TableKit.Editable.textInput('date', {}, function(string) {
  var format = "yyyy-MM-dd";
  var date = Date.parse(string);
  if (date)
  {
    return date.toString(format);
  }
  return date;
}, "today");
TableKit.Editable.textInput('model_name', {}, undefined, "");
TableKit.Editable.selectInput('odm_id', {}, [
  <% for oem in Odm.find(:all,  rder => 'name') %>
    <%= "['#{oem.name}','#{oem.id}']," %>
  <% end %>
]);
TableKit.Editable.selectInput('brand_id', {}, [
  <% for brand in Brand.find(:all,  rder => 'name') %>
    <%= "['#{brand.name}','#{brand.id}']," %>
  <% end %>
]);
</script>

How do you get that to update the data model? In Rails, you'd configure the javascript to post to your hwconfigs/table_edit action, and process the posts in the hwconfigs ActionController as follows:

def table_edit
  hwconfig = Hwconfig.find_by_product_code(params[:id])
  if !hwconfig
    hwconfig = Hwconfig.new()
    hwconfig.product_code = params[:id]
  end
  if !params[:value]
    params[:value] = ""
  end
  if hwconfig.respond_to? params[:field].to_sym
    hwconfig.update_attributes(params[:field] => params[:value])
  end
  result = params[:value]
  case params[:field]
    when "brand_id"
       result = hwconfig.brand.name
    when "odm_id"
       result = hwconfig.odm.name
  end
  render :text => result
  return
end

Two things worth noting there. First is the cheeky use of introspection to get the model updated (respond_to?). I keep saying this, but it's worth remembering that this code completely trusts the client to be sending valid data. In our table we'll have selected and sent a list of values for the drop-downs, but there's nothing to stop someone determined sending a POST with a different set of values.
Second thing to note is that we echo back the text that we want rendered in the table cell. In the case of text and dates, that's easy. In the case of the drop downs, we need to convert the value sent back into the name of the item that we want displayed in the table cell.
That's the bulk of the work. There are a couple of neat tricks that you can use to make your table a bit easier to use. If you click on one of the empty 'Date' cells, you'll see that the default text in the edit box is 'today'. Clicking 'OK' magically translates that text into today's date, which is quite cool. You can also try things like 'tomorrow', 'last tuesday' or 'next week'. That's DateJS in action. Problem is, DateJS is a client-side library so we need to do the translation from text to date before the post hits the server. How do we swindle that one? In 'prototype.js', we can edit the serializeElements method to perform some 'validation' before the post is sent:

  serializeElements: function(elements, getHash) {
    var data = elements.inject({}, function(result, element) {
      if (!element.disabled && element.name) {
        var key = element.name;
	if ($(element).validator)
	{
	  value = $(element).validator($(element).getValue());
	}
        else
	{
	  value = $(element).getValue();
	}
        if (value != undefined) {
          if (result[key]) {
            if (result[key].constructor != Array) result[key] = [result[key]];
            result[key].push(value);
          }
          else result[key] = value;
        }
      }
      return result;
    });

'course, we'll need to edit the constructor for the TextInput to allow us to specify a validation function and a default value in TableKit.Editable.CellEditor.prototype:

TableKit.Editable.textInput = function(n,attributes,validator,defaultvalue) {
  TableKit.Editable.addCellEditor(new TableKit.Editable.CellEditor(n, {
    element : 'input',
    attributes : Object.extend({name : 'value', type : 'text'}, attributes||{}),
    validator : validator,
    defaultvalue: defaultvalue
  }));
};

and add the validation (and default value) code:

case 'textarea':
  if (op.validator)
  {
    field.validator = op.validator;
  }
  var textVal = TableKit.getCellText(cell)
  if (textVal == undefined && op.defaultvalue != undefined)
  {
    field.value = op.defaultvalue;
  }
  else
  {
    field.value = textVal;
  }

and while we're at it fix a bug in the drop-down value code in the same function:

case 'select':
  var txt = TableKit.getCellText(cell);
  $A(op.selectOptions).each(function(v){
    field.options[field.options.length] = new Option(v[0], v[1]);
    if(txt === v[0]) {
      field.options[field.options.length-1].selected = 'selected';
    }
  });
  break;

Couldn't be simpler. Or something.