2

WordPress considers both a post and the post's featured image as two separate posts, which seems to make it impossible to create a SELECT query putting the post and post's image URL into the same row, like this:

post_id  imageURL
100      [URL]

How do you merge two rows in a MySQL table which have no column with a common value? The value in one column is associated with the value in a different column.

meta_id   post_id    meta_key          meta_value  
100       250        _thumbnail_id     101
101       255       _wp_attached_file  [URL]

...to get a result like this

post_id  imageURL
100      [URL]

WordPress seems to have changed it database structure since this question was answered on this site.

I am trying to understand the principle in MySQL of taking the value from one row and showing it in another row, when "Group by" is not available.

Many hours later of watching videos, trial and error, reading answers here, this query gets me only half-way to solving it, but either the Post ID or the Image URL are always missing. I cannot merge two rows to unite the Post with its image URL.

SELECT * FROM `wp_postmeta`
INNER JOIN wp_posts on wp_posts.id = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = "_wp_attached_file"
ORDER BY `wp_postmeta`.`post_id` DESC

Please help. There may be a simple bit of syntax to achieve this, which I am missing. The query is coming in via the WP API, so using raw SQL queries, not the functions available inside WordPress.

Raunak Gupta
  • 10,412
  • 3
  • 58
  • 97
rikuwolf
  • 103
  • 2
  • 10
  • You only showed the `wp_postmeta` table. What's the `wp_posts` table? Are you selecting `meta_id` as `post_id`? – aaron Dec 05 '17 at 00:36
  • Good question @aaron. The wp_posts table does contain all images related to a WordPress page, but as far as I can tell, the wp_posts table gives no way to identify the featured image (main image) for the post. It lists all related images (which it also considers posts), regardless of whether the image is embedded in the body, or whether it's the main "featured" image. I think you put your finger on it with your second question. See my response to raunak-gupta below. – rikuwolf Dec 06 '17 at 12:08
  • I don't see your response. – aaron Dec 06 '17 at 12:18

2 Answers2

7

You are trying to get the data based on the value of post_id and meta_value, so this can be achieved by using Self JOIN

Here is the sample working MySQL query

SELECT p.id AS post_id, pm2.meta_value AS URL
FROM `wp_posts` AS p
INNER JOIN `wp_postmeta` AS pm1 ON p.id = pm1.post_id
INNER JOIN `wp_postmeta` AS pm2 ON pm1.meta_value = pm2.post_id
AND pm2.meta_key = '_wp_attached_file'
AND pm1.meta_key = '_thumbnail_id'
ORDER BY p.id DESC;

Hope this helps!

Raunak Gupta
  • 10,412
  • 3
  • 58
  • 97
  • Thank you Raunak Gupta. That's perfect. Yes, problem solved. The SQL query concept I was missing was the self join. And the practical problem is solved nicely by this query. – rikuwolf Dec 06 '17 at 14:05
  • @rikuwolf: I'm glad that it helped you and please don't forget to accept my answer if it's answering your question. See [What should I do when someone answers my question?](http://stackoverflow.com/help/someone-answers). – Raunak Gupta Dec 06 '17 at 14:13
  • Ah, yes. First time getting an answer. Now accepted. Thanks again! – rikuwolf Dec 07 '17 at 13:54
2

The answer came from Raunak Gupta, so thanks to Raunak. Here's the code which solved it.

SELECT p.id AS post_id, p.post_title, concat('https://www.ourdomain.com','/wp-content/uploads/',pm2.meta_value) AS URL
FROM `wp_posts` AS p
INNER JOIN `wp_postmeta` AS pm1 ON p.id = pm1.post_id
INNER JOIN `wp_postmeta` AS pm2 ON pm1.meta_value = pm2.post_id
AND pm2.meta_key = '_wp_attached_file'
AND pm1.meta_key = '_thumbnail_id'
ORDER BY p.id DESC

The key principle needed to create this MySQL query was the self join.

rikuwolf
  • 103
  • 2
  • 10