Csoncatenating row values by groups

 
  • Apr 19, 2020
  • 0 views

usecase_1

TASK USECASES:

  1. Empty values created due to large chunk of description
  2. Grouping multiple intersts

Query User Intersts

SELECT
    u.user_email AS 'Email',
    u.display_name,
    SUBSTRING_INDEX(u.display_name, ' ', 1) AS 'First',
    SUBSTRING_INDEX(u.display_name, ' ', -1) AS 'Last',
    wp.post_title AS 'Company',
    mp.meta_value AS 'Job Title',
    a.name AS 'Interets'
FROM 
    (SELECT id, name
    FROM posts_terms_authors
    WHERE posts_terms_authors.taxonomy='category' ) AS a
LEFT JOIN 
    (SELECT id, name
    FROM posts_terms_authors
    WHERE posts_terms_authors.taxonomy='author') AS b
    ON a.id = b.id
LEFT JOIN wp_users u ON u.user_login = b.name
LEFT JOIN wp_usermeta mp ON u.id = mp.user_id
LEFT JOIN wp_usermeta mc ON u.id = mc.user_id
LEFT JOIN wp_posts wp ON wp.ID = mc.meta_value
WHERE mp.meta_key LIKE 'position'
  AND mc.meta_key LIKE 'company';

Concatenating user intersts

CREATE OR REPLACE VIEW user_intersts_grouped AS
SELECT Email,First,Last,
       Company,'Job Title',  
       group_concat( distinct intersts) AS 'Intersts' 
FRROM user_intersts
GROUP BY Email,First,Last,Company, Job Title;

Get users with no interests

SELECT * FROM wp_users U 
WHERE U.ID NOT IN (
    SELECT ID FROM wp_users a
    JOIN user_intersts_grouped b 
    ON a.user_email = b.Email)

Check total number of records

SELECT count(*) FROM ( 
    SELECT u.ID, u.user_email AS 'Email',
       u.display_name,
       SUBSTRING_INDEX(u.display_name, ' ', 1) AS 'First',
       SUBSTRING_INDEX(u.display_name, ' ', -1) AS 'Last',
       wp.post_title AS 'Company',
       mp.meta_value AS 'Job Title'
    FROM  wp_users u
    LEFT JOIN wp_usermeta mp ON u.id = mp.user_id
    LEFT JOIN wp_usermeta mc ON u.id = mc.user_id
    LEFT JOIN wp_posts wp ON wp.ID = mc.meta_value
    WHERE mp.meta_key like 'position' and mc.meta_key like 'company'
    AND u.ID NOT IN (SELECT ID FROM wp_users a
    JOIN user_intersts_grouped b ON a.user_email = b.Email)) T;

Create view from final table

CREATE OR REPLACE VIEW users_with_no_interetsts AS
SELECT u.user_email AS 'Email',
       SUBSTRING_INDEX(u.display_name, ' ', 1) AS 'First',
       SUBSTRING_INDEX(u.display_name, ' ', -1) AS 'Last',
       wp.post_title AS 'Company',
       mp.meta_value AS 'Job Title'
FROM  wp_users u
LEFT JOIN wp_usermeta mp ON u.id = mp.user_id
LEFT JOIN wp_usermeta mc ON u.id = mc.user_id
LEFT JOIN wp_posts wp ON wp.ID = mc.meta_value
WHERE mp.meta_key like 'position' and mc.meta_key like 'company'
AND u.ID NOT IN (SELECT ID FROM wp_users a
JOIN user_interests_grouped b ON a.user_email = b.Email)

Switched to user and select all the categories categories:

a:10:{
i:0;s:2:"56";
i:1;s:2:"24";
i:2;s:2:"43";
i:3;s:2:"23";
i:4;s:2:"40";
i:5;s:2:"37";
i:6;s:2:"52";
i:7;s:3:"185";
i:8;s:2:"54";
i:9;s:2:"38";}

CREATE OR REPLACE VIEW categories AS
SELECT * FROM wp_terms WHERE term_id IN (56,24,43,23,40,37,52,185,54,38)
ORDER BY name;

Finally populating user interests (in case views got deleted or not exist on staging/prod, the injection query will use the full sub query. )

global $wpdb;
$sql="select t.ID, GROUP_CONCAT(DISTINCT catagory) AS 'Interests' FROM( SELECT u.ID, c.term_id AS catagory FROM ( select u.user_email as 'Email', u.display_name, SUBSTRING_INDEX(u.display_name, ' ', 1) as 'First', SUBSTRING_INDEX(u.display_name, ' ', -1) as 'Last', wp.post_title as 'Company', mp.meta_value as 'Job Title', a.name as 'Interets' from ( select id, name from posts_terms_authors where posts_terms_authors.taxonomy='category') as a left join (select id, name from posts_terms_authors where posts_terms_authors.taxonomy='author') as b on a.id = b.id left join wp_users u on u.user_login = b.name left join wp_usermeta mp on u.id = mp.user_id left join wp_usermeta mc on u.id = mc.user_id left join wp_posts wp on wp.ID = mc.meta_value where mp.meta_key like 'position' and mc.meta_key like 'company' and a.name !='Uncategorized' ) i JOIN categories c ON i.Interets = c.name LEFT JOIN wp_users u ON u.display_name = i.display_name ) t GROUP BY t.ID;";
$results = $wpdb->get_results($sql);

foreach ( $results as $result ) {
	update_user_meta( $result->ID, 'profile_interests', explode(',', $result->Interests) );
}

It’s probably better to convert to procedure…