WordPress.org

Forums

Displaying Posts Using a Custom Select Query (15 posts)

  1. smoczokwik
    Member
    Posted 7 years ago #

    In a struggle to have the posts on my category pages listed by custom field I tried the following code (based on Displaying_Posts_Using_a_Custom_Select_Query).

    The problems:
    1. It sorts the entries by date anyway :-(
    2. It does not respect the chosen category (i.e. dispalys all posts)

    The second time I approach this problem and again it outwits me. Help please!

    [moderated: code pasted at http://wordpress.pastebin.ca/548100 ]

    To see the script in action go to:
    http://www.sobek.pl/category/metoda-callana/stage-4/

  2. MichaelH
    Member
    Posted 7 years ago #

    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

  3. smoczokwik
    Member
    Posted 7 years ago #

    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.

  4. moshu
    Member
    Posted 7 years ago #

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

  5. 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.

  6. smoczokwik
    Member
    Posted 7 years ago #

    @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.

  7. kernowkernow
    Member
    Posted 7 years ago #

    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
  8. Modesty
    Member
    Posted 7 years ago #

    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?

  9. alanft
    Member
    Posted 7 years ago #

    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/

  10. jan londn
    Member
    Posted 7 years ago #

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

  11. ninjaboy81
    Member
    Posted 7 years ago #

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

  12. Ingenius Dastard
    Member
    Posted 7 years ago #

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

  13. Josh Byers
    Member
    Posted 7 years ago #

    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.

  14. dexno
    Member
    Posted 6 years ago #

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

    Thanks

  15. valeeum
    Member
    Posted 6 years ago #

    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!

Topic Closed

This topic has been closed to new replies.

About this Topic