[wp-hackers] Query to return only posts that have an image attachment

[Dave Viner]

First, let me answer at least part of your question.  I don't know of a
WordPress function that would give you what you're looking for (posts that
have attachments of type=image).  But, here's some raw SQL that would do it:

select art.ID, att.ID, att.post_title
from wp_posts art, wp_posts att
where art.ID != att.ID
and art.ID = att.post_parent
and att.post_mime_type = 'image/jpeg';
order by art.post_date_gmt desc
limit 5;

This SQL returns the post IDs that have at least 1 image attached of type
'image/jpeg' - and limits it to the 5 most recent articles posted.

I'm not sure if this is what you're looking for, but that sounds like the
SQL you're describing.  You can take these IDs and feed them into get_post()
to load the post objects in your template.

On the attachment issue, I don't think that it's always true that the
attachment is created when you insert an image into a post.  Here's a test I
just ran on my WP 2.9.1 install.

Login
Click Media Library.
Click Add New.
Upload new image.

Now, in the database I see:

mysql> select ID, post_title, post_type, post_mime_type from wp_posts where
id > 391017;
+--------+--------------------+------------+----------------+
| ID     | post_title         | post_type  | post_mime_type |
+--------+--------------------+------------+----------------+
| 391018 | image_resize_test6 | attachment | image/jpeg     |
+--------+--------------------+------------+----------------+

Perfect.

I create a new post, and insert the image.
Click Posts
Click Add New
Click the 'Add Media' icon.
Click the 'From Media Gallery'
Select the image that I just uploaded.
Click 'Insert Into Post'.

Now, the db shows this:

mysql> select ID, post_title, post_type, post_mime_type, post_parent from
wp_posts where id > 391017;
+--------+-----------------------------+------------+----------------+-------------+
| ID     | post_title                  | post_type  | post_mime_type |
post_parent |
+--------+-----------------------------+------------+----------------+-------------+
| 391018 | image_resize_test6          | attachment | image/jpeg     |
 391019 |
| 391019 | image test... please ignore | post       |                |
    0 |
| 391020 | image test... please ignore | revision   |                |
 391019 |


Perfect.  Image is an attachment, and post_parent is set to the newly
created post.

I add a second image to the post, using the same process (but selecting a
different image from the media library. The post appears fine with 2 images.
 But, in the DB, I now see this:

mysql> select ID, post_title, post_type, post_mime_type, post_parent from
wp_posts where post_parent = 391019;
+--------+-----------------------------+------------+----------------+-------------+
| ID     | post_title                  | post_type  | post_mime_type |
post_parent |
+--------+-----------------------------+------------+----------------+-------------+
| 391018 | image_resize_test6          | attachment | image/jpeg     |
 391019 |
| 391020 | image test... please ignore | revision   |                |
 391019 |
| 391021 | image test... please ignore | revision   |                |
 391019 |
| 391022 | image test... please ignore | revision   |                |
 391019 |
| 391023 | image test... please ignore | revision   |                |
 391019 |
+--------+-----------------------------+------------+----------------+-------------+

Where's the second image?  It's not attached to the post.

In this case, it's because the second image was in fact already "attached"
to another post:
mysql> select ID, post_title, post_type, post_mime_type, post_parent from
wp_posts where ID = 391001;;
+--------+--------------------+------------+----------------+-------------+
| ID     | post_title         | post_type  | post_mime_type | post_parent |
+--------+--------------------+------------+----------------+-------------+
| 391001 | image_resize_test2 | attachment | image/jpeg     |      391002 |
+--------+--------------------+------------+----------------+-------------+

Since the post_parent column is one-to-one with the attachment-post-id, a
single image can appear in multiple posts, but only be "attached" to one.


In addition, if you delete the HTML from a post that shows the image, the
attachment does not disappear.  So, just because the DB has an image
"attached" to a post does not mean that the image actually appears in the
article.  (This may not be an issue in your use case, but I'm just pointing
out the DB issues.)

HTH
Dave Viner

On Thu, Jan 21, 2010 at 9:01 PM, Adam Holzband <adamholz at gmail.com> wrote:

> Thanks for your response Dave.  I understand the difference between
> attaching an image to a post and inserting but appreciate you pointing out
> the potential additional complexity.  Actually, if you add something to
> Media Library and then insert in a post later, it will be an attachment.
>
> In any case, the scenario I'm working with is very specific and each post
> can contain a gallery of attachments.  I just need to be able to query only
> the posts that have an attachment with an image post_mime_type. Still
> hoping
> that someone out there has some guidance on a better way than one of my
> kludges to accomplish that.  Thanks.
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>