Query from WordPress

[Marzio]

I have the table wp_posts and the table wp_postmeta

[Marzio]

From the table “wp_postmeta” I need to extract only the records combined with the table “wp_posts” that have the field “meta_key” equal to “_wp_attached_file”, leaving out the others

[patrick]

That will just work fine if you move the wp_postmeta.meta_key = '_wp_attached_file' to the where condition.

Query will become something like:

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
  ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_postmeta.meta_key = '_wp_attached_file'
ORDER BY wp_posts.post_date DESC

[Marzio]

I had already tried this solution but unfortunately it does not work and I think it is impossible that it can work this way.
The problem is that every record in the “wp_posts” table should have only one record (and not 2 records) associated with the “wp_postmeta” table.
In the table “wp_postmeta” there are 2 or more records associated to the post, but only one is to be associated to obtain the name of the image.
The name is present in the table “wp_postmeta”, in the “meta_value” field, but only if the record that has the field “meta_key” equal to “_wp_attached_file”
I hope I have explained the problem well

[patrick]

But that is exactly what the query does, by adding the wp_postmeta.meta_key = '_wp_attached_file' to the where conditions it filters out all the records that doesn’t have _wp_attached_file as meta_key, so no double records.

The following queries output exactly the same

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
  ON wp_postmeta.post_id = wp_posts.ID
  AND wp_postmeta.meta_key = '_wp_attached_file'
WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
ORDER BY wp_posts.post_date DESC

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
  ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_postmeta.meta_key = '_wp_attached_file'
ORDER BY wp_posts.post_date DESC

[Marzio]

I understood the problem is WordPress
I thought the links of the images linked to the posts were in the wp_postmeta.meta_value field (with the wp_postmeta.meta_key = “_wp_attached_file” field), but then I discovered that this only happens a few times, while in other posts the wp_postmedia field. meta_key must be the same as “_webdados_fb_open_graph_specific_image”
I no longer understand anything about WordPress
I try to study the problem better

[George]

That’s is why you really shouldn’t query the complicated database of Wordpress directly but use its API so you don’t have to deal with the complicated underlying structure.

[Marzio]

Thanks George, but honestly for API and WordPress, I do not know where to start

[patrick]

For the wordpress API see: https://developer.wordpress.org/rest-api/
Listing the posts: https://developer.wordpress.org/rest-api/reference/posts/#list-posts

https://blog.domain.com/wp-json/wp/v2/posts/?orderby=date&order=desc

[Freddy_Blockchain]

very good idea to use API! I already used Wordpress and tried to directly connect to its quite complex json stored data. If API is connected witb Wappler, then I‘m highly interested in your solution and I think its not only me! Thank you dear poweful wappler community!

[Marzio]

Thank you so much I will study

[Freddy_Blockchain]

This way has multiple benefits!

• Longer compatible with Wordpress changes
• More Robust
• Clean implementation and concept
• Works also over different hosted mysql servers
• Risk of wrapping up you query is minimized