0

I have a table entity_translations, which relate O->M to string_translations via entity_translation_fields. Each row in string_translations has an additional status that can be obtained via a join with current_string_translation_status.

I need to add an additional column to the result of a select from entity_translations that will be determined based on the following conditions:

  1. If all string_translation records that correspond to an entity_translation record have status = 'READY', then that entity_translation gets status 'READY'.
  2. Else, if any string_translation records that correspond to an entity_translation record have status = 'FAILED', then that entity_translation gets status 'FAILED'.
  3. Else, if any string_translation records that correspond to an entity_translation record have status = 'IN_PROGRESS', then that entity translation gets status 'IN_PROGRESS'.
  4. Else, that entity translation gets status 'ADDED'.

What I want

Given schema and data in this fiddle, I would like to have the following result:

|  id | entity_id | entity_type |      status |
| --- | --------- | ----------- | ----------- |
|   1 |         1 |        post |      FAILED | <--- Because status for string translation 2 is "FAILED"
|   2 |         2 |        post | IN_PROGRESS | <--- Because status for string translation 5 is "IN_PROGRESS"
|   3 |         3 |        post |       READY | <--- Because status for string translation 6 is "READY"

Here's the query I used to get the data that I would like to aggregate into the above desired result:

SELECT *
FROM `entity_translations`
JOIN `entity_translation_fields`
    ON (`entity_translation_fields`.`entity_translation_id` = `entity_translations`.`id`)
JOIN `string_translations`
    ON (`string_translations`.`id` = `entity_translation_fields`.`string_translation_id`)
JOIN `current_string_translation_status`
    ON (`current_string_translation_status`.`string_translation_id` = `string_translations`.`id`)
ORDER BY `entity_translations`.`id` ASC, `string_translations`.`id` ASC;

Markdown tables weren't rendering for some reason, so I just added it as code.

What I tried

  1. One way could be to join entity_translation with string_translation (and its status view), then group by entity_translation's PK. But I don't know how I could then determine the values from the status column that is not in GROUP BY. If ANY() and ALL() were aggregate functions, I could use them in multiple nested IF conditions; but they're not.
  2. Perhaps, another way is to use a subquery to add a column to string_translation. But I don't understand how to then use ANY or ALL with yet another subquery.

Here is the whole setup. It has some extra things that are not relevant to this current problem. The query that is relevant is the one that says the following on top:

-- Get data for all entity translations, including their current status <----- PROBLEM
1
2

If I understand the task correctly, then

SELECT subquery.id, 
       subquery.entity_type, 
       subquery.entity_id,
       CASE WHEN SUM(subquery.status != 'READY') = 0 THEN 'READY'
            WHEN SUM(subquery.status = 'FAILED') > 0 THEN 'FAILED'
            WHEN SUM(subquery.status = 'IN PROGRESS') > 0 THEN 'IN PROGRESS'
            ELSE 'ADDED'
            END status
FROM (SELECT et.id, et.entity_type, et.entity_id, stl.status
      FROM entity_translations et
      JOIN entity_translation_fields etf ON etf.entity_translation_id = et.id
      JOIN string_translations st ON etf.string_translation_id = st.id
      JOIN string_translation_log stl ON stl.string_translation_id = st.id
      JOIN (SELECT string_translation_id, MAX(changed) changed
            FROM string_translation_log
            GROUP BY string_translation_id) stl1 ON stl1.string_translation_id = stl.string_translation_id
                                                AND stl1.changed = stl.changed
      ORDER BY et.id, etf.string_translation_id, stl.changed ) subquery
GROUP BY subquery.id, 
         subquery.entity_type, 
         subquery.entity_id;

fiddle

18
  • The result this produces in your fiddle looks correct, thank you very much! However, I'm having trouble understanding 1) Why SUM() is used. I don't understand how it works for string values. 2) How I could do the same, but by using the view that aggregates status log entries. Alternatively, why that is not advisable. 3) Why make a subquery for the same table. I tried doing it without the subquery, and got correct results. – XedinUnknown Aug 31 '20 at 14:18
  • Here's what my setup looks right now, and it seems to be working correctly: db-fiddle.com/f/wFQtPUnQsJc4cPWb4tGoyQ/10 – XedinUnknown Aug 31 '20 at 14:25
  • @XedinUnknown, CASE WHEN SUM(subquery.status != 'READY') ... is the same as: SUM(CASE WHEN subquery.status != 'READY' THEN 1 ELSE 0 END) .... MySQL has a somewhat liberal attitude when it comes to implicit casting. – Lennart Aug 31 '20 at 16:17
  • I don't understand how it works for string values. Argument of SUM() function is comparing expression which have not string but integer (formally boolean but MySQL have no such datatype). Integer may be summarized easily. * I tried doing it without the subquery, and got correct results.* This is random - without it a random row from a group will be used whereas subquery guarantees that the row with maximal datetime will be taken. Execute the subquery only and investigate the output, do the same with subquery and the table itself only. – Akina Aug 31 '20 at 16:44
  • If SUM(CASE WHEN) is same as CASE WHEN SUM(), then I understand: it's implicitly transforming bool to int, and adding them together. About subquery, I will try. Thanks! – XedinUnknown Aug 31 '20 at 17:21
0

So, based on what @Akina has explained to me, here is my version, which I prefer, because it uses an already existing view, keeping that logic centralized, and making the eventual query simpler. There are also a little more fields, because I eventually needed them. They can be removed from SELECT, in which case they also have to be removed from the GROUP BY clause.

Disclaimer: Of course, without the answer of @Akina, I wouldn't be able to figure it out. I'm just adding mine here to show perhaps a different approach. I am a software engineer, an architect; not a DBA.

SELECT
    `et`.`id`,
    `et`.`entity_type`,
    `et`.`entity_id`,
    `et`.`source_lang`,
    `et`.`destination_lang`,
    CASE WHEN SUM(`csts`.`status` != 'READY') = 0 THEN 'READY'
         WHEN SUM(`csts`.`status` = 'FAILED') > 0 THEN 'FAILED'
         WHEN SUM(`csts`.`status` = 'IN_PROGRESS') > 0 THEN 'IN_PROGRESS'
         ELSE 'ADDED'
         END status
FROM `entity_translations` `et`
JOIN `entity_translation_fields` `etf`
    ON (`etf`.`entity_translation_id` = `et`.`id`)
JOIN `string_translations` `st`
    ON (`st`.`id` = `etf`.`string_translation_id`)
JOIN `current_string_translation_status` `csts`
    ON (`csts`.`string_translation_id` = `st`.`id`)
GROUP BY `et`.`id`, `et`.`entity_type`, `et`.`entity_id`, `et`.`source_lang`, `et`.`destination_lang`;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.