Skip to content

Instantly share code, notes, and snippets.

@TerrePorter

TerrePorter/gist:fe7b52b4f2e5d8d56ff12dab939ffc4f

Forked from ainsleyclark/gist:aa719471b72246215cd725443dcde9b5
Created March 4, 2019 14:44
Show Gist options
  • Save TerrePorter/fe7b52b4f2e5d8d56ff12dab939ffc4f to your computer and use it in GitHub Desktop.
Save TerrePorter/fe7b52b4f2e5d8d56ff12dab939ffc4f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
class WP_Query_CustomSQL extends WP_Query
{
function get_posts() {
$posts = parent::get_posts();
// overwrite max_num_pages settings if set
$q = &$this->query_vars;
if (isset($q['max_num_pages'])) {
$this->max_num_pages = $q['max_num_pages'];
}
return $posts;
}
}
function paginate_links_with_provided_wpquery( $args = '', $wp_query) {
global $wp_rewrite;
// Setting up default values based on the current URL.
$pagenum_link = html_entity_decode( get_pagenum_link() );
$url_parts = explode( '?', $pagenum_link );
// Get max pages and current page out of the current query, if available.
$total = isset( $wp_query->max_num_pages ) ? $wp_query->max_num_pages : 1;
$current = get_query_var( 'paged' ) ? intval( get_query_var( 'paged' ) ) : 1;
// Append the format placeholder to the base URL.
$pagenum_link = trailingslashit( $url_parts[0] ) . '%_%';
// URL base depends on permalink settings.
$format = $wp_rewrite->using_index_permalinks() && ! strpos( $pagenum_link, 'index.php' ) ? 'index.php/' : '';
$format .= $wp_rewrite->using_permalinks() ? user_trailingslashit( $wp_rewrite->pagination_base . '/%#%', 'paged' ) : '?paged=%#%';
$defaults = array(
'base' => $pagenum_link, // http://example.com/all_posts.php%_% : %_% is replaced by format (below)
'format' => $format, // ?page=%#% : %#% is replaced by the page number
'total' => $total,
'current' => $current,
'aria_current' => 'page',
'show_all' => false,
'prev_next' => true,
'prev_text' => __( '« Previous' ),
'next_text' => __( 'Next »' ),
'end_size' => 1,
'mid_size' => 2,
'type' => 'plain',
'add_args' => array(), // array of query args to add
'add_fragment' => '',
'before_page_number' => '',
'after_page_number' => '',
'hide_page_numbers' => false
);
$args = wp_parse_args( $args, $defaults );
if ( ! is_array( $args['add_args'] ) ) {
$args['add_args'] = array();
}
// Merge additional query vars found in the original URL into 'add_args' array.
if ( isset( $url_parts[1] ) ) {
// Find the format argument.
$format = explode( '?', str_replace( '%_%', $args['format'], $args['base'] ) );
$format_query = isset( $format[1] ) ? $format[1] : '';
wp_parse_str( $format_query, $format_args );
// Find the query args of the requested URL.
wp_parse_str( $url_parts[1], $url_query_args );
// Remove the format argument from the array of query arguments, to avoid overwriting custom format.
foreach ( $format_args as $format_arg => $format_arg_value ) {
unset( $url_query_args[ $format_arg ] );
}
$args['add_args'] = array_merge( $args['add_args'], urlencode_deep( $url_query_args ) );
}
// Who knows what else people pass in $args
$total = (int) $args['total'];
if ( $total < 2 ) {
return;
}
$current = (int) $args['current'];
$end_size = (int) $args['end_size']; // Out of bounds? Make it the default.
if ( $end_size < 1 ) {
$end_size = 1;
}
$mid_size = (int) $args['mid_size'];
if ( $mid_size < 0 ) {
$mid_size = 2;
}
$add_args = $args['add_args'];
$r = '';
$page_links = array();
$dots = false;
if ( $args['prev_next'] && $current && 1 < $current ) :
$link = str_replace( '%_%', 2 == $current ? '' : $args['format'], $args['base'] );
$link = str_replace( '%#%', $current - 1, $link );
if ( $add_args )
$link = add_query_arg( $add_args, $link );
$link .= $args['add_fragment'];
/**
* Filters the paginated links for the given archive pages.
*
* @since 3.0.0
*
* @param string $link The paginated link URL.
*/
$page_links[] = '<a class="prev page-numbers" href="' . esc_url( apply_filters( 'paginate_links', $link ) ) . '">' . $args['prev_text'] . '</a>';
endif;
if (! $args['hide_page_numbers']) {
for ( $n = 1; $n <= $total; $n++ ) :
if ( $n == $current ) :
$page_links[] = "<span aria-current='" . esc_attr( $args['aria_current'] ) . "' class='page-numbers current'>" . $args['before_page_number'] . number_format_i18n( $n ) . $args['after_page_number'] . "</span>";
$dots = true;
else :
if ( $args['show_all'] || ( $n <= $end_size || ( $current && $n >= $current - $mid_size && $n <= $current + $mid_size ) || $n > $total - $end_size ) ) :
$link = str_replace( '%_%', 1 == $n ? '' : $args['format'], $args['base'] );
$link = str_replace( '%#%', $n, $link );
if ( $add_args )
$link = add_query_arg( $add_args, $link );
$link .= $args['add_fragment'];
/** This filter is documented in wp-includes/general-template.php */
$page_links[] = "<a class='page-numbers' href='" . esc_url( apply_filters( 'paginate_links', $link ) ) . "'>" . $args['before_page_number'] . number_format_i18n( $n ) . $args['after_page_number'] . "</a>";
$dots = true;
elseif ( $dots && ! $args['show_all'] ) :
$page_links[] = '<span class="page-numbers dots">' . __( '&hellip;' ) . '</span>';
$dots = false;
endif;
endif;
endfor;
}
if ( $args['prev_next'] && $current && $current < $total ) :
$link = str_replace( '%_%', $args['format'], $args['base'] );
$link = str_replace( '%#%', $current + 1, $link );
if ( $add_args )
$link = add_query_arg( $add_args, $link );
$link .= $args['add_fragment'];
/** This filter is documented in wp-includes/general-template.php */
$page_links[] = '<a class="next page-numbers" href="' . esc_url( apply_filters( 'paginate_links', $link ) ) . '">' . $args['next_text'] . '</a>';
endif;
switch ( $args['type'] ) {
case 'array' :
return $page_links;
case 'list' :
$r .= "<ul class='page-numbers'>\n\t<li>";
$r .= join("</li>\n\t<li>", $page_links);
$r .= "</li>\n</ul>\n";
break;
default :
$r = join("\n", $page_links);
break;
}
return $r;
}
<?php
/*
Template Name:
*/
get_header();
global $wp;
$curURL = home_url( $wp->request );
$contentbrand = $str= preg_replace('/\W\w+\s*(\W*)$/', '$1', get_the_title());
$currpage = get_query_var('paged');
$coloursbuttons = $post->post_name;
?>
<!--====================================
Content Header
=====================================-->
<header class="contentheader bgcolour__white-off contentheader--brandpage" >
<div class="contentheader__bgwrapper d-flex justify-content-center align-items-center flex-column">
<div class="brandpage__headerwrapper d-flex flex-column align-items-center">
<?php if (stripos($curURL, 'scale-up')) { ?>
<img class="brandpage__icon brandpage__icon--scaleup" src=" <?php echo ASSETS; ?>/icon/foebar/icon-fire.svg" alt="Scale Up Logo">
<h1 class="brandpage__heading mt-2 colour__white">Scale Up</h1>
<span class="brand__lead brand__lead--scaleup">Innovation</span>
<span class="brand__lead brand__lead--flip brand__lead--scaleup">Innovation</span>
<p class="brandpage_paragraph colour__white text-center">Where ambitious SME operators unlock the strategies of<br class="d-none d-md-inline">scaling up by engaging the UKs leading experts in growth</p>
</div>
<?php } if (stripos($curURL, 'business-leaders')) { ?>
<img class="brandpage__icon brandpage__icon--leaders" src=" <?php echo ASSETS; ?>/icon/foebar/icon-crown.svg" alt="Business Leaders Logo">
<h1 class="brandpage__heading mt-2 colour__white">Business</h1>
<span class="brand__lead brand__lead--leaders">Leaders</span>
<span class="brand__lead brand__lead--flip brand__lead--leaders">Leaders</span>
<p class="brandpage_paragraph colour__white text-center">Maximum motivation and inspiration first hand:<br class="d-none d-md-inline">how the titans of British business made thier success</p>
</div>
<?php } if (stripos($curURL, 'marketing-experts')) { ?>
<img class="brandpage__icon brandpage__icon--marketing" src=" <?php echo ASSETS; ?>/icon/foebar/icon-star.svg" alt="Marketing Experts Logo">
<h1 class="brandpage__heading mt-2 colour__white">Marketing</h1>
<span class="brand__lead brand__lead--marketing">Experts</span>
<span class="brand__lead brand__lead--flip brand__lead--marketing">Experts</span>
<p class="brandpage_paragraph colour__white text-center">Scale Ups and Entreprenuers deliver top line revenue growth though the<br class="d-none d-md-inline">understanding and application of advanced marketing techniques</p>
</div>
<?php } if (stripos($curURL, 'funding-experts')) { ?>
<img class="brandpage__icon brandpage__icon--funding" src=" <?php echo ASSETS; ?>/icon/foebar/icon-wings.svg" alt="Funding Experts Logo">
<h1 class="brandpage__heading mt-2 colour__white">Funding</h1>
<span class="brand__lead brand__lead--funding">Experts</span>
<span class="brand__lead brand__lead--flip brand__lead--funding">Experts</span>
<p class="brandpage_paragraph colour__white text-center px-4 px-md-0">From tips on getting investment ready to meeting<br class="d-none d-md-inline">the experts who can arrange funding to accelerate growth </p>
</div>
<?php } if (stripos($curURL, 'start-up')) { ?>
<img class="brandpage__icon brandpage__icon--startup" src=" <?php echo ASSETS; ?>/icon/foebar/icon-rocket.svg" alt="Start Up Logo">
<h1 class="brandpage__heading mt-2 colour__white">Start Up</h1>
<span class="brand__lead brand__lead--startup">Accelerator</span>
<span class="brand__lead brand__lead--flip brand__lead--startup">Accelerator</span>
<p class="brandpage_paragraph colour__white text-center">Where smart Start Up founders shortcut early stage<br class="d-none d-md-inline">development and fire on all cyclinders faster</p>
</div>
<?php } ?>
<div class="d-flex mt-3 mt-lg-4 brandpage__buttons">
<a class="notextdec" href="<?php echo home_url( '/visit/' ) ; ?>">
<button type="button" class="homebtn bgcolour__green mx-2 mx-md-3 <?php echo $coloursbuttons ?>">
<span class="homebtn__text">Visit</span>
</button>
</a>
<a class="notextdec" href="<?php echo home_url( '/exhibit/' ) ; ?>">
<button type="button" class="homebtn bgcolour__blue mx-2 mx-md-3 <?php echo $coloursbuttons ?>">
<span class="homebtn__text">Exhibit</span>
</button>
</a>
</div>
<div class="brand__backgroundwrapper">
<img class="brand__background position-absolute" src=" <?php echo IMAGES; ?>/img-<?php echo $post->post_name?>-background-min.png" alt="Rainbow Background">
</div>
</div>
<!-- FOE Bar -->
<?php get_template_part( 'blocks/foebar' ) ?>
</header>
<!--====================================
Content Brands
=====================================-->
<section class="section_contentbrands contentbrands">
<div class="container py-4">
<div class="row mb-2 mb-lg-5">
<div class="col-12 my-0 my-3 my-md-4 my-lg-4">
<h2 class="text-center px-3"><?php echo get_the_title(); ?></h2>
<div class="w-100 mb-3 mb-md-4 mt-1 contentbrands__line contentbrands__line--<?php $postfirst = $post->post_name; $arr = explode('-',trim($postfirst)); echo $arr[0]; ?>"></div>
<?php if ( function_exists('yoast_breadcrumb') ) { yoast_breadcrumb( '<span class="breadcrumbs breadcrumbs--notheader">','</span>' ); } ?>
</div>
<div class="col-12 col-lg-8 <?php if ($currpage != 0) { echo 'mt-3'; } ?>">
<?php if ($currpage == 0) {?><p class=""><?php echo get_the_content(); ?></p><?php } ?>
<?php
// ----------------------------------------------------
// ------------- Custom Query --------------
// ----------------------------------------------------
global $wpdb;
// set post slug
$post_slug=$post->post_name;
// set post types
$post_types = ['news', 'exhibitors', 'speakers'];
// number of post to show per each post_type
$post_per_posttype = 2;
// to keep the last post id that is being displayed on the page
$last_post_id_by_type= [];
// keeps the display of posts order
$post_order_index= [];
// get the current page number
$current_page_number = get_query_var('paged', 1);
// if 0, then we are on page 1
if ($current_page_number==0) { $current_page_number = 1; }
// check if we have sql_offset info for this page load
$transient_key = implode('-', $post_types).'_sqloffset_page';
if ( false === ( $sql_offset = get_transient( $transient_key . $current_page_number) ) ) {
// no valid transient set
foreach ($post_types as $post_type_key => $post_type) {
$sql_offset[$post_type] = 0;
}
}
// Make SQL Parts
$joinTemplate = [];
$whereTemplate = [];
foreach ($post_types as $post_type_key => $post_type) {
$joinTemplate[] = "left join
(select GROUP_CONCAT(id order by post_date desc, id desc) as grouped_id, post_type from (
SELECT id, post_type, post_date
FROM foe_342fj29x2_posts
inner join foe_342fj29x2_term_relationships on foe_342fj29x2_term_relationships.object_id = id
inner join foe_342fj29x2_term_taxonomy on foe_342fj29x2_term_relationships.term_taxonomy_id = foe_342fj29x2_term_taxonomy.term_taxonomy_id
inner join foe_342fj29x2_terms on foe_342fj29x2_term_taxonomy.term_id = foe_342fj29x2_terms.term_id
where foe_342fj29x2_term_taxonomy.taxonomy = 'category' and foe_342fj29x2_terms.slug = '$post_slug'
and post_type in ('$post_type')
and post_status = 'publish'
order by post_date desc, id desc
) d$post_type_key
GROUP BY d$post_type_key.post_type) post_type_$post_type_key
ON foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";
$selectFoundPostQueryAddon[] = "WHEN foe_342fj29x2_posts.post_type = '$post_type' THEN post_type_$post_type_key.grouped_id";
}
// base template for sql query
$sql_template = "select count(foe_342fj29x2_posts.id), foe_342fj29x2_posts.post_type#sql_col# from foe_342fj29x2_posts #join_templates# where foe_342fj29x2_posts.post_type in ('".implode("', '", $post_types)."') group by foe_342fj29x2_posts.post_type";
// Assemble Query
// adds a comma separated list of the post ids that are found in each join statement to the results
$found_post_query_select_addon = "CASE " . implode(' ', $selectFoundPostQueryAddon). " ELSE null END as post_ids";
$sql_template = str_replace("#sql_col#", ','.$found_post_query_select_addon, $sql_template);
// adds generated joins to query
$sql_template = str_replace("#join_templates#", implode("\n", $joinTemplate), $sql_template);
// execute posts build query
global $wpdb;
$page_count_per_post_type = $wpdb->get_results($sql_template, ARRAY_N);
// First place to check if we have results
// echo "<pre>";
// var_dump($page_count_per_post_type);
// echo "</pre>";
// set vars for found_posts, max_posts_count and build the post_order_index
$found_posts = 0;
$max_posts_count=0;
foreach ($page_count_per_post_type as $page_count) {
// make a total post found for all post_types
$found_posts += $page_count[0];
// max posts - total of posts per type
$max_posts_count += $page_count[0];
// set up post order index
$post_order_index[$page_count[1]] = explode(',', $page_count[2]);
}
// set the max pages for the results
$max_posts_num_pages = ceil( $max_posts_count / ($post_per_posttype * count($post_types)));
// get base post ids to show using the normal post_per_posttype
$no_more_post_for_post_type=[];
$post_ids_to_fetch = [];
foreach ($post_order_index as $k => $v) {
// using the sql_offset for this post type, get the next x results from the post_order_index
$t = array_slice($v, $sql_offset[$k], $post_per_posttype);
// if number available is less than or equal to the offset+post_per_posttype, then there are no more post to fetch for this type
if (count($v) <= ($sql_offset[$k] + $post_per_posttype)) {
$no_more_post_for_post_type[] = $k;
}
// keep a growing list of post ids
$post_ids_to_fetch = $post_ids_to_fetch + array_combine($t, array_pad([], count($t), $k));
}
// if we need more posts to make total wanted on page
while (count($post_ids_to_fetch) < (count($post_types) * $post_per_posttype)) {
// get current count of postids to fetch by post type
$post_ids_to_fetch_count_per_type = array_count_values($post_ids_to_fetch);
// remove any post_types that do not have any more posts
$post_types_with_more_posts = [];
foreach ($post_types as $k => $v) {
if (!in_array($v, $no_more_post_for_post_type)) {
$post_types_with_more_posts[] =$v;
} else {
unset($post_ids_to_fetch_count_per_type[$v]);
}
}
// if have more posts
if (count($post_types_with_more_posts)) {
// get the post type with the lowest count from the number of ids in $post_ids_to_fetch
$fetch_more_from_post_type = array_search(min($post_ids_to_fetch_count_per_type), $post_ids_to_fetch_count_per_type);
// get the next post id from the post order index
$t = array_slice($post_order_index[$fetch_more_from_post_type], $sql_offset[$fetch_more_from_post_type] + $post_ids_to_fetch_count_per_type[$fetch_more_from_post_type], 1);
// check if this is the last id available
if ((count($post_order_index[$fetch_more_from_post_type]) <= ($sql_offset[$fetch_more_from_post_type] + $post_ids_to_fetch_count_per_type[$fetch_more_from_post_type] + 1))) {
$no_more_post_for_post_type[] = $fetch_more_from_post_type;
}
$post_ids_to_fetch = $post_ids_to_fetch + array_combine($t, array_pad([], count($t), $fetch_more_from_post_type));
} else {
//There are no more posts to fetch, stop trying to get more
break; // exit the while loop
}
}
// get current count of postids to fetch by post type
$post_ids_to_fetch_count_per_type = array_count_values($post_ids_to_fetch);
// fetch the display posts query
$loop = new WP_Query_CustomSQL(
array(
'post_type' => $post_types,
'post__in' => array_keys($post_ids_to_fetch),
'max_num_pages' => $max_posts_num_pages
)
);
// put the post in the order of the post_types array
if ($loop->have_posts()) {
// make posts index by post_type
$tmpPosts = [];
foreach ($loop->posts as $k => $v) {
$tmpPosts[get_post_type( $v->ID )][$v->ID] = $v;
// update the offset to add in this post to the count
$sql_offset[get_post_type( $v->ID )] += 1;
}
// assemble new ordered posts
$finPosts = [];
foreach ($post_types as $k => $v) {
if (isset($tmpPosts[$v])) {
// reorder based on the sql result order
foreach ($post_order_index[$v] as $k1 => $v1) {
if (isset($tmpPosts[$v][$v1])) {
// make an array of posts grouping them in the post_types order
$finPosts[] = $tmpPosts[$v][$v1];
// set last post id for each type
$last_post_id_by_type[$v] = (int) $v1;
}
}
}
}
// update the $loop with the new ordered posts
$loop->posts = $finPosts;
// save the sql offset data for the next page view
if ($current_page_number + 1 <= $max_posts_num_pages) {
set_transient($transient_key . ($current_page_number + 1), $sql_offset);
}
}
// ----------------------------------------------------
// ------------- Begin Dispaly Code -----------
// ----------------------------------------------------
$columnwidth = 'col-lg-6';
$newscounter = 0;
$speakercounter = 0;
$exhibitorcounter = 0;
if ( $loop->have_posts() ) {
if (count($post_ids_to_fetch)) {
while ( $loop->have_posts() ) {
$loop->the_post();
$post_type = get_post_type( $post->ID );
//News
if ($post_type == 'news' && $newsCounter < $post_ids_to_fetch_count_per_type['news']) {
if ($newscounter == 0) {
echo '<div class="row"><div class="col-12 mb-2 mb-lg-3">';
echo '<span class="contentbrands__title type__weight--medium">News for ' . $contentbrand . '</span>';
}
require( locate_template ('blocks/content-newsrow.php'));
//If last post of this post type
if (isset($last_post_id_by_type[$post_type]) && $last_post_id_by_type[$post_type] == $post->ID) {
echo '</div></div>';
}
$newscounter++;
}
//Exhibitors
if ($post_type == 'exhibitors') {
if ($exhibitorcounter == 0 && $exhibitorCounter < $post_ids_to_fetch_count_per_type['exhibitors']) {
echo '<div class="row"><div class="col-12 mb-2 mb-lg-3">';
echo ' <span class="contentbrands__title type__weight--medium">Exhibitors for ' . $contentbrand . '</span>';
echo '<div class="row mt-2">';
}
require( locate_template ('blocks/content-exhibitor.php'));
$exhibitorcounter++;
//If last post of this post type
if (isset($last_post_id_by_type[$post_type]) && $last_post_id_by_type[$post_type] == $post->ID) {
echo '</div></div></div>';
}
}
//Speakers
if ($post_type == 'speakers' && $speakerCounter < $post_ids_to_fetch_count_per_type['speakers']) {
if ($speakercounter == 0) {
echo '<div class="row"><div class="col-12 mb-2 mb-lg-3 mb-3 mb-lg-4">';
echo '<span class="contentbrands__title type__weight--medium">Experts for ' . $contentbrand . '</span>';
echo '<div class="row mt-2">';
}
require( locate_template ('blocks/content-speaker.php'));
$speakercounter++;
//If last post of this post type
if (isset($last_post_id_by_type[$post_type]) && $last_post_id_by_type[$post_type] == $post->ID) {
echo '</div></div></div>';
}
}
}
}
// handle pagination
$args = array(
'prev_text' => __( 'Previous page', 'twentysixteen' ),
'next_text' => __( 'Next page', 'twentysixteen' ),
'before_page_number' => '<span class="meta-nav screen-reader-text">' . __( 'Page', 'twentysixteen' ) . ' </span>',
'screen_reader_text' => __( '' ),
'type' => 'plain',
'hide_page_numbers' => true
);
// Set up paginated links.
$links = paginate_links_with_provided_wpquery( $args , $loop);
if ( $links ) {
echo '<div class="contentbrand__pagination">' . _navigation_markup( $links, 'pagination') . '</div>';
}
} else {
echo '<h3>No News, Exhibitors or Speakers</h3>';
}
wp_reset_postdata(); ?>
</div>
<div class="col-12 col-lg-4 mt-4 mt-lg-0">
<?php get_sidebar(); ?>
</div>
</div><!-- End Row -->
</div><!-- End Container -->
</section>
<!--====================================
Footer
=====================================-->
<?php get_footer(); ?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment