Support » Plugins and Hacks » Displaying Posts Using a Custom Select Query

Viewing 14 replies - 1 through 14 (of 14 total)
  • It would seem your ORDER BY is not necessary since you are only selecting posts that have one specific meta_value (paragraf).

    Instead of putting that code in a category template, try putting it in a page template.

    Also see:
    Template Hierarchy
    Stepping into Templates

    Thanks, but actually I’m trying to get all the posts that have the meta key ‘paragraf’ set and sort them according the the ‘paragraf’ value. In other words, the meta key is needed to limit the posts I want to show, the value is used to sort these posts.

    Indeed, the error was here:
    NOT ORDER BY wpostmeta.meta_key ASC
    but ORDER BY wpostmeta.meta_value ASC

    There is still the second problem left:
    How do I make it show only entries of the chosen category?
    it works with the standard tags, so I guess there must be a variable to pass along to achieve the script only showing posts of one category.

    Did you try to use it in a category template?
    Say, your category has ID# 5, use a template file:
    category-5.php

    If you want to do it by category as well, you need to restructure your query a bit… That example they use is overly simple anyway.

    SELECT $wpdb->posts.*
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
    WHERE $wpdb->postmeta.meta_key = 'paragraf'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->post2cat.category_id IN (1,2,3)
    ORDER BY $wpdb->postmeta.meta_value ASC

    Change the 1,2,3 with your category ID numbers, of course.

    @moshu & Otto42

    Hmm, looks like your ideas would make a good combo :-). Since I need to e.g display only the posts of category 4 on one page, and category 5 on another the only way out for seems to be making 12 pages (because there are 12 categories) and modify the query accordingly for each page.

    with wordpress 2.3 you need to update the sql query shown above to this:

    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->term_taxonomy.term_id = 1,2,3
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->postmeta.meta_key = 'paragraf'
    ORDER BY $wpdb->postmeta.meta_value ASC

    Dear all, I’ve been struglling with this for a while now… I have a category that displays articles with basic info on some festivals… in my custom fields I have 3 fields defining the date each festival begins… now I would like to sort all the articles in the category using these custom values… could you you help me to do that?

    the easiest way might be to use add_filter( ‘posts_orderby’, X). try adapting the code used by Random Posts query code here: http://themeshaper.com/wordpress-cms-plugins-the-bare-minimum/

    It’s working great here but i would like to limit the posts to lets say 10 posts, would that be possible? Thanks.

    I made a new discussion for my question: http://wordpress.org/support/topic/161154?replies=1#post-707079

    Does this respect the “Blog pages per page” number specified in the WordPress admin?

    I would also like to know how to limit the query to a certain number of posts – just like ‘showposts’ does in the normal query.

    Thanks.

    i would like to know how to apply wp_pagenavi for this combo?

    Thanks

    I can’t figure out how to embed this code within functions.php and get it to work. Are there specific scope issues that need to be addressed?? When I copy the code above directly into category.php, it works fine. Thanks for the support!

Viewing 14 replies - 1 through 14 (of 14 total)
  • The topic ‘Displaying Posts Using a Custom Select Query’ is closed to new replies.