[WordPress] SQL クエリ: グループの Concat 子供親を持つ要素

スポンサーリンク

Question

私は投稿とその内容の一覧をエクスポートするワードプレスのデータベースを照会しようとしています。すべての作業が、添付されているイメージを含める方法を把握できません。私は確信していない、同じテーブル内の親を子供たちに関連付ける方法

親識別子は、ID と呼ばれる列で、post_parent と呼ばれる列で子の識別子です。連結しようとしている値が (まさに私が必要) イメージに完全な URL が含まれている guid と呼ばれる列

wp_posts テーブル構造:

ID   post_parent   guid          post_type
1     0             http://...    attachment
2     1             http://...    attachment
3     1             http://...    attachment
4     1             http://...    attachment

立っているここで私のクエリは:

SELECT DISTINCT
post_title
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-year' AND wap_postmeta.post_id = wap_posts.ID) AS "Year"
,(SELECT group_concat(wap_terms.name separator ', ') 
FROM wap_terms
INNER JOIN wap_term_taxonomy ON wap_terms.term_id = wap_term_taxonomy.term_id
INNER JOIN wap_term_relationships wpr ON wpr.term_taxonomy_id = wap_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'car-manufacturer' AND wap_posts.ID = wpr.object_id
) AS "Manufacturer"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-model' AND wap_postmeta.post_id = wap_posts.ID) AS "Model"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-trim' AND wap_postmeta.post_id = wap_posts.ID) AS "Trim"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-vin' AND wap_postmeta.post_id = wap_posts.ID) AS "VIN"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-odometer' AND wap_postmeta.post_id = wap_posts.ID) AS "Odometer"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-price' AND wap_postmeta.post_id = wap_posts.ID) AS "Price"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-extcolor' AND wap_postmeta.post_id = wap_posts.ID) AS "Exterior Color"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-intcolor' AND wap_postmeta.post_id = wap_posts.ID) AS "Interior Color"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-transmission' AND wap_postmeta.post_id = wap_posts.ID) AS "Transmission"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-engine' AND wap_postmeta.post_id = wap_posts.ID) AS "Engine"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-fuel' AND wap_postmeta.post_id = wap_posts.ID) AS "Fuel Type"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-drivetrain' AND wap_postmeta.post_id = wap_posts.ID) AS "Drivetrain"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-doors' AND wap_postmeta.post_id = wap_posts.ID) AS "car-doors"
,(SELECT meta_value FROM wap_postmeta WHERE wap_postmeta.meta_key = 'car-stockno' AND wap_postmeta.post_id = wap_posts.ID) AS "Stock #"
,(SELECT group_concat(wap_terms.name separator ', ') 
FROM wap_terms
INNER JOIN wap_term_taxonomy ON wap_terms.term_id = wap_term_taxonomy.term_id
INNER JOIN wap_term_relationships wpr ON wpr.term_taxonomy_id = wap_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'car-feature' AND wap_posts.ID = wpr.object_id
) AS "Features"
,(SELECT group_concat(wap_terms.name separator ', ') 
FROM wap_terms
INNER JOIN wap_term_taxonomy ON wap_terms.term_id = wap_term_taxonomy.term_id
INNER JOIN wap_term_relationships wpr ON wpr.term_taxonomy_id = wap_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'car-body-type' AND wap_posts.ID = wpr.object_id
) AS "Body Type"

-- MY PROBLEM STARTS HERE
,(SELECT group_concat(wap_posts.guid separator ', ') 
FROM wap_posts
WHERE wap_posts.post_type = 'attachment' AND wap_posts.post_parent = wap_posts.ID
) AS "Images"
-- MY PROBLEM ENDS HERE

FROM wap_posts
WHERE post_type = 'vehicle' AND post_status = 'publish'
ORDER BY
post_title

添付ファイルの post_parent は決して ID. それ自身と一致するために NULL を返しますこれを実現それは独自の ID ですよりもむしろ私は親の ID を持つ post_parent 値と一致するクエリを確認する方法を知ってしないでください

うまくいけば、私は十分な情報を掲載している、私は含める必要があります他のデータがあるかどうか、私に教えてください。読んでいただきありがとうございます !

最高のよろしく、ジェイ ・

答え

(いないすべての投稿は添付ファイルを持っていると仮定して)、必要な結果を生成するために同じテーブルに、

left join

を行うことができます

select     p.Id,group_concat(c.guid separator ', ') guids 
from wap_posts p
left join wap_posts c on (c.post_parent = p.parent)

http://stackoverflow.com/questions/27027881/sql-query-group-concat-children-elements-with-parent

スポンサーリンク

Leave a Reply