Get result from two post_types by post_meta

  • Resolved wamslers

    (@wamslers)


    8 years ago

    Hi,

    I want to get posts from my post_type “elements” and “calendar”.

    It works with the post_type “elements”, because I only have one ACF-field for the sql request. Bu the post_type “calendar” I have a ACF-field for from_date and to_date, and I want to get the posts between the two fields.

    I’m stocked, and have tried a lot of sql-requests, but without result – I hope someone can help me!

    This is my SQL-select code:

    $today = date("Ymd");
$result = $wpdb->get_results ( "
SELECT $wpdb->posts.* 
FROM $wpdb->posts, $wpdb->postmeta
WHERE ($wpdb->posts.ID = $wpdb->postmeta.post_id 
AND $wpdb->postmeta.meta_key = 'followupdate' 
AND $wpdb->postmeta.meta_value = $today
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'elements')
OR ($wpdb->posts.ID = $wpdb->postmeta.post_id 
AND $wpdb->postmeta.meta_key = 'from_date' 
AND $wpdb->postmeta.meta_value >= $today
AND $wpdb->postmeta.meta_key = 'to_date' 
AND $wpdb->postmeta.meta_value < $today
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'calendar')
ORDER BY $wpdb->posts.post_date DESC
" );
    • This topic was modified 8 years ago by wamslers.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Joey

    (@leglesslizard)

    8 years ago

    Hey,

    I’m not big on using SQL code in my PHP so I’m not sure I’m 100% right in saying this but in the past I’ve joined the tables (posts and postmeta) on post_id before running the query.

    I would instead, however, use a WP_Query here as described in the codex and an answer shown here. You’ll possibly need to tweak the “type” to be DATA or NUMERIC in that answer depending on how your dates are stored though.

    Thread Starter wamslers

    (@wamslers)

    8 years ago

    Thank you – it works! This is my code:

    <?php
$paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
$today2 = date('Ymd', strtotime('-6 hours'));
$myquery = new WP_Query(array('post_type' => array('elements','calendar'), 
'posts_per_page' => 6, 
'paged' => $paged,
'orderby' => 'title',
'order' => 'ASC',
'meta_query'=>array(
	'relation'=>'AND',
	array(
		'key' => 'from_date',
		'value' => $today2,
		'compare' => '<=',
		'type' => 'CHAR'
	),
	array(
		'key' => 'to_date',
		'value' => $today2,
		'compare' => '>=',
		'type' => 'CHAR'
	)
)
));
if ($myquery->have_posts()) :
while ($myquery->have_posts()) : $myquery->the_post();
$posttype = get_post_type(get_the_ID());
if($posttype == 'elements'){ 
	$calendar_color = 'followup'; 
	} else { 
	$calendar_type = get_post_meta(get_the_ID(), 'type', TRUE);
		if($calendar_type == 1){
			$calendar_color = 'vacation';
		}elseif($calendar_type == 2){
			$calendar_color = 'dayoff';
		}elseif($calendar_type == 3){
			$calendar_color = 'meeting';
		}
	} ?>
<div class="calendar-box-<?=$calendar_color?>"><?=the_title();?> | <?=$calendar_color?></div>
<?php endwhile; wp_reset_postdata(); else : endif; ?>
    • This reply was modified 8 years ago by wamslers.
Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Get result from two post_types by post_meta’ is closed to new replies.