Handling Drupal Field “delta” with a manual insert

OK, so, you should never do a manual insert of data with SQL into a Drupal field.  Or, rather, it’s probably really, really frowned upon, since the fields are supposed to be validated, possibly transformed, and just in general, a CMS should keep you from getting your hands dirty.  But there are a couple of cases where SQL simply is preferred:

  1. Say you have a few hundred thousand records to get in — you’ll wait for days for Feeds Importer to manage this for you (and I love Feeds Importer, but…).
  2. Or, let’s say you have an entity reference to populate for entities that already exist, but you have to do some complicated SQL to connect them together based on some attributes in other fields, or in some other source.

It is case #2 that I will demonstrate here.  I need to insert an entity reference from entity “foo” to entity “bar”, both of whom already have a reference to a 3rd common entity “baz”.  If we could garantee that you only have many to 1 (many foo, 1 bar) or 1 to 1 relationships (1 foo, 1 bar), a straightforward insert will work (see code #1).   In fact, you could probably just use Views to construct this, and then import it with Feeds Importer.

Code 1: Insert into entity reference table assuming only 1 record per source entity.

insert into field_data_foobar ( entity_type, bundle, deleted, entity_id, revision_id, language, delta, foobar_target_id)
select 'foo_entity', a.bundle, 0, a.fooid, a.fooid, 'und', 1, d.barid
from foo_entity as a 
left outer join field_data_foobaz as b 
on (
  a.fooid = b.entity_id 
  and b.entity_type = 'foo_entity'
)
left outer join field_data_barbaz as c 
on (
  b.barbaz_target_id = c.barbaz_target_id
  and c.entity_type = 'bar_entity'
)
left outer join bar_entity as d 
on (d.barid = c.entity_id)
where b.foobaz_target_id is not null 
  and c.barbaz_target_id is not null
  and d.barid is not null 
  and (a.fooid || '-' || d.barid) not in (
    select entity_id || '-' || foobar_target_id from field_data_foobar 
    where entity_type = 'foo_entity' 
  )
;

However, if you have a 1 to many (1 foo to many bar), you will run afoul of the Drupal entity field requirement to have a unique “delta” value for each record for entity foo, so that it can maintain a consistent sort order for them in the field interface (and perhaps there are other reasons?).   This can be tricky, since one would have to insert the proper delta value, incrementing each time you inserted a supplemental record for an entity with multiple inserts.  There are many ways to tackle this, including using a shell script or PHP, but this use case is just iterating a single SQL statement over and over until you get to zero inserts.  The basic algorithm goes liek this:

  1. Aggregate on the bundle and PKID column of your entity reference source entity (foo_entity in this case).
  2. Use min(d.barid) to get the lowest value of target entity
  3. Join back to insert table, using count of number of values for the source entity +1 as the delta
  4. Use filter in the where clause where you select instances of existing string (“fooid-barid”) to insure that duplicate values are not entered (this is also used in Code 1 to prevent duplication).
  5. Repeat the insert query until it inserts 0 values.

Code 2: insert statement that prevents duplicate records,

insert into field_data_foobar ( entity_type, bundle, deleted, entity_id, revision_id, language, delta, foobar_target_id)
select 'foo_entity', a.bundle, 0, a.fooid, a.fooid, 'und', count(e.*) + 1, min(d.barid)
from foo_entity as a 
left outer join field_data_foobaz as b 
on (
  a.fooid = b.entity_id 
  and b.entity_type = 'foo_entity'
)
left outer join field_data_barbaz as c 
on (
  b.barbaz_target_id = c.barbaz_target_id
  and c.entity_type = 'bar_entity'
)
left outer join bar_entity as d 
on (d.barid = c.entity_id)
left outer join field_data_foobar as e 
on (e.entity_id = a.fooid and e.entity_type = 'foo_entity')
where b.foobaz_target_id is not null 
  and c.barbaz_target_id is not null
  and d.barid is not null 
  and (a.fooid || '-' || d.barid) not in (
    select entity_id || '-' || foobar_target_id from field_data_foobar 
    where entity_type = 'foo_entity' 
  )
group by a.bundle, a.fooid 
;
Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

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

Connecting to %s