Custom query by category

  • eian00

    (@eian00)


    14 years, 2 months ago

    Hello,

    I would need some help to edit the following code:

    <?php
global $wpdb;
global $post;
$key1 = 'naselje';
$val1 = get_post_meta($post->ID, 'naselje', true);
$key2 = 'grad_opcina';
$val2 = get_post_meta($post->ID, 'grad_opcina', true);
$category = get_the_category($post->ID);
$category = $category[0]->cat_ID;
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta metacolor, $wpdb->postmeta metawgt

WHERE wposts.ID = metacolor.post_id

AND wposts.ID = metawgt.post_id
AND (metacolor.meta_key = '$key1' AND metacolor.meta_value = '$val1')
AND (metawgt.meta_key = '$key2' AND metawgt.meta_value = '$val2')
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
ORDER BY UPPER(wposts.post_title) ASC
LIMIT 4
"; 

$pageposts = $wpdb->get_results($querystr, OBJECT); ?>  <?php if ($pageposts):
?>  

<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>

    It works just fine.
    It shows in my sidebar all the posts that have the same custom fields as the current post displayed.

    Now I would need to add some more filtering.

    How to make this same code filter posts with the same two custom fields as the current post and also by the same category as the current post?

    Thank you

Viewing 2 replies - 1 through 2 (of 2 total)
  • vtxyzzy

    (@vtxyzzy)

    14 years, 2 months ago

    This is UNTESTED, but I think this query is what you want:

    $querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta metacolor, $wpdb->postmeta metawgt
JOIN $wpdb->term_relationships tr ON wposts.ID = tr.object_id
JOIN $wpdb->term_taxonomy tt ON ( tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.term_id = $category )
WHERE wposts.ID = metacolor.post_id

AND wposts.ID = metawgt.post_id
AND (metacolor.meta_key = '$key1' AND metacolor.meta_value = '$val1')
AND (metawgt.meta_key = '$key2' AND metawgt.meta_value = '$val2')
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
ORDER BY UPPER(wposts.post_title) ASC
LIMIT 4
";
    vtxyzzy

    (@vtxyzzy)

    14 years, 2 months ago

    Slight modification to add taxonomy = ‘category’:

    $querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta metacolor, $wpdb->postmeta metawgt
JOIN $wpdb->term_relationships tr ON wposts.ID = tr.object_id
JOIN $wpdb->term_taxonomy tt ON ( tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'category' AND tt.term_id = $category )
WHERE wposts.ID = metacolor.post_id

AND wposts.ID = metawgt.post_id
AND (metacolor.meta_key = '$key1' AND metacolor.meta_value = '$val1')
AND (metawgt.meta_key = '$key2' AND metawgt.meta_value = '$val2')
AND wposts.post_type = 'post'
AND wposts.post_status = 'publish'
ORDER BY UPPER(wposts.post_title) ASC
LIMIT 4
";
Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Custom query by category’ is closed to new replies.