Mysql – Select value in group based on every/any conditions

MySQL

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:

If all string_translation records that correspond to an entity_translation record have status = 'READY', then that entity_translation gets status 'READY'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'FAILED', then that entity_translation gets status 'FAILED'.
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'.
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

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.
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

Best Answer

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

Related Solutions

MySQL Query Plan – Understanding Silly Query Plans

I'm assuming because you have SELECT * above, that you are actually using SELECT *.

1. If MySQL were to use the index_promotion_codes_on_t_id_and_gu_id index when ordering on id, then MySQL would have to do the following:

Retrieve the primary keys for all 33088 records that match the WHERE clause
Search for each of the 33088 records using the primary key and read all the table data for the record
Order all 33088 records by id in a temp table
Get the first record

If it were a bubble sort, it would quickly find the first record and could end the sort early, but with the method it uses, it's nearly done sorting before it's sure it has the first record.

Whereas if it uses the primary key (which has the order) it would do the following:

Scan the table records in order, following the primary key index
Find the first record that matches the WHERE clause (with estimated distribution, about 137 records before it finds one), then stop

2. 137 is the estimated number of number of rows it will scan before it finds a match. Total rows divided by 33088 matching records approximately equals 137. Strangely, the EXPLAIN results for the query without an ORDER BY doesn't consider LIMIT (the rows count is the total number of records that match the WHERE), but the query with the ORDER BY does consider the LIMIT (the number it expects to scan before finding one).

3. How else would you get the first record?

MySQL – Subquery or Multiple Left Joins, or Both?

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name 
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;