Autor Zpráva
igid26
Profil
Zdravím,
potreboval by som poradiť s mojim SQL. Vopred upozorňujem, že SQL moc neovládam a uvedený kód som si stiahol a upravil podľa seba. Potreboval by som však spojiť riadky s rovnakým ID. Konkrétne sa jedná o stĺpec order_id. Hodnoty Qty, lineTotal, subtotal, sú číselné by sa mali navzájom spočítať. Hodnoty order_item_name sú názvy produktov a mali by navzájom spojiť, respektíve oddeliť čiarkou.

select p.ID as order_id, p.post_date, p.post_status, i.order_item_name, i.order_id, i.order_item_id, i.order_item_type, max( CASE WHEN im.meta_key = '_product_id' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as productID, max( CASE WHEN im.meta_key = '_qty' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as Qty, max( CASE WHEN im.meta_key = '_line_total' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as lineTotal, max( CASE WHEN im.meta_key = '_line_subtotal' and i.order_item_id = im.order_item_id THEN im.meta_value END ) as subtotal, max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email, max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name, max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name, max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1, max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2, max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city, max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state, max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode, max( CASE WHEN pm.meta_key = '_billing_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_phone, max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_email, max( CASE WHEN pm.meta_key = '_billing_country' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_country, max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total, max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax, max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date from wp_posts as p, wp_postmeta as pm, wp_woocommerce_order_items as i, wp_woocommerce_order_itemmeta as im where p.post_type = 'shop_order' and p.ID = pm.post_id and p.ID = i.order_id and order_item_type = 'line_item' group by i.order_item_id HAVING _billing_email = 'jankohrasko@mojemail.sk'  
ORDER BY `Qty` ASC

Samozrejme ak by niekto prišiel s nejakým elegantnejším riešením budem rád :)

Vopred ďakujem za pomoc.
Keeehi
Profil
Měl jsi tam obrovský cross join. Na tom by se ti ten dotaz hodně brzo utavil. Ono by asi šlo něco nedělat jako subquery ale upřímně, nechce se mi o tom moc přemýšlet. Už takto musím v hlavě držet 4 tabulky a když nemám možnost si to vyzkoušet, tak se do takového mentáního cvičení pouštět nebudu.
To že si to nemůžu otestovat oproti databázi má ještě jeden důsledek - můžou v tom být chyby. Pokud ano, tak to snad zvládneš opravit, přehledné to snad je, takže pokud jsem někde zaměnil jeden typ id za jiný, nebo tak podobně, tak snad na to přijdeš.
SELECT
    *
FROM (
    SELECT
        ID AS order_id,
        post_date,
        post_status
    FROM
        wp_posts
    WHERE
        post_type = 'shop_order'
) AS orders
LEFT JOIN (
    SELECT
        post_id AS order_id,
        MAX(CASE WHEN meta_key = '_billing_first_name' THEN meta_value END) AS _billing_first_name,
        MAX(CASE WHEN meta_key = '_billing_last_name' THEN meta_value END)  AS _billing_last_name,
        MAX(CASE WHEN meta_key = '_billing_address_1' THEN meta_value END)  AS _billing_address_1,
        MAX(CASE WHEN meta_key = '_billing_address_2' THEN meta_value END)  AS _billing_address_2,
        MAX(CASE WHEN meta_key = '_billing_city' THEN meta_value END)       AS _billing_city,
        MAX(CASE WHEN meta_key = '_billing_state' THEN meta_value END)      AS _billing_state,
        MAX(CASE WHEN meta_key = '_billing_postcode' THEN meta_value END)   AS _billing_postcode,
        MAX(CASE WHEN meta_key = '_billing_phone' THEN meta_value END)      AS _billing_phone,
        MAX(CASE WHEN meta_key = '_billing_email' THEN meta_value END)      AS _billing_email,
        MAX(CASE WHEN meta_key = '_billing_country' THEN meta_value END)    AS _billing_country,
        MAX(CASE WHEN meta_key = '_order_total' THEN meta_value END)        AS order_total,
        MAX(CASE WHEN meta_key = '_order_tax' THEN meta_value END)          AS order_tax,
        MAX(CASE WHEN meta_key = '_paid_date' THEN meta_value END)          AS paid_date
    FROM
        wp_postmeta
    GROUP BY 
        post_id
) AS order_details
ON
    orders.order_id = order_details.order_id
LEFT JOIN (
    SELECT
        order_id,
        GROUP_CONCAT(order_item_name SEPARATOR ', ')           AS order_item_names,
        GROUP_CONCAT(order_items.order_item_id SEPARATOR ', ') AS order_item_ids,
        GROUP_CONCAT(order_item_type SEPARATOR ', ')           AS order_item_types,
        GROUP_CONCAT(productID SEPARATOR ', ')                 AS productIDs, -- asi je stejné jako order_item_ids, ale v query to máš, tak jsem to nechal i tady
        SUM(Qty)                                               AS Qty,
        SUM(lineTotal)                                         AS lineTotal,
        SUM(subtotal)                                          AS subtotal
    FROM
        wp_woocommerce_order_items AS order_items
    LEFT JOIN (
        SELECT
            order_item_id,
            MAX(CASE WHEN meta_key = '_product_id' THEN meta_value END)    AS productID,
            MAX(CASE WHEN meta_key = '_qty' THEN meta_value END)           AS Qty,
            MAX(CASE WHEN meta_key = '_line_total' THEN meta_value END)    AS lineTotal,
            MAX(CASE WHEN meta_key = '_line_subtotal' THEN meta_value END) AS subtotal
        FROM
            wp_woocommerce_order_itemmeta
        GROUP BY
            order_item_id
    ) AS order_items_details
    ON
        order_items.order_item_id = order_items_details.order_item_id
    WHERE
        order_item_type = 'line_item'
    GROUP BY
        order_id
) AS order_items_sumary
ON
    orders.order_id = order_items_sumary.order_id
WHERE
    _billing_email = 'jankohrasko@mojemail.sk'
ORDER BY
    `Qty`;
igid26
Profil
Keeehi:
ďakujem moc, funguje skvele.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0