Rails external model updates

A requirement of the phonebook application I’ve been working on is support for dialling policies.

One complicating factor is that updating a departmental policy default is automatically applied to all (and only) the departmental accounts that had the old policy setting. There are 11 types of policy to support. My DBA had wanted to use a trigger but I want to avoid such things mainly because I want to make sure I apply application level logging to record all of the updates which is hard with a trigger.

It took me a day to figure it out and it became inevitable that I would break a Rails golden rule but I believe my approach is clean and easily understandable.

One thing that troubled me was finding a reasonably efficient way of identifying the affected account records in a departmental update and then going though each to apply the change. I managed a neat optimisation.

Because this is an update operation it so happens that the before and after states are available in the update controller: in the controller instance variable (@departments) and the submitted params variable (department_params).

In my extensions table model I added a scope like,

scope :policy_type_affectees, ->(dept_id, params) {find_by_sql("
SELECT p.id AS id,
p.fullname AS fullname,
p.extension_id AS extension_id,
p.room_id AS room_id,
CASE p.conferencing_policy_id
WHEN d.conferencing_policy_id THEN #{params[:conferencing_policy_id]}
ELSE p.conferencing_policy_id
END AS conferencing_policy_id,
CASE p.voice_policy_id
WHEN d.voice_policy_id THEN #{params[:voice_policy_id]}
ELSE p.voice_policy_id
END AS voice_policy_id
phones p INNER JOIN departments d
ON p.department_id = d.id
WHERE (p.voice_policy_id = d.voice_policy_id
OR p.conferencing_policy_id = d.conferencing_policy_id)
AND d.id = #{dept_id}")}

(abbreviated to just 2 from 11 actual policies). The query is run before the update call in the controller:

ppt = Phone.policy_type_affectees(@department.id, department_params)

The effect of this query is that if the user record doesn’t match the old departmental default then the user policy is selected, but if it does match, the new departmental default will be selected even though it isn’t in the database!

The update method for the department is called and i added the following snippet.

ppt.each do |phone|
  Phone.update(phone.id, phone.to_hash)

For each affected user account (Phone record) from the query, call the model’s update method with the id and a hash representation of the record and then call a local (to the controller) method to insert an activity log entry and move to the next one.

The good stuff? Being able to find the affected rows and add the new values in one query without any DB lookups require during the individual account updates.

Bad stuff? Duplication of the main departmental log_update method to cater for an external model. A worthy sacrifice given the simplicity of the overall solution and when I get round to writing an activity_log gem, this can probably be evaporated out. There’s also the matter of not using parameterised queries in the find_by_sql call and inserting submitted parameters in teh query. I know there’s a way of parameterising for find_by_sql but if I remember, it’s quite messy and this is low risk.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.