Grab main image of the last post in Wordpress

Question

I need to display the latest post posted Wordpress blog in a Cakephp application and am doing it as follows:

$post = $this->Post->query(
    "SELECT
        posts.id,
        posts.post_title AS title,
        posts.post_date,
        posts.guid,
        files.meta_value AS filepath
    FROM
        wp_posts posts
    INNER JOIN wp_posts attachments ON posts.ID = attachments.post_parent
    INNER JOIN wp_postmeta files ON attachments.ID = files.post_id
    WHERE files.meta_key = '_wp_attached_file' ORDER BY posts.post_date DESC LIMIT 1"
);

Until then beauty, I’m able to get the last registered post, however the image is not the one that is registered to be featured in the blog, which would be a way to search only the main photo of this post?

Answer 1

I managed to do it this way, but it got a little strange:

    $post = $this->Post->query(
        "SELECT
            posts.id,
            posts.post_title AS title,
            posts.post_date,
            posts.guid
        FROM
            wp_posts posts
        INNER JOIN wp_posts attachments ON posts.ID = attachments.post_parent
        INNER JOIN wp_postmeta files ON attachments.ID = files.post_id
        ORDER BY posts.post_date DESC LIMIT 1"
    );

    $picture = $this->Post->query(
        "SELECT picture.guid FROM wp_postmeta
        INNER JOIN wp_posts picture ON wp_postmeta.post_id=picture.ID
        WHERE post_parent = {$post[0]['posts']['id']} AND meta_key = '_wp_attached_file'
        ORDER BY post_date DESC LIMIT 1"
    );

    $post[0]['posts']['picture'] = $picture[0]['picture']['guid'];

    $this->set('post', $post);