WP tbl joins.. DBA'rs help

[cfU]

Am looking to return term_id, name and slug from wp_terms. These term_id's need to be associated to post_id's that reside in wp_post_meta where wp_postmeta.meta_key='wpb_post_views_count'

I also need those results ordered by : order by cast(wp_postmeta.meta_value as signed) desc;

just having problems associating back out... here are the tbls possibly involved

mysql> describe wp_postmeta;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+


mysql> describe wp_terms;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| term_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | MUL | | |
| slug | varchar(200) | NO | UNI | | |
| term_group | bigint(10) | NO | | 0 | |
+------------+---------------------+------+-----+---------+----------------+


mysql> describe wp_posts
-> ;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(20) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | longtext | NO | | NULL | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+

mysql> describe wp_term_relationships;
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| object_id | bigint(20) unsigned | NO | PRI | 0 | |
| term_taxonomy_id | bigint(20) unsigned | NO | PRI | 0 | |
| term_order | int(11) | NO | | 0 | |
+------------------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)



mysql> describe wp_term_taxonomy;
+------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+----------------+
| term_taxonomy_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| term_id | bigint(20) unsigned | NO | MUL | 0 | |
| taxonomy | varchar(32) | NO | MUL | | |
| description | longtext | NO | | NULL | |
| parent | bigint(20) unsigned | NO | | 0 | |
| count | bigint(20) | NO | | 0 | |
+------------------+---------------------+------+-----+---------+----------------+

[freecartoonporn]

try here
www.stackoverflow.com

[sarettah]

Put this together real quick so don't know if it is 100% correct or not:

select c.term_id, c.name as term_name, c.slug as term_slug from wp_posts a
inner join wp_term_relationships b on a.id=b.object_id
inner join wp_term_taxonomy c on b.term_taxonomy_id=c.term_taxonomy_id
inner join wp_terms d on c.term_id=d.term_id
inner join wp_post_meta e on a.id=e.post_id
where e.meta_key='wpb_post_views_count'
order by cast(e.meta_value as signed) desc

[cfU]

Originally Posted by sarettah

Put this together real quick so don't know if it is 100% correct or not:

select c.term_id, c.name as term_name, c.slug as term_slug from wp_posts a
inner join wp_term_relationships b on a.id=b.object_id
inner join wp_term_taxonomy c on b.term_taxonomy_id=c.term_taxonomy_id
inner join wp_terms d on c.term_id=d.term_id
inner join wp_post_meta e on a.id=e.post_id
where e.meta_key='wpb_post_views_count'
order by cast(e.meta_value as signed) desc

Awesome thank you!!!! Small changes here there.. but that was the connection I was looking for!! Thank you very much!!!

[fris]

curious why you are using a sql call rather than a wp query?

[sarettah]

Originally Posted by cfU

Awesome thank you!!!! Small changes here there.. but that was the connection I was looking for!! Thank you very much!!!

You're welcome.

Originally Posted by fris

curious why you are using a sql call rather than a wp query?

If you are asking me I just went into one of my wife's wp dbs to figure out the relationships. I am well versed in databases but not so much in wp.

If you are asking the OP, I will let him answer ;p

.

[fris]

Originally Posted by sarettah

You're welcome.



If you are asking me I just went into one of my wife's wp dbs to figure out the relationships. I am well versed in databases but not so much in wp.

If you are asking the OP, I will let him answer ;p

.

ya the original poster ;)