Skip to content

Instantly share code, notes, and snippets.

@ainsleyclark

ainsleyclark/gist:aa719471b72246215cd725443dcde9b5

Last active March 4, 2019 14:44
Show Gist options
  • Save ainsleyclark/aa719471b72246215cd725443dcde9b5 to your computer and use it in GitHub Desktop.
Save ainsleyclark/aa719471b72246215cd725443dcde9b5 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 --------------
// ----------------------------------------------------
$post_slug=$post->post_name;
global $wpdb;
$sql = "select * from foe_342fj29x2_posts
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 = 'foebar' and foe_342fj29x2_terms.slug = '$post_slug'
and post_type in ('news') and post_status = 'publish'
order by post_date desc, id desc
LIMIT 18446744073709551610 offset 0
) d0
GROUP BY d0.post_type) post_type_0
ON foe_342fj29x2_posts.post_type = post_type_0.post_type
WHERE
((foe_342fj29x2_posts.post_type = post_type_0.post_type AND (FIND_IN_SET(foe_342fj29x2_posts.id, post_type_0.grouped_id) <= 2 and FIND_IN_SET(foe_342fj29x2_posts.id, post_type_0.grouped_id) > 0)))";
$results = $wpdb->get_results($sql);
$post_types = ['news', 'exhibitors', 'speakers'];
// number of post to show per each post_type
$post_per_posttype = 2;
$sql_offset = get_query_var('paged', 0);
if ($sql_offset -1 > 0) {
$sql_offset = ($sql_offset - 1) * $post_per_posttype;
}
// Make SQL Parts
$joinTemplate = [];
$whereTemplate = [];
$whereTemplateAddon = [];
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 = 'foebar' and foe_342fj29x2_terms.slug = '$post_slug'
and post_type in ('$post_type')
and post_status = 'publish'
order by post_date desc, id desc
LIMIT 18446744073709551610 offset #sql_offset#
) 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";
$whereTemplate[] = "foe_342fj29x2_posts.post_type = post_type_$post_type_key.post_type";
$whereTemplateAddon[] = "AND (FIND_IN_SET(foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) <= $post_per_posttype and FIND_IN_SET(foe_342fj29x2_posts.id, post_type_$post_type_key.grouped_id) > 0)";
$selectFoundPostQueryAddon[] = "WHEN foe_342fj29x2_posts.post_type = '$post_type' THEN post_type_$post_type_key.grouped_id";
}
$sql_template = "select #sql_col# from foe_342fj29x2_posts
#join_templates_0#
#join_templates_1#
#join_templates_2#
where
((#where_0# #where_addon_0#)
or
(#where_1# #where_addon_1#)
or
(#where_2# #where_addon_2#))
#append#";
// Assemble Queries
$sqlQuerys['found_post_query'] = $sql_template;
$sqlQuerys['wp_query'] = $sql_template;
$found_post_query_select_addon = "CASE " . implode(' ', $selectFoundPostQueryAddon). " ELSE null END as post_ids";
$found_posts_sql = $sql_template;
foreach ($post_types as $post_type_key => $post_type) {
$sqlQuerys['found_post_query'] = str_replace("#sql_col#", 'count(foe_342fj29x2_posts.id), foe_342fj29x2_posts.post_type,'.$found_post_query_select_addon, $sqlQuerys['found_post_query']);
$sqlQuerys['found_post_query'] = str_replace("#append#", 'group by foe_342fj29x2_posts.post_type', $sqlQuerys['found_post_query']);
$sqlQuerys['found_post_query'] = str_replace("#where_addon_$post_type_key#", '', $sqlQuerys['found_post_query']);
$sqlQuerys['found_post_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", 0, $joinTemplate[$post_type_key]), $sqlQuerys['found_post_query']);
$sqlQuerys['found_post_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['found_post_query']);
$sqlQuerys['wp_query'] = str_replace("#sql_col#", '*', $sqlQuerys['wp_query']);
$sqlQuerys['wp_query'] = str_replace("#append#", "", $sqlQuerys['wp_query']);
$sqlQuerys['wp_query'] = str_replace("#where_addon_$post_type_key#", $whereTemplateAddon[$post_type_key], $sqlQuerys['wp_query']);
$sqlQuerys['wp_query'] = str_replace("#join_templates_$post_type_key#", str_replace("#sql_offset#", $sql_offset, $joinTemplate[$post_type_key]), $sqlQuerys['wp_query']);
$sqlQuerys['wp_query'] = str_replace("#where_$post_type_key#", $whereTemplate[$post_type_key], $sqlQuerys['wp_query']);
}
global $wpdb;
$page_count_per_post_type = $wpdb->get_results($sqlQuerys['found_post_query'], ARRAY_N);
// //dump the results
// echo "<pre>";
// var_dump($page_count_per_post_type);
// echo "</pre>";
// need to pass the max posts possible to the query as it would not be generated correctly
$page_count_per_post_type = $wpdb->get_results($sqlQuerys['found_post_query'], ARRAY_N);
// get the largest page count on a page
$largest_page_count_per_post_type = 0;
$found_posts = 0;
$last_post_id_by_type= [];
$post_order_index= [];
foreach ($page_count_per_post_type as $page_count) {
$largest_page_count_per_post_type = ($page_count[0] > $largest_page_count_per_post_type)? $page_count[0]:$largest_page_count_per_post_type;
$found_posts += $page_count[0];
// set up post order index
$post_order_index[$page_count[1]] = explode(',', $page_count[2]);
}
// page_per_posts is for pagination (post_per_posttype * num_of_posttypes_in_query)
$loop = new WP_Query_CustomSQL($sqlQuerys['wp_query'], array( 'posts_per_page' => $post_per_posttype * 3 , 'found_posts' => $found_posts, 'max_num_pages' => ceil( $largest_page_count_per_post_type / $post_per_posttype )));
// 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;
}
// 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])) {
$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;
}
$columnwidth = 'col-lg-6';
$newscounter = 0;
$speakercounter = 0;
$exhibitorcounter = 0;
if ( $loop->have_posts() ) :
if (count($post_ids_to_fetch)) {
while (count($post_ids_to_fetch) < (count($post_types) * $post_per_posttype)) {
...
while ( $loop->have_posts() ) : $loop->the_post();
$post_type = get_post_type( $post->ID );
//News
if ($post_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) {
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') {
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>';
}
}
endwhile;
}
}
$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>';
endif;
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