# Show list of custom posts with respective attachments?

I would like to construct a query to show a list of custom posts ("podcasts") with the urls to their respective attachments. Each custom post_type ("podcast") has 1 attachment (the mp3 file belonging to it's post_parent). I would like an unordered list showing podcast's title, and it's attachment url (guid of the attachment) like this:

<ul>
<li>Podcast Title - http://..../podcast.mp3</li>


I achieved this with wp queries:

<?php query_posts('showposts=1&post_type=podcasts'); ?>
<ul>
<?php while (have_posts()): the_post(); ?>
<li><?php the_title(); ?><br>
<?php
$args = array( 'post_type' => 'attachment', 'post_parent' =>$post->ID );
$attachments = get_posts($args);
if ($attachments) { foreach ($attachments as $attachment ) { echo$attachment->guid;
}
} ?>
</li>
<?php endwhile; ?>
</ul>


But I would like to know how I solve with raw SQL query. I've tried so many queries it's made me dizzy - any help would be amazing!

* EDIT: Decided to go with John's suggestion of WordPress' queries. Thank you and I trust someone else will also find this useful in future.

-
What queries have you tried? Also, if you think the WP_Query queries are dizzying, wait till you see the raw SQL. –  s_ha_dum Jan 7 '13 at 15:38
hey, actually I got the query_posts to work fine, but yes it's the raw sql I'm after :-) I've tried: SELECT * FROM wp_posts WHERE post_type='podcasts' and then another query in the the 'while' print, but that didn't work. Tried a few AND and OR options.. nothing. I'm not an expert with sql queries, I might add. I will edit the question to reflect this, thank you. –  Mark Jan 7 '13 at 15:40

Trust us on this one, you want to use WordPress' query solution. It provides a whole bunch of awesome extras, such as object caching, autoloading of metadata, etc. Plus, it's almost guaranteed to be forwards compatible.

So. How to do what you've inquired about. Something like this ought to do the trick:

<?php
$podcasts = new WP_Query( array( 'post_type' => 'podcasts', 'posts_per_page' => 10, // the number of podcasts you want to show. use -1 for all of them. )); foreach ($podcasts as $podcast ) {$attachment = get_posts('post_type=attachment&post_status=inherit&post_parent='.$podcast->ID); if ($attachment) {
$attachment =$attachment[0];
?>
<li><?php echo get_the_title( $podcast->ID ); ?> - <?php echo wp_get_attachment_url($attachment->ID ); ?></li>
<?php
}
}
?>

-
John, thank you so much for such a quick response. I think you're right in using wordpress solution. I have updated the question to reflect my search for sql solution, as I'd like to get my head around how that would be done. Thank you. –  Mark Jan 7 '13 at 15:50
A quick way to check out the queries you're running is to install the debug bar plugin: wordpress.org/extend/plugins/debug-bar It'll give you a list of all the queries run on any given page load. :) –  John P Bloch Jan 7 '13 at 16:00
Ok, I concede - wp's queries it is :) thank you again –  Mark Jan 7 '13 at 16:26