Custom SQL Query? WordPress
- SOLVED
I am trying to splice together 2 post types and pull in 8 posts/events greater than the current day. I am struggling to get the default post type posts to show up though. Right now only the events are showing up - and I think it is something to do with my meta_query. The code is below:
global $post;
$today = date( 'Ymd' );
$args = array(
'post_type' => array('post', 'tribe_events'),
'post_status' => 'publish',
'posts_per_page' => 8,
'meta_query' => array(
'relation' => 'OR',
'event_date' => array(
'key' => '_EventStartDate',
'compare' => '>=',
'value' => $today,
),
'date' => array(
'key' => 'date',
'compare' => '>=',
'value' => $today,
)
),
'orderby' => array( 'event_date' => 'ASC', 'date' => 'ASC' ),
);
Any help would be greatly appreciated.
Answers (6)
Rempty answers:
Hello
the problem is that post dont have the meta. maybe adding one more condition to the meta query will help like this
global $post;
$today = date( 'Ymd' );
$args = array(
'post_type' => array('post', 'tribe_events'),
'post_status' => 'publish',
'posts_per_page' => 8,
'meta_query' => array(
'relation' => 'OR',
'event_date' => array(
'key' => '_EventStartDate',
'compare' => '>=',
'value' => $today,
),
'date' => array(
'key' => 'date',
'compare' => '>=',
'value' => $today,
),
array(
'key' => '_EventStartDate',
'value' => '',
'compare' => 'NOT EXISTS',
)
),
'orderby' => array( 'event_date' => 'ASC', 'date' => 'ASC' ),
);
but the best solution is have the 2 posts types have the same meta field name to compare
JAMES GEIGER comments:
Hi, Do you know what Kyle was talking about above? He said:
I also would not use rempty's solution below because TEC uses janky values at times for things like recurring events and past events that might cause false positives.
Can you take another look? It seems to work but there are indeed some false positives showing up. I see 2 older posts and 1 old event showing from last year.
TimMatz answers:
Try this custom query:
$list = $wpdb->get_col("
select p.id
from
{$wpdb->prefix}postmeta pm
left join {$wpdb->prefix}posts p on p.id = pm.post_id
where
(
(pm.meta_key='_EventStartDate' and p.post_type='tribe_events')
OR
(pm.meta_key='date' and p.post_type='post)
)
AND
p.post_status = 'publish'
AND
pm.meta_value >= '$today'
order by pm.meta_value ASC limit 8");
$list will contain id of posts in true order. Then use get_post() for each.
JAMES GEIGER comments:
How do I display it? Can you give an example?
TimMatz comments:
global $post;
foreach( $list as $post_id )
{
$post = get_post( $post_id );
setup_postdata( $post );
?>
<h1><?php the_title() ?></h1>
<div><?php the_content() ?></div>
<?php
}
Mohamed Ahmed answers:
Hi James,
Here you are the code
<?php
/******************************************************
* code by : Mohamed Ahmed (#Eldigital)
* callmeinstantly@gmail
* Splice together 2 post types and pull in 8 posts/events greater than the current day.
* Struggling to get the default post type posts to show up though.
* Right now only the events are showing up.
*/
global $wpdb;
$sql_query = "
SELECT DISTINCT $wpdb->posts.*
FROM $wpdb->posts, $wpdb->postmeta
WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
AND
(
$wpdb->posts.post_status = 'publish'
OR
$wpdb->posts.post_status = 'future'
)
AND
(
(
$wpdb->posts.post_type = 'post'
AND
$wpdb->posts.post_date >= NOW()
)
OR
(
$wpdb->posts.post_type = 'tribe_events'
AND
$wpdb->postmeta.meta_key = '_EventStartDate'
AND
$wpdb->postmeta.meta_value >= NOW()
)
)
ORDER BY $wpdb->posts.post_date ASC LIMIT 8
";
$posts = $wpdb->get_results($sql_query);
As this is WordPress SQL query, wordpress modify and results the answer equivalent to INNER JOIN.
.
JAMES GEIGER comments:
Don't you need to do inner join like TimMatz?
Mohamed Ahmed comments:
Yes, It will works with you.
Although I respect them but I'm always don't see other users solutions.
I need your comment on the answer :)
Mohamed Ahmed comments:
As this is WordPress SQL query, wordpress modify and results the answer equivalent to INNER JOIN.
Reigel Gallarde answers:
hello,
can you try using this code instead?
$get_events = tribe_get_events(array(
'post_type' => array('post', 'tribe_events'),
'post_status' => 'publish',
'start_date' => tribe_event_beginning_of_day(date('j M Y')),
'posts_per_page' => 8
), true);
Shoeb mirza answers:
'post_type' => array('tribe_events', 'post')
try changing your post_type
JAMES GEIGER comments:
No luck
Shoeb mirza comments:
You are trying to get 8 Posts all together right?
JAMES GEIGER comments:
Hi Shoeb, Yes.
Kyle answers:
You are doing a meta_query using _EventStartDate but posts do not have that meta field so it will return negative for any of those. It isn't possible. You would have to add a new meta field for the posts to recognize in your OR part of the query.
JAMES GEIGER comments:
Or do I need to do a custom SQL query? Can you write it?
Kyle comments:
You need a default post meta field just as a placeholder in your query.
add_action('save_post_post_type', 'set_default_meta', 10, 3);
function set_default_meta( $post_ID ) {
if ( !wp_is_post_revision($post_ID) ) {
update_post_meta( $post_ID, 'query_placeholder', 'hold' );
}
}
Then add another argument to the OR query where the jey is query_placeholder and the value is hold and the comparison is =
Go back and hit update on the 8 most recent posts for it to show and it will work going forward.
JAMES GEIGER comments:
ok - so it will work for posts going forward?
JAMES GEIGER comments:
Like this?
global $post;
$today = date( 'Ymd' );
$args = array(
'post_type' => array('tribe_events', 'post'),
'post_status' => 'publish',
'posts_per_page' => 8,
'meta_query' => array(
'relation' => 'OR',
'event_date' => array(
'key' => '_EventStartDate',
'compare' => '>=',
'value' => $today,
),
'date' => array(
'key' => 'query_placeholder',
'compare' => '=',
'value' => 'hold',
)
),
'orderby' => array( 'event_date' => 'ASC', 'date' => 'ASC' ),
Kyle comments:
To be honest I'm not really sure how you are getting your query structure. Where is event_date coming from in all of the args?
JAMES GEIGER comments:
I am not sure I thought you had to define the array as something so I called it event_date - is that wrong?
Kyle comments:
Oh, I see you're using it for orderby.
Let me reformat this, one second
Kyle comments:
Here it is in a code formatter https://www.paste.org/91754
I wouldn't use 'date' to name a meta query field because that is a default query option. I also would not use rempty's solution below because TEC uses janky values at times for things like recurring events and past events that might cause false positives.
To that end
JAMES GEIGER comments:
ok
JAMES GEIGER comments:
Hi I think there is an issue. I added the set_default_meta function and action you posted. I updated a few posts. I even added a new post, but only events are showing up - no other posts (not even the new test post).
Any ideas?
Kyle comments:
Can you check the custom field area to see if there is any value there, you can also manually set it for the purpose of testing and we can revisit the save post action once the query is working
JAMES GEIGER comments:
I had to add it manually, but I noticed that some events are missing (there are two in late march that are missing) and there are 2 posts showing up, but they are at the end of the list even though they were posted today?
Kyle comments:
Your orderby is going to have the same issues as the meta query, the posts don't have a value for event_date so get pushed to the back.
It is only pulling 8 objects total, so those 2 posts replaced 2 events
JAMES GEIGER comments:
so this won't work?
Kyle comments:
If you want to spend a little more time and effort we could do something similar to the default value, but we make something that is literally setting a _EventStartDate value which will then work with your original query a bit better, but it'd be tricky for whenever you edit posts etc. so we'd have to work through it.
JAMES GEIGER comments:
yeah...what do you recommend sir?
Kyle comments:
If you want to send me a pm here https://blueskycoding.com/contact-us/ we can discuss best options
JAMES GEIGER comments:
I would rather stay here ... so you're saying it is not an easy fix?