使用具有多个术语的MYSQL在wordpress中重新索引自定义表

Question

I am currently working on re-indexing a custom table in my Wordpress database. I am collecting the data values sucessfully, however I am struggling to collect all $wpdb->terms.

Certain posts have more than one term associated with them, for example I have a custom post type (stores) and within this a custom taxonomy (products-sold) and within this custom taxonomy I have a list of 4 terms (Term A, Term B, Term C, Term D).

I have managed to fill the new database with the information needed except Colomn "post_terms", the first term gets added but I need to add the other terms associated (if they exist) so that the cell displays each slug seperated by a comma (slug-A, slug-B).

            $sql = "";
            $sql .= "INSERT INTO $this->tablename (`post_id`, `post_type`, `post_terms`, `lat`, `lng`) ";
            $sql .= "SELECT * FROM (";
                $sql .= "
                    SELECT
                        `$wpdb->posts`.`ID` AS `post_id`,
                        `$wpdb->posts`.`post_type`,
                        `$wpdb->terms`.`slug` AS `post_terms`,
                        `lat_field`.`meta_value` AS `lat`,
                        `lng_field`.`meta_value` AS `lng`
                    FROM
                        `$wpdb->posts`
                        INNER JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
                        INNER JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
                        INNER JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
                        LEFT JOIN `$wpdb->postmeta` AS `lat_field` ON (`$wpdb->posts`.`ID` = `lat_field`.`post_id` AND `lat_field`.`meta_key` IN ('".implode('\', \'',$this->keys['lat'])."'))
                        LEFT JOIN `$wpdb->postmeta` AS `lng_field` ON (`$wpdb->posts`.`ID` = `lng_field`.`post_id` AND `lng_field`.`meta_key` IN ('".implode('\', \'',$this->keys['lng'])."'))
                    WHERE `$wpdb->posts`.`post_status` = 'publish' AND (`lat_field`.`meta_value` IS NOT NULL OR `lng_field`.`meta_value` IS NOT NULL) AND " . $wpdb->term_taxonomy . ".taxonomy = 'products-sold'
                ";

            $sql .= ") AS `t`";
            $sql .= " ON DUPLICATE KEY UPDATE `lat` = VALUES(`lat`), `lng` = VALUES(`lng`)";
            $wpdb->query($sql);

This is the working code I have above, any help would be appreciated.

Answer 1

No time for a long, exasperation answer ....

You'll want to:

GROUP_CONCAT(slug, ',')

and add a GROUP BY post_id

This will show all slugs concatenated for the row and separated by a comma. These slugs will be matched and grouped on post_id. Where you should have a distinct post_id entity per result.