Extract substring from a blob column to make an addition to use it in subfield-CodePudding

I want to extract the value of a column that initialy was a blob field.

So I have this SQL query :

CREATE TEMPORARY TABLE IF NOT EXISTS single_commande AS (
    SELECT o.order_id, coi.order_item_id, CONVERT(coic.adjustments_value USING utf8) AS ajustement
    FROM all_orders o
        LEFT JOIN commerce_order_item coi
            on o.order_id = coi.order_id
        LEFT JOIN commerce_order_item__adjustments coic
            on coic.entity_id = coi.order_item_id
    WHERE o.order_id = 51
);

Which gave me those data :

The adjustment values look like this :

O:32:"Drupal\commerce_order\Adjustment":7:{s:7:"..type";s:3:"tax";s:8:"..label";s:4:"Taxe";s:9:"..amount";O:27:"Drupal\commerce_price\Price":2:{s:9:"..number";s:4:"4.60";s:15:"..currencyCode";s:3:"EUR";}s:13:"..percentage";s:3:"0.2";s:11:"..sourceId";s:48:"tva|default|56fdce53-c1db-4ae2-8612-c3f0a4e6e4da";s:11:"..included";b:1;s:9:".*.locked";b:0;}

And I need to extract the number value (here, 4.6) from this string, to this on each row and sum all those number to get a total.

I need this total to be used in another query, this one:

CREATE TEMPORARY TABLE IF NOT EXISTS traiteur_commandes AS (
    SELECT o.order_id     AS `CommandeID`,
           (SUM OF ALL ADJUSTMENT BY order_id  AS `TotalHT` /* to calculate */
    FROM all_orders o
        ...
    ORDER BY o.order_id
);

In the end, I'll remove the CREATE TEMPORARY TABLE IF NOT EXISTS single_commande because I'll need to use subquery, this is just for test.

I think I have to do something like

SELECT sum(CONVERT(coic.adjustments_value USING utf8 /* DO SOMETHING TO GET THE VALUE */ )) AS ajustment

Maybe I can extract the value with a regex of something like REGEXP '.*Price";s:[0-9] :"number".*' (i'm working on the regex for now)

Any idea if this is the best way or i'm totally wrong ?

EDIT : this is from the drupal commerce module, so I can't alter the field in the database. Also, I can't use php or something else, I can only export data through MySQL.

CodePudding user response：

Edit: Here's a solution for MySQL. Based on your comments, I believe you are using MariaDB, not MySQL. These two database products are not compatible.

I tested by setting a variable @s to the PHP serialized object you show. You would use your ajustement column as you query your temp table.

set @s = 'O:32:"Drupal\\commerce_order\\Adjustment":7:{s:7:"..type";s:3:"tax";s:8:"..label";s:4:"Taxe";s:9:"..amount";O:27:"Drupal\\commerce_price\\Price":2:{s:9:"..number";s:4:"4.60";s:15:"..currencyCode";s:3:"EUR";}s:13:"..percentage";s:3:"0.2";s:11:"..sourceId";s:48:"tva|default|56fdce53-c1db-4ae2-8612-c3f0a4e6e4da";s:11:"..included";b:1;s:9:".*.locked";b:0;}'

First use a REGEXP_SUBSTR() to extract the Price object.

mysql> select regexp_substr(@s,
  '"Drupal\\\\commerce_price\\\\Price":2:\\{[^}]*\\}', 1, 1, 'c') as PriceObject;
 ---------------------------------------------------------------------------------------------- 
| PriceObject                                                                                  |
 ---------------------------------------------------------------------------------------------- 
| "Drupal\commerce_price\Price":2:{s:9:"..number";s:4:"4.60";s:15:"..currencyCode";s:3:"EUR";} |
 ----------------------------------------------------------------------------------------------

Based on that, use another to extract the "..number" field and its following value.

mysql> select regexp_substr(
  regexp_substr(@s,
    '"Drupal\\\\commerce_price\\\\Price":2:\\{[^}]*\\}', 1, 1, 'c'),
  '"..number";[^;]*;') as number;
 ------------------------ 
| number                 |
 ------------------------ 
| "..number";s:4:"4.60"; |
 ------------------------

Based on that, use another to extract the value.

mysql> select regexp_substr(
  regexp_substr(
    regexp_substr(@s,
      '"Drupal\\\\commerce_price\\\\Price":2:\\{[^}]*\\}', 1, 1, 'c'),
    '"..number";[^;]*;'),
  '"[[:digit:].] "') as number;
 -------- 
| number |
 -------- 
| "4.60" |
 --------

Then strip the quotes and cast as a decimal number.

mysql> select cast(json_unquote(
  regexp_substr(
    regexp_substr(
      regexp_substr(@s,
        '"Drupal\\\\commerce_price\\\\Price":2:\\{[^}]*\\}', 1, 1, 'c'),
      '"..number";[^;]*;'),
    '"[[:digit:].] "')) as decimal(9,2)) as number;
 -------- 
| number |
 -------- 
|   4.60 |
 --------