Basic SQL queries are no problem for me, but the Joins and complex ORDER BY isn’t easy for me.
There’s 4 tables, and they are part of a Wordpress setup.
What I’m trying to do is summed up in 4 SELECT statements:
SELECT guid FROM wp_posts WHERE id=(JOIN category_inclusions) AS main_query ORDER BY (JOIN lineup) ASC
SELECT term_id FROM wp_terms WHERE slug=‘home-slideshow-mouseovers’ AS category_ids
SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(JOIN category_ids) AS category_inclusions
SELECT meta_value FROM wp_postmeta WHERE metakey=‘slot’ AND post_id=‘(JOIN category_inclusions)’ AS lineup
The summation of it is this:
QUERY1’s IDs match where=QUERY3’s category_ids match where QUERY2’s rows include “home-slideshow-mouseovers”, and sort it all according to what “slot” value QUERY1’s IDs have in the “wp_postmeta” table.
It’s complicated, but seems perfect for some good JOIN syntax. I’ve played w/different query creators, but it’s a tough task even knowing what should take priority.
To simplify this up a bit & just to get me some traction on this, say I removed a condition.
I’m just looking for the JOIN syntax to do this:
SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(SELECT term_id FROM wp_terms WHERE slug=‘home-slideshow-mouseovers’) AS category_inclusions
And then to ORDER BY, then the category_inclusions have to be JOIN’ed somehow.
ORDER BY (SELECT meta_value FROM wp_postmeta WHERE metakey=‘slot’ AND post_id=‘(JOIN category_inclusions)’ AS lineup
When I look at different examples, the queries get tricky for me when “ON” is used. The masterminds of SQL would think this is cake, the same as how I could point out the best practices to use w/PHP, so the help is appreciated.
the reason your first post went unanswered, and the second one is likely to have the same result, is because it’s not at all clear what you’re trying to do
nor did you explain what the tables contain, or how they are related
Thanks r937. I’m trying to explain this the best way that I can, so here’s putting it in more conversational English.
There’s 4 tables:
“wp_posts” contains rows of content.
“wp_terms” is a table of categories.
“wp_term_relationships” ties categories by their id in wp_terms to their object_id in wp_posts.
“wp_postmeta” is a table containing custom fields and their values, tied to a record in wp_posts by id.
“wp_posts”
id, content
“wp_terms”
id, category_name
“wp_term_relationships” ties posts to categories
object_id (essentially wp_posts.id), category_id (essentially wp_terms.id)
So what I’m trying to do is tie everything together, and putting it in simple english, “I’m trying to select all posts of a certain category and order them according to a custom field called ‘slot’.”
Which would come up like this:
SELECT id, content FROM wp_posts WHERE id=(JOIN: SELECT object_id’s FROM wp_term_relationships WHERE category_id=(JOIN: SELECT id’s FROM wp_terms WHERE category_name=“home”)) ORDER BY (JOIN SELECT wp_postmeta.fieldvalue WHERE wp_postmeta.content_id=wp_posts.id AND wp_postmeta.fieldname=“slot”)
It just looks like a mess when it’s typed out like that.
SELECT
p.id
,p.content
FROM
wp_posts p
INNER
JOIN
wp_term_relationships t2p
ON
p.id = t2p.object_id
INNER
JOIN
wp_terms t
ON
t2p.category_id = t.id
LEFT OUTER
JOIN
wp_postmeta m
ON
p.id = m.content_id
AND
m.custom_fieldname = 'slot'
WHERE
t.category_name = 'home'
ORDER
BY
m.custom_fieldvalue
SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = ‘home-slideshow-mouseovers’ AND wp_postmeta.meta_key = ‘slot’
ORDER BY
wp_postmeta.meta_value
Thank you for your help & for steering me in the right direction. I appreciate it hugely.
Thanks r937, so far it’s working great. I’m playing around with where to drop the final sorting, and what I’ve come up with
SELECT wp_posts.id, wp_posts.content
FROM wp_terms
INNER
JOIN wp_term_relationships
ON wp_term_relationships.category_id = wp_terms.id
INNER
JOIN wp_posts
ON wp_posts.id = wp_term_relationships.object_id
WHERE wp_terms.category_name = ‘home’ INNER
JOIN wp_postmeta
ON wp_postmeta.content_id = wp_posts.ID WHERE wp_postmeta.custom_fieldname = ‘slot’
ORDER BY wp_postmeta.custom_fieldvalue ASC
Swapping that final “WHERE” with “AND” doesn’t do the trick either. What would I be better off doing to rethink the sorting logic?
...
INNER
JOIN wp_postmeta
ON wp_postmeta.post_id = wp_posts.ID
[COLOR="Blue"][B]AND [/B]wp_postmeta.meta_key = 'slot'[/COLOR]
WHERE wp_terms.slug = 'home-slideshow-mouseovers'
ORDER
BY wp_postmeta.meta_value
also, please remove the parentheses you added to the ON conditions
Thanks r937. Now what if I wanted to grab 2 values from the wp_postmeta table, both with the same post_id, but different meta_key.
Such as
SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value as meta_value1
wp_postmeta.meta_value as meta_value2
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = ‘home-slideshow-mouseovers’ AND meta_value1 = ‘slot’ and meta_value2 = ‘Read More Link’
ORDER BY
meta_value1
It’d seem to me that iterating through that result using mysql_result(“query”, $x, “meta_value1”) to spit out the ‘slot’ value and mysql_result(“query”, $x, “meta_value2”) to spit out the ‘Read More Link’ value would be logical. Does it seem like there would be any problem or smarter way to do this, to you?
I have been working on using a JOIN to create a pool of data across 3 different tables, and to sort it according to “slot” values in another table. The query works great. Now I’m looking to return data in the rows “wp_postmeta.meta_value” as some sort of alias that I can call later when I iterate through the result set.
SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = ‘home-slideshow-mouseovers’ AND wp_postmeta.meta_value
= ‘slot’
ORDER BY
wp_postmeta.meta_value
^ The above works fine.
It’s just that the wp_postmeta table is going to contain data like this:
Table wp_postmeta
post_id meta_value meta_key
400 slot 1
401 slot 3
402 slot 2
400 Read More /news/400_1/
401 Read More /news/401_1/
402 Read More /news/402_1/
And I would like to query the wp_postmeta table for the meta_key under the meta_value of “Read More” while I query it for “slot”.
I’ve tried designating the different value as some sort of alias, like below:
SELECT
wp_posts.ID,
wp_posts.post_content,
wp_postmeta.meta_value as meta_value1
wp_postmeta.meta_value as meta_value2
FROM
wp_terms
INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
WHERE
wp_terms.slug = ‘home-slideshow-mouseovers’ AND meta_value1 = ‘slot’ and meta_value2 = ‘Read More Link’
ORDER BY
meta_value1
It’s just that something’s off as far as my attempts at aliasing (in bold).
SELECT wp_posts.ID
, wp_posts.post_content
, [COLOR="Red"]meta1.meta_value as meta_value1[/COLOR]
, [COLOR="blue"]meta2.meta_value as meta_value2[/COLOR]
FROM wp_terms
INNER
JOIN wp_term_relationships
ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
INNER
JOIN wp_posts
ON wp_posts.ID = wp_term_relationships.object_id
[COLOR="Red"]LEFT OUTER
JOIN wp_postmeta AS meta1
ON meta1.post_id = wp_posts.ID
AND meta1.meta_key = 'slot'[/COLOR]
[COLOR="Blue"]LEFT OUTER
JOIN wp_postmeta AS meta2
ON meta2.post_id = wp_posts.ID
AND meta2.meta_key = 'Read More Link'[/COLOR]
WHERE wp_terms.slug = 'home-slideshow-mouseovers'
i made them LEFT OUTER JOINs as i wasn’t sure if you wanted them mandatory… change to INNER joins if you do
i also took off your ORDER BY clause because it no longer made sense, feel free to add it back with the appropriate column(s)
