custom multisite query for home page WordPress
- SOLVED
It needs to return all published posts on site 1, and also posts on site 8 that have a special custom field set, this is what I have but it doesn't really work:
$sql="SELECT SQL_CALC_FOUND_ROWS wp_1_posts.*, wp_8_posts.*
FROM wp_1_posts, wp_8_posts, wp_8_postmeta
WHERE 1=1 AND wp_1_posts.post_type = 'post' AND wp_1_posts.post_status = 'publish'
AND wp_8_posts.post_type = 'post' AND wp_8_posts.post_status = 'publish'
AND wp_8_postmeta.meta_key = '_show_on_main' AND wp_8_postmeta.meta_value = 1
ORDER BY wp_1_posts.post_date, wp_8_posts.post_date DESC LIMIT 0, 25";
It's limited to 25 but I'll need to know how many rows there are in total for my pagination functionality. The posts have to be ordered by date independently of which site they come from.
Answers (2)
John Cotton answers:
You could vary where you do the post_type/status WHERE clause, but this should give
you it:
SELECT SQL_CALC_FOUND_ROWS * FROM (
SELECT * FROM wp_1_posts WHERE post_type = 'post' AND post_status = 'publish'
UNION ALL
SELECT p.* FROM wp_8_posts p INNER JOIN wp_8_postmeta pm ON pm.post_id = p.id WHERE post_type = 'post' AND post_status = 'publish' AND pm.meta_key = '_show_on_main' AND pm.meta_value = 1
) T1
ORDER BY post_date DESC LIMIT 0, 25;
Eric Kittell comments:
hey that looks good, but it's not returning any results, just to test it I setup simpler queries and both of these return the expected results:
SELECT SQL_CALC_FOUND_ROWS * FROM wp_1_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 0, 25;
SELECT SQL_CALC_FOUND_ROWS * FROM wp_8_posts p INNER JOIN wp_8_postmeta pm ON pm.post_id = p.id WHERE post_type = 'post' AND post_status = 'publish' AND pm.meta_key = '_show_on_main' AND pm.meta_value = 1
ORDER BY post_date DESC LIMIT 0, 25;
John Cotton comments:
Hi Eric
It think what's happening is that there's an error (hence no results) since you can't UNION those two queries - they don't return the same number of columns.
You need to have it exactly as I entered it:
SELECT p.*
from the second one otherwise you're getting all the postmeta columns and that throws out the union.
If you tell me what you want to get back (which columns) I can rewrite it so that it works.
JC
John Cotton comments:
PS Assuming that you're using $wpdb->get_results with this, you could
echo $wpdb->last_error;
just after the query to see what's happening.
Eric Kittell comments:
ok there was an error because somebody added an extra column to the wp_1_posts table!
it works now, thanks for helping me debug it!
Ashfame answers:
You can carry out normal queries in a multisite setup across sites like this:
<?php
global $switched;
switch_to_blog(7);
get_posts($args); // it will act as if it was run in standalone wordpress setup whose blog ID is 7
restore_current_blog(); // and this returns you to the current blog you are on
?>
So this way you can just switch to the blog, query and then switch to yet another, query and display them as you want.
Eric Kittell comments:
yeah I know of that BUT then I'd have to do 2 different queries and sort through the results with PHP to get them ordered by date, this site is slow enough as it is...
Ashfame comments:
Use get_posts to retrieve all the posts. Code link - http://codex.wordpress.org/Function_Reference/get_posts
<?php $args = array(
'numberposts' => -1, // this will return all posts
'meta_key' => '_show_on_main',
'meta_value' => 1 ); ?>
So the code should be like:
<?php
global $switched;
switch_to_blog(1);
$args = array(
'numberposts' => -1, // this will return all posts
'meta_key' => '_show_on_main',
'meta_value' => 1 );
get_posts($args);
switch_to_blog(8);
$args = array(
'numberposts' => -1, // this will return all posts
'meta_key' => '_show_on_main',
'meta_value' => 1 );
get_posts($args);
restore_current_blog(); // and this returns you to the current blog you are on
?>
Ashfame comments:
That's what WordPress APIs offer, and sorting is not really gonna slow you down.
Eric Kittell comments:
I'm asking for a custom sql query, this is not a question about the Worpdress API
Ashfame comments:
<blockquote>If there's a way to do this with query vars instead of custom sql that would be great, but I doubt it.</blockquote>
I believe you were willing to accept WP APIs
Eric Kittell comments:
sorry I meant if there was a GOOD way to do it with the API, I'm sure there is not, I don't think dealing with an array of thousands of posts is a good way to do this...