SQL Query prepend output-CodePudding

I have completely hacked together this SQL query from a few different sources, it's for a WordPress database with subscriptions:

SELECT
  p.ID as 'Subscription ID',
  p.post_status as 'Status',
  pm1.meta_value as 'Billing First Name',
  pm2.meta_value as 'Billing Last Name',
  pm3.meta_value as 'Billing Email',
  oitems.order_item_name as 'Product',
  pm4.meta_value as 'Order Total',
  pm5.meta_value as 'D.O.B',
  pm6.meta_value as 'Membership Numer',
  pm7.meta_value as 'Shirt Size',
  pm8.meta_value as 'Edit User',
  pm20.meta_value as 'Next Payment Date'
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
INNER JOIN wp_postmeta pm2 ON pm2.post_id = p.ID
INNER JOIN wp_postmeta pm3 ON pm3.post_id = p.ID
INNER JOIN wp_postmeta pm4 ON pm4.post_id = p.ID
INNER JOIN wp_postmeta pm5 ON pm5.post_id = p.ID
INNER JOIN wp_postmeta pm6 ON pm6.post_id = p.ID
INNER JOIN wp_postmeta pm7 ON pm7.post_id = p.ID
INNER JOIN wp_postmeta pm8 ON pm8.post_id = p.ID
INNER JOIN wp_postmeta pm20 ON pm20.post_id = p.ID
INNER JOIN wp_woocommerce_order_items oitems ON oitems.order_id = p.ID
WHERE
  post_type = 'shop_subscription'
  AND pm1.meta_key = '_billing_first_name'
  AND pm2.meta_key = '_billing_last_name'
  AND pm3.meta_key = '_billing_email'
  AND pm4.meta_key = '_order_total'
  AND pm5.meta_key = 'birth_details'
  AND pm6.meta_key = 'membership_number'
  AND pm7.meta_key = 'shirt_size'
  AND pm8.meta_key = '_customer_user'
  AND pm20.meta_key = '_schedule_next_payment'
  AND oitems.order_item_type = 'line_item'
ORDER BY p.ID DESC

Everything is returned as expected but I have hit a bit of a wall, pm.8 returns a simple integer which is the user ID, I want to prepend some text to the output of the integer so it returns something like this /wp-admin/user-edit.php?user_id=pm.8

I don't want to edit anything in the DB just change the SQL output. Can anyone help on this, I'm proving to be pretty clueless on anything beyond the basics of SQL queries.

Edit based on help from @Caius
Pivot Operation > That explains why records were missing, I much prefer the pattern you have shown. Here is my attempt.

SELECT
    p.ID as "Member ID",
    p.post_date as "Last Order",
    p.post_status as "Status",
    MAX( CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) as "Email",
    MAX( CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) as "First Name",
    MAX( CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) as "Last Name",
    MAX( CASE WHEN pm.meta_key = '_billing_address_1' THEN pm.meta_value END) as "Billing Address",
    MAX( CASE WHEN pm.meta_key = 'membership_number' THEN pm.meta_value END) as "Member Number",
    MAX( CASE WHEN pm.meta_key = 'birth_details' THEN pm.meta_value END) as "D.O.B.",
    MAX( CASE WHEN pm.meta_key = '_customer_user' THEN pm.meta_value END) as "User ID.",
    ( SELECT CONCAT('/wp-admin/user-edit.php?user_id=', pm.meta_key = '_customer_user') ) as "Edit",
    ( SELECT GROUP_CONCAT( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as "Member Type"
FROM
    wp_posts p
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
WHERE
    post_type = 'shop_subscription'
GROUP BY
    p.ID

Everything returns as expected but I can't work out how to return the correct '_customer_user' in the concat?

CodePudding user response：

The standard command for it in most databases is:

CONCAT('/wp-admin/user-edit.php?user_id=', <YOUR_PM_8_THING>)

I couldn't quite work out what you were talking about with pm.8 - it's not an alias in your query, and you've put pm.8 into your literal string as literally pm.8, but I'm assuming you have something like pm8.user_id which is an integer like 123, and you want a string like ...?user_id=123, so it would hence be CONCAT('...?user_id=', pm8.user_id)

There are other ways of concatenating strings and integers together, but the syntax tends to vary between databases; Oracle uses ||, SQLServer uses etc - these variants sometimes treat null as a blank string, other times cause the whole expression to become null. CONCAT generally offers a more consistent concatenating behavior, and most DB allow many arguments to it, so you can e.g. call CONCAT('a', 12, 'c', d.e_f) but you do also find databases that only allow 2 arguments to CONCAT, which makes for a more wearisome stacking of multiple concat calls CONCAT(CONCAT('a', 12), 'b')...

By the way, what you've done there is a pivot operation but I did want to point out that it will only work if all the data is present - as soon as any one of those fields mentioned in any join is absent the whole row disappears

It can be more robust to adopt a pattern of:

SELECT 
  p.ID as "Subscription ID",
  p.post_status as "Status",
  MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) as "Billing First Name",
  MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) as "Billing Last Name",
  ...
FROM
  wp_posts p
  INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
GROUP BY
  p.ID, p.post_status

If you want to see it in action take out the Group By and the Max clause

Everything returns as expected but I can't work out how to return the correct '_customer_user' in the concat?

The "max case when blah" is the expression that gives you the value, so you put it inside the concat where you want the value to appear

SELECT
    MAX...,
    MAX...,
    CONCAT('blah url', MAX(CASE WHEN pm.meta_key = '_customer_user' THEN pm.meta_value END))