Change query LEFT JOIN to FULL OUTER JOIN (respect…

Having an Entity-Attribute Value setup table which is structured like this (it is from a third party plugin, I can not change the database design):

Now I want to create a table whith data_id being the id, the names being the columns and the values being their values. Still every data_id does not have a value for every name therefore I want the value in the result to be NULL (or empty) in case there is no value for this name within the original table.

Now I have written a PHP script which is generating the required query for me:

$ihash = function($len = 10){
        return substr(str_shuffle(str_repeat("abcdefghijklmnopqrstuvwxyz", 10)), 0, 10);
    };
$columns = $wpdb->get_col("SELECT DISTINCT name FROM ".$wpdb->prefix."cf7_vdata_entry");

$fromselects = [];
$left_joins = [];
$wheres = [];
$used=[];

foreach($columns as $column) {
    $m = $ihash();
    while(in_array($m,$used)) {
        $m = $ihash();
    }
    array_push($used,$m);
    array_push($fromselects,"$m.value as `$column`");
    $left_joins  .= " LEFT JOIN wp_cf7_vdata_entry AS $m ON a.data_id = $m.data_id ";
    array_push($wheres,"$m.name = '$column'");
}

$query = "SELECT a.data_id, ".implode(", ",$fromselects)."
  FROM (SELECT DISTINCT data_id FROM wp_cf7_vdata_entry) AS a JOIN
  ".$left_joins."
 WHERE ".implode(" AND ",$wheres);

This is how a generated query looks like:

SELECT a.data_id, 
       vtddnqrdjy.value AS `foerderung`, 
       fwfyxgczvn.value AS `company`, 
       jwlpmnbepe.value AS `firstname`,
       -- ... more fields
FROM   (SELECT DISTINCT data_id 
        FROM   wp_cf7_vdata_entry) AS a 
       JOIN wp_cf7_vdata_entry AS vtddnqrdjy 
         ON a.data_id = vtddnqrdjy.data_id 
       JOIN wp_cf7_vdata_entry AS fwfyxgczvn 
         ON a.data_id = fwfyxgczvn.data_id 
       JOIN wp_cf7_vdata_entry AS jwlpmnbepe 
         ON a.data_id = jwlpmnbepe.data_id 
       -- ... more joins
WHERE  vtddnqrdjy.name = 'foerderung' 
       AND fwfyxgczvn.name = 'company' 
       AND jwlpmnbepe.name = 'firstname' 
       AND mloxjygcqp.name = 'lastname' 
       -- ... more fields
LIMIT  10

The result table is generated correctly, but the result is empty:

The reason is that results which do not have ALL column values are filtered out, but they should have null values instead (having exactly one result for each data_id existing in the table). I was thinking about replacing all LEFT JOINs with FULL OUTER JOINs (which has to be faked in MySQL), but this is overcomplicating things and the probably already bad performance will be extremly bad than. How could I solve this?

Answer

MySQL does not support FULL OUTER JOIN. Instead, use aggregation:

SELECT de.data_id,
       MAX(CASE WHEN de.name = 'foerderung' THEN de.value END) as foerderung, 
       MAX(CASE WHEN de.name = 'company' THEN de.value END) as company
           . . .   -- just repeat for each column
FROM wp_cf7_vdata_entry de
GROUP BY de.data_id;

Change query LEFT JOIN to FULL OUTER JOIN (respecting null values) – how to change this (without horrible performance afterwards)?

Advertisement

Answer