Join this to that and the other.

[ElmoTheClown]

Gah.. Approaching suicidal..

 

SELECT p.ID, p.post_content, p.post_title, p.post_name, p.guid, m1.meta_value AS date, m2.meta_value AS alt_dates, term1.name as charter, term2.name as itin
FROM $wpdb->posts p 
JOIN $wpdb->postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'date') 
LEFT JOIN $wpdb->postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'alt_dates') 
JOIN $wpdb->postmeta m3 ON (p.ID = m3.post_id AND m3.meta_key = 'number_days') 
JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id) 

MORE TO JOIN HERE

WHERE p.post_type = 'cruise' AND p.post_status = 'publish' AND tr.term_taxonomy_id={$term_tax[0]}";

 

All goes according to plan... but I have more to join, and I can't seem to get it.

 

I need to get the charter name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = 0 AND terms.term_id=$term_tax[0]

AND

I need to get the itin name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = $term_tax[0]  AND terms.term_id=$term_tax[0]

 

I would appreciate any help!

[ElmoTheClown]

Server version: 5.1.56

 

The query works great without the "MORE TO JOIN HERE"

 

I honestly can;t seem to wrap my head around the rest.  I know what I want it t ot tell me...  Just not how to tell it to tell me.

[fenway]

Then it sounds like you need to join in a table twice.

 

Show us what the actual query is.

[ElmoTheClown]

Currently, the query is:

SELECT p.ID, p.post_content, p.post_title, p.post_name, p.guid, m1.meta_value AS date, m2.meta_value AS alt_dates, term1.name as charter, term2.name as itin
FROM wp_posts p 
JOIN wp_postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'date') 
LEFT JOIN wp_postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'alt_dates') 
JOIN wp_postmeta m3 ON (p.ID = m3.post_id AND m3.meta_key = 'number_days') 
JOIN wp_term_relationships tr ON (p.ID = tr.object_id) 

$term_tax[0] is an integer representing a page(post.ID).

 

I get how to join the same table more than once, but I can't get my head around joining two tables twice.

 

need to get the charter name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = 0 AND terms.term_id=$term_tax[0]

AND

I need to get the itin name (terms.name) from somehow making terms.term_id=term_taxonomy.term_id AND term_taxonomy.parent = $term_tax[0]  AND terms.term_id=$term_tax[0]

 

Here is a breakdown of the database with relevant columns:

CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_content` longtext COLLATE utf8_unicode_ci NOT NULL,
`post_title` text COLLATE utf8_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'publish',
`post_name` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`guid` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`post_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`),
KEY `type_status_date` (`post_type`,`post_status`,`ID`),
) ENGINE=MyISAM AUTO_INCREMENT=169 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8_unicode_ci,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=576 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `wp_terms` (
`term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`term_id`),
KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `wp_term_taxonomy` (
`term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`term_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`taxonomy` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`parent` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

Thank you for your help.

 

 

[ElmoTheClown]

                SELECT p.ID, p.post_content, p.post_title, p.post_name, p.guid, m1.meta_value AS cruise_date, m2.meta_value AS alt_dates, m3.meta_value as days, t1.name as company, t2.name as asset 
                FROM wp_posts p 
                JOIN wp_postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'cruise_date') 
                LEFT JOIN wp_postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'alt_dates') 
                JOIN wp_postmeta m3 ON (p.ID = m3.post_id AND m3.meta_key = 'number_days') 
                JOIN wp_term_relationships tr1 ON p.ID = tr1.object_id 
                JOIN wp_term_taxonomy tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id 
                JOIN wp_terms t1 ON tt1.term_id = t1.term_id 
                JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id 
                JOIN Kterm_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id 
                JOIN wp_terms t2 ON tt2.term_id = t2.term_id 
                WHERE p.post_type = 'cruise' AND p.post_status = 'publish' AND tr1.term_taxonomy_id={$term_tax[0]} AND tt2.parent = {$term_tax[0]}";

It works.  There should only be 1 company, and 1 asset per post.  Imagine things will be broken if more end up in.  Is there a better way?

 

Seems to me to be a pretty stinking big query.. but I don't know. 

 

What do you think?

[fenway]

Yeah, it's not pretty -- but there is little you can do if you want all the results co-dependent. 

[ElmoTheClown]

Guess I am stuck with it. 

 

With today's technology, could databases become a bit less relationship driven, and more use be made of storage?

 

I could easily change the

       JOIN wp_term_relationships tr1 ON p.ID = tr1.object_id 
                JOIN wp_term_taxonomy tt1 ON tr1.term_taxonomy_id = tt1.term_taxonomy_id 
                JOIN wp_terms t1 ON tt1.term_id = t1.term_id 
                JOIN wp_term_relationships tr2 ON p.ID = tr2.object_id 
                JOIN term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id 
                JOIN wp_terms t2 ON tt2.term_id = t2.term_id 
to
[code]
               JOIN wp_postmeta m4 ON (p.ID = m4.post_id AND m4.meta_key = 'company') 
               JOIN wp_postmeta m5 ON (p.ID = m4.post_id AND m4.meta_key = 'asset') 

This would tax the storage more, but processing and memory less?

[fenway]

Sorry, I don't follow...?

[ElmoTheClown]

Sorry, I don't follow...?

That's because of my awesome 'splainin' skills.

I was wondering if it would be better to use the "meta" table, and store company and asset names in actual text (Delta Airlines, Delta Adventures), which although would require roughly 10 to 20 times more disk space....  As opposed to the three tables (terms, term taxonomies, and term relationships), which takes more storage, but also much more processing power (6 join statements instead of 2).

 

 

 

[fenway]

It's always better to start off normalized.

[ElmoTheClown]

Thank you