Double theta to compute delta

We are denormalizing into a field past migration; while usually I freak out writing directly to the field tables in this case it's justified because it's a custom module (never do this in contrib) and also because we are well aware of what does not happen when you do that. With that said, we have a query that roughly looks like this:

INSERT INTO {field_data_field_denorm_data} (entity_type, bundle, deleted, entity_id, revision_id, language, delta, field_denorm_data_target_id) SELECT 'user', 'user', 0, field1, field1, 'und', ????????????, field2 FROM table1 .... long complex query where a single field1 has several field2.

So yeah. But delta... delta is a problem. MSSQL has ROW_NUMBER(), MySQL does not and the usual replacement (user variables, @x:=@x+1) is documented to be incorrect and not reliable. There's a lesser known trick that can be used, however:

CREATE TABLE tmp SELECT DISTINCT field1, field2 .... long complex query repeated

now, ALTER TABLE tmp ADD KEY(field1) in preparation, and then:

INSERT INTO {field_data_field_denorm_data} (entity_type, bundle, deleted, entity_id, revision_id, language, delta, field_denorm_data_target_id) SELECT 'user', 'user', 0, t1.field1, t1.field1, 'und', COUNT(*) - 1, field2 FROM tmp t1 INNER JOIN tmp t2 ON t1.field1=t2.field1 AND t1.field2 >= t2.field2 GROUP BY t1.field1,t1.field2

Double theta for the win.

Explanation (numbers copied from real data):

+-------------+----------+---------------------------------+ | t1.field1   |t1.field2 | group_concat(t2.field2)         | +-------------+----------+---------------------------------+ |        4440 |      427 | 427                             | |        4440 |      428 | 427,428                         | |        4440 |      429 | 427,428,429                     | |        4440 |      433 | 427,428,429,433                 | |        4440 |      435 | 427,428,429,433,435             | |        4440 |      436 | 427,428,429,433,435,436         | |        4440 |      438 | 427,428,429,433,435,436,438     | |        4440 |      439 | 427,428,429,433,435,436,438,439 | +-------------+----------+---------------------------------+

Consider the user 4440. When t1.field2 is 427, when joining with t2, there's only itself when joined, so the count is 1 , delta is 0. When t1.field2 is 428 now we have two, namely 428 and 428, so the count is 2 and the delta is 1. And so on. The unsaid, implicit trick is that MySQL orders on what you group.

Advertisment: If you find this too complex, consider hiring Tag1 Consulting for your database problems.

Original Article:

Double theta to compute delta