我在想是否有人能帮我解决这个问题。
Example Table ($tableTerms):
-----------------------------
| NAME | SLUG |
-----------------------------
| Dean | dean |
-----------------------------
| Football | football |
-----------------------------
我试图从表$tableterms的“name”列中获取一些数据。
在查询过程中,我将“slug”列调用,以便查找“dean”参加的正确“event_name”。然后在同一个查询中,我想调用列(“name”),但这次访问行“football”,这样我就可以看到“dean”参加了什么类型的活动。
我使用的是wordpress的术语和分类法,这就是为什么同一个表中有不同类型的数据。
我试着用左接来达到我的目标,但是我没有运气。在添加左连接和额外的select之前,查询已经运行,但现在需要额外的数据。
任何人的帮助都很好,谢谢!
院长。
// Look Up Events Attended
$tableTerms = $wpdb->prefix . "terms";
$tableTermTaxonomy = $wpdb->prefix . "term_taxonomy";
$tableTermRelationships = $wpdb->prefix . "term_relationships";
$tableEvents = $wpdb->prefix . "em_events";
$tableEventLocations = $wpdb->prefix . "em_locations";
$tableEventsMeta = $wpdb->prefix . "em_meta";
$slug = strtolower($firstName)."-".strtolower($lastName);
if(isset($memberID)) {
$eventsAttendedQuery = $wpdb->get_results("
SELECT
event_name,
event_start_date,
location_name,
tableTermsCategory.name
FROM
$tableTerms,
$tableTermTaxonomy,
$tableTermRelationships,
$tableEvents,
$tableEventLocations
LEFT JOIN
$tableTerms AS tableTermsCategory
ON tableTermsCategory.term_id = $tableTermTaxonomy.term_id AND
$tableTermTaxonomy.taxonomy = 'event-categories' AND
$tableTermTaxonomy.term_taxonomy_id = $tableTermRelationships.term_taxonomy_id AND
$tableTermRelationships.object_id = $tableEvents.post_id
WHERE
YEAR(event_start_date) = $year AND
slug = '$slug' AND
$tableTerms.term_id = $tableTermTaxonomy.term_id AND
$tableTermTaxonomy.taxonomy = 'event-tags' AND
$tableTermRelationships.term_taxonomy_id = $tableTermTaxonomy.term_taxonomy_id AND
$tableEvents.post_id = $tableTermRelationships.object_id AND
$tableEvents.location_id = $tableEventLocations.location_id
ORDER BY event_start_date ASC
");
}
最佳答案
查看您的查询,发现JOINS
完全是weired。如下修改查询
SELECT
event_name,
event_start_date,
location_name,
tableTermsCategory.name
FROM
$tableTerms LEFT JOIN $tableTermTaxonomy ON $tableTerms.term_id = $tableTermTaxonomy.term_id
AND $tableTermTaxonomy.taxonomy IN ('event-categories', 'event-tags')
AND $tableTermTaxonomy.term_taxonomy_id = $tableTermRelationships.term_taxonomy_id
LEFT JOIN $tableTermRelationships ON $tableTermRelationships.term_taxonomy_id = $tableTermTaxonomy.term_taxonomy_id
LEFT JOIN $tableEvents ON $tableEvents.post_id = $tableTermRelationships.object_id
LEFT JOIN $tableEventLocations ON $tableEvents.location_id = $tableEventLocations.location_id
WHERE
YEAR(event_start_date) = $year AND
slug = '$slug'
ORDER BY event_start_date;