Child Category Posts via Custom Query

  • Hello!
    My morning was a nice one; found answers to a lot of questions regarding custom SQL queries in WordPress. (see thread) Now the archive template displays all posts sorted by a custom ‘year’ tag with working page navigation and stuff. (in action)
    Still, when I display a parent category, the query finds nothing (see page). How do I modify my query to show posts of child categories? I tried to sort out this nice article by Kafkaesqi about parents and childs, but got confused.

    The code in use:

    <?php
    $pagelimit=6;
    $page = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $pagenum=($page-1)*$pagelimit;

    $pageposts = $wpdb->get_results("
    SELECT * FROM $wpdb->posts, $wpdb->post2cat, $wpdb->postmeta
    WHERE ID = $wpdb->postmeta.post_id
    AND ID = $wpdb->post2cat.post_id
    AND category_id = $cat
    AND meta_key = 'year'
    AND post_status = 'publish'
    ORDER BY meta_value DESC
    LIMIT $pagenum,$pagelimit
    ", OBJECT); ?>

    Thanks in advance for any hint!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter paracetamol

    (@paracetamol)

    Hm, I can’t edit the old post anymore.

    This one get’s the categories:

    SELECT *
    FROM $wpdb->wp_categories
    WHERE category_parent = $cat

    But how do I implement that in the query? A friend helped me clean up the old query; it reads a correct list by ‘year’ now (it did before, but by accident).

    SELECT *
    FROM $wpdb->posts
    JOIN $wpdb->post2cat ON ID = post2cat.post_id
    LEFT JOIN $wpdb->postmeta ON ID = postmeta.post_id
    WHERE category_id = $cat
    AND post_status = 'publish'
    AND meta_key='year'
    ORDER BY meta_value DESC

    I have this issue too. I would like to query just the the child posts of a category. In general I am disappointed with WP’s lack of flexibility in doing things with categories.

    Surely its quite doable though. Any help?

    i don’t know if this will work with mySQL – but in SQL there is the “IN”-Clause.

    So you could try to add this to the where-clause:


    select <your fields>
    from <your tables and joins>
    where <your filters>
    AND category_id IN
    (SELECT <only the category id field here!>
    FROM $wpdb->wp_categories
    WHERE category_parent = $cat)

    This way the current category_id is checked against a list of categories.

    But as said: not sure if mySQL can do this

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Child Category Posts via Custom Query’ is closed to new replies.