Scribu's Posts to Posts filter on pre_get_posts WordPress
- SOLVED
I am using Scribu's Posts to Posts plugin https://github.com/scribu/wp-posts-to-posts/wiki
and need a filter on pre_get_posts to make it so it only shows posts that have 2 or more connected posts
I tried to do a posts_where filter
$where .= "AND $wpdb->posts.ID in ( SELECT p2p_from
from $wpdb->p2p
group by p2p_from
having COUNT(*) > 1 )
but that didn't work, it works if I do
$where .= "AND $wpdb->posts.ID in ( SELECT p2p_from
from $wpdb->p2p ) but just picks posts with one connection
There is probably a totally different way to do this that doesn't use posts_where I don't care how its done I just need it in pre_get_posts
Answers (2)
timDesain Nanang answers:
<blockquote>I just need it in pre_get_posts</blockquote>
You can try this hook:
add_action('pre_get_posts','wpq_pre_get_posts');
function wpq_pre_get_posts( $wp_query_obj ) {
global $wpdb;
$qry = " SELECT p2p_from from ".$wpdb->prefix."p2p group by p2p_from having COUNT(*) > 1 ";
$res = $wpdb->get_results($qry);
if(!empty($res)){
$ids = array();
foreach($res as $data){
$ids[] = $data->p2p_from;
}
$wp_query_obj->set('post__in', $ids );
}
}
Katie comments:
I cant get that to work - does this conflict with it? I need it to go in
function custom_posts( $query ){
if (( ! is_admin() ) && $query->is_main_query())
{
if ( is_home() ) {
$query->set('tag__not_in', array(12728));
$query->set('category__not_in', array(219,1649,2369,989,1169,969,1909,729,689,1869,81,2269,929,173, 2129, 2289, 2708,2169));
}
}
}
add_action( 'pre_get_posts', 'custom_posts' );
I tried all different variations - I need it to go inside
if (( ! is_admin() ) && $query->is_main_query())
{
if ( is_home() ) {
(not admin is main query and is home)
is this part for sure correct?
$wp_query_obj->set('post__in', $ids );
it shouldn't be $query->set ?
or ('post__in', array( $ids )); ?
the $wpdb->prefix is phpbb_ so I was using phpbb_p2p
Katie comments:
I removed my custom_posts function and tried yours on its own and it didn't filter
timDesain Nanang comments:
<blockquote>is this part for sure correct?
$wp_query_obj->set('post__in', $ids );
it shouldn't be $query->set ?
or ('post__in', array( $ids )); ?
</blockquote>
yes, it is correct.
$wp_query_obj is depended on att's name in the function
if you are using $this_name, then the function is
function wpq_pre_get_posts( $this_name )
and the object is
$this_name->set();
Yes, $ids is an array, check the code ( $ids = array(); and $ids[] = )
You should use (is_home() OR is_front_page()) instead of is_home() only.
Try this code:
add_action('pre_get_posts','wpq_pre_get_posts');
function wpq_pre_get_posts( $query ) {
global $wpdb;
if(!is_admin() AND $query->is_main_query() AND (is_home() OR is_front_page()) ) {
$qry = " SELECT p2p_from from ".$wpdb->prefix."p2p group by p2p_from having COUNT(*) > 1 ";
$res = $wpdb->get_results($qry);
if(!empty($res)){
$ids = array();
foreach($res as $data){
$ids[] = $data->p2p_from;
}
$query->set('post__in', $ids ); //yes, $ids is an array
}
$query->set('tag__not_in', array(12728));
$query->set('category__not_in', array(219,1649,2369,989,1169,969,1909,729,689,1869,81,2269,929,173, 2129, 2289, 2708,2169));
}//is_
}
Katie comments:
ok will try that in just a sec getting server back up
Katie comments:
I got Call to a member function get_results() on a non-object
Katie comments:
I can query this directly and it gives me correct results - SELECT p2p_from from phpbb_p2p group by p2p_from having COUNT(*) > 1
Katie comments:
If I query it in MySQL does it matter than "Current selection does not contain a unique column. "
would that mess it up
timDesain Nanang comments:
are you sure, the query is main query?
try change this part
if(!is_admin() AND $query->is_main_query() AND (is_home() OR is_front_page()) ) {
with
if(!is_admin() AND (is_home() OR is_front_page()) ) {
and add this code
echo '<pre>'; print_r($qry); echo '</pre>';
after
$res = $wpdb->get_results($qry);
Katie comments:
can I just do a query outside the function something like
$query = "SELECT * from phpbb_p2p group by p2p_from having COUNT(*) > 1";
$res = mysql_fetch_assoc($query);
$res = $res['p2p_from'];
then say
$query->set('post__in', $res );
Katie comments:
I have the leave the is_home part as is because I have other ones is_search is_category etc
but I know it works because Ive been using that right now
I can add is_front page though but its for sure home not front page
Katie comments:
this for sure works:
function custom_posts( $query ){
if (( ! is_admin() ) && $query->is_main_query())
{
if ( is_home() ) {
$query->set('tag__not_in', array(12728));
$query->set('category__not_in', array(219,1649,2369,989,1169,969,1909,729,689,1869,81,2269,929,173, 2129, 2289, 2708,2169));
}
if (is_category() ) {
$cat_id = get_term_by( 'slug', $query->query_vars['category_name'], 'category');
if ($cat_id->parent != 0) {
$query->set( 'post_type', array( 'product') );
}
}
if ($query->is_search) {
if (isset($_GET['post_type']) && $_GET['post_type'] == 'product') {
$post_type = 'product';
} else {
$post_type = 'post';
}
$query->set('post_type', $post_type);
$query->set( 'orderby', 'date' );
}
}
}
add_action( 'pre_get_posts', 'custom_posts' );
I just cant get the results of this
SELECT p2p_from from phpbb_p2p group by p2p_from having COUNT(*) > 1
inside $query->set('post__in', $ids );
so frusterating :(
timDesain Nanang comments:
Wordpress said: "The code is poetry".
ok, wait a minute, I will try your code.
Katie comments:
do I need to have post type set if I use post__in
its just regular post type post
Katie comments:
lol poetry is not the first word that comes to mind
timDesain Nanang comments:
have you declared <strong>global $wpdb;</strong> before the query?
timDesain Nanang comments:
try this code, should be works:
add_action( 'pre_get_posts', 'custom_posts' );
function custom_posts( $query ){
if (( ! is_admin() ) && $query->is_main_query()){
if ( is_home() ) {
$query->set('tag__not_in', array(12728));
$query->set('category__not_in', array(219,1649,2369,989,1169,969,1909,729,689,1869,81,2269,929,173, 2129, 2289, 2708,2169));
}
if (is_category() ) {
$cat_id = get_term_by( 'slug', $query->query_vars['category_name'], 'category');
if ($cat_id->parent != 0) {
$query->set( 'post_type', array( 'product') );
}
}
if ($query->is_search) {
if (isset($_GET['post_type']) && $_GET['post_type'] == 'product') {
$post_type = 'product';
} else {
$post_type = 'post';
}
$query->set('post_type', $post_type);
$query->set( 'orderby', 'date' );
}
global $wpdb;
$qry = " SELECT p2p_from from ".$wpdb->prefix."p2p group by p2p_from having COUNT(*) > 1 ";
$res = $wpdb->get_results($qry);
echo '<pre>'; print_r($qry); echo '</pre>';
if(!empty($res)){
$ids = array();
foreach($res as $data){
$ids[] = $data->p2p_from;
}
$query->set('post__in', $ids ); //yes, $ids is an array
}
}
}
timDesain Nanang comments:
I mean (for debugging)
echo '<pre>'; print_r($res); echo '</pre>';
Katie comments:
it worked!!! I didn't have global $wpdb; ...
thank you!
Dbranes answers:
I think you want this SQL query instead:
AND {$wpdb->posts}.ID IN ( SELECT p2p_to FROM {$wpdb->p2p} GROUP BY p2p_to HAVING COUNT(*) > 1 )
to get all posts that have more than one connection.
If you want to filter the main query, to show only posts with more than one connection, you can use the following:
/**
* Filter the main query to show only posts with more than 1 connection
*
* @see http://www.wpquestions.com/question/showChronoLoggedIn/id/9778
*/
add_action( 'pre_get_posts', 'wpse_main_query_filter' );
function wpse_main_query_filter( $query ) {
if( $query->is_main_query() ) {
add_filter( 'posts_where', 'wpq_more_than_one_connection' );
}
}
function wpq_more_than_one_connection( $where ) {
global $wpdb;
$where .= " AND {$wpdb->posts}.ID IN ( SELECT p2p_to FROM {$wpdb->p2p} GROUP BY p2p_to HAVING COUNT(*) > 1 ) ";
return $where;
}
because <em>pre_get_posts</em> is activated before <em>posts_where</em>.
Katie comments:
ok this what I do now that works:
function custom_posts( $query ){
if (( ! is_admin() ) && $query->is_main_query())
{
if ( is_home() ) {
$query->set('tag__not_in', array(12728));
$query->set('category__not_in', array(219,1649,2369,989,1169,969,1909,729,689,1869,81,2269,929,173, 2129, 2289, 2708,2169));
}
if (( is_home() ) || ( $query->is_feed && $query->query_vars['all'] != 'true' )) {
add_filter( 'posts_where', 'custom_where' );
}
}
}
add_action( 'pre_get_posts', 'custom_posts' );
function custom_where( $where = '' ){
global $wpdb;
$where .= "AND $wpdb->posts.ID in (SELECT p2p_from
from $wpdb->p2p)";
remove_filter( 'posts_where', 'custom_where' );
return $where;
}
that works but just pulls posts with one connection - I replaced your where with my where and the db went down so Im not sure if the query was too big or what
Dbranes comments:
Strange, works I just tested it on my install, it filtered out the main query.
My connection is <em>cpt_to_post</em>, but I didn't include the connection name in the SQL query, but we could do that if needed.
Whats the outcome of only this query?:
SELECT p2p_to FROM {$wpdb->p2p} GROUP BY p2p_to HAVING COUNT(*) > 1
Katie comments:
I need it to be SELECT p2p_from not p2p_to because I have it set up where all post IDs are in the p2p_from column and I want to pull posts that are in that column 2 or more times
Katie comments:
all my connections are one type of connection - im fiddling with yours to see if I can get it working
Katie comments:
I think maybe SELECT p2p_from FROM $wpdb->p2p GROUP BY p2p_from HAVING COUNT(*) > 1 is what I need but I have so many connections it locks up
would adding a date filter or post ID number larger then X make this query worse or quicker
Katie comments:
I can query SELECT p2p_from FROM $wpdb->p2p GROUP BY p2p_from HAVING COUNT(*) > 1 quick no problem by itself and gives me my result I need
Dbranes comments:
By using p2p_from instead of p2p_to it sounds like you're trying to find:
<blockquote>all songs that are on more than a single record but not finding records that have more than 1 song.
</blockquote>
So if
SELECT p2p_from FROM {$wpdb->p2p] GROUP BY p2p_from HAVING COUNT(*) > 1
works, does
SELECT {$wpdb->posts}.ID FROM {$wpdb->posts} WHERE {$wpdb->posts}.ID IN ( SELECT p2p_from FROM $wpdb->p2p GROUP BY p2p_from HAVING COUNT(*) > 1 ) LIMIT 0, 30
work as well?
Katie comments:
yes its the same thing - that's what I was trying below with Dbranes but its too hard for the server I have to do it this way
Katie comments:
ok hang on