If you’re able to construct an SQL query that’ll do the job, you can run it using the global wpdb class object: $wpdb
. Most devs tend to use the WP_Query class to construct custom queries. Its argument structure tends to be more accessible and forgiving. WP_Query doesn’t always work for all situations. While very flexible, its flexibility is not unlimited. There are a number of filters in WP_Query::get_posts() that allow us to alter various clauses of the resulting SQL query string. This greatly extends its flexibility, but it still has its limits. When all else fails, there’s always that global $wpdb. You can run any query that’s possible this way, so it’s the ultimate in flexibility, provided you are able to construct the desired query correctly.
I will warn you to not rely on the GUID field for related file data. That is not the purpose of the field. There’s nothing saying the GUID cannot be some other unique identifier that is unrelated to the file. The actual field used to reference a file is in postmeta. Your queries should use this postmeta, not GUID.
The one glaring weakness of WP_Query is it cannot access data of custom tables. In order to do that you need to build your own SQL queries and run them through global $wpdb. I’m rather weak with SQL myself. I’ve resorted to using WP_Query just to see what SQL it comes up with, then modifying it for my needs and using the result in $wpdb. The WP_Query part is not part of the final code, it’s just a learning technique I sometimes use when I’m stuck in my SQL query composition effort 🙂
Hi,
I’ve looked at this, thanks.
I now have a query that works with this:
if ( $postids )
{
echo "<ul>";
foreach ( $postids as $id )
{
$post = get_post( intval( $id ) );
setup_postdata( $post );
?>
<li>
<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>">
<?php the_title(); ?>
</a>
</li>
<?php
}
echo "</ul>";
}
This gives the list, but the link is to the ‘page’ of that attachment, and then on that page is the link to the file. How would I get the link to the file instantly?
You want to query for “attachment” post types whose parent has the folder id of interest. Then the file path relative to uploads folder is in each attachment’s postmeta under the key “_wp_attached_file”
I solved it, thanks!
Adding this $path = wp_get_attachment_url( $id );
and echoing $path
instead of the_permalink();
Kind regards,
Martijn
I just tried to Add this code to a widget, where the query is not working, should I use something special in a widget?
No, but the snippet you posted is missing the actual query that generates $postids, so I couldn’t speculate why it doesn’t work in a widget. Are you otherwise getting widget output, just no posts returned?
Oh sorry, here is the complete code:
$postids=$wpdb->get_col( $wpdb->prepare(
"
SELECT asdr_medialibrary_posts.attachment
FROM asdr_medialibrary_posts
WHERE asdr_medialibrary_posts.fid = %s
",
$meta_key2
) );
if ( $postids )
{
echo "<ul>";
foreach ( $postids as $ida )
{
$post = get_post( intval( $ida ) );
setup_postdata( $post );
$path = wp_get_attachment_url( $ida );
?>
<li>
<a href="<?php echo $path; ?> " rel="bookmark" title="<?php the_title_attribute(); ?>">
<?php the_title(); ?>
</a>
</li>
<?php
}
echo "</ul>";
}else{ echo 'Geen bestanden'; }
I used the same query and code in a page template, only difference is $metakey2
but I can echo that just fine on both.
When I comment out the query it the page loads normally with just an empty widget. But with the query there, the page stops loading on that widget, and all that comes after is not loaded. (i put the widget in between 2 other widgets, the one before it will load. the one after wont.
-
This reply was modified 7 years, 3 months ago by Martijn. Reason: Extra info
Thanks for the query. As it turns out, that part is fine, the problem is in the snippet you posted prior to that but I couldn’t see it then. Something about being out of context confused me I think 🙂
Just add global $post;
somewhere before the foreach loop. It’s a common omission. I forget it myself sometimes even though I know better. Use it anytime you use setup_postdata(). You didn’t need it on the main template because it was declared in scope as part of the_post()
in the main loop.
Hi,
It is not working still.
if I comment out:
$postids=$wpdb->get_col( $wpdb->prepare(
"
SELECT asdr_medialibrary_posts.attachment
FROM asdr_medialibrary_posts
WHERE asdr_medialibrary_posts.fid = %s
",
$meta_key2
) );
This bit, it ‘works’ as in it echos Geen bestanden from the ..else{ echo 'Geen bestanden'; }
But with this query, which worked on the other page. it just doesn’t work.
Well, the global was necessary, but it apparently prevented me from seeking any other issues. The issue with the query is so closely related I’m embarrassed that I missed it.
Just before the query declare global $wpdb;
🙂
Sorry for being a bit blind, but that’s how it goes sometimes.
This worked, thanks.
I have something working now.
Thanks fro the help