How to use ACF Custom Database Tables data with WP

If you are using the ACF Custom Database Tables plugin and have opted to bypass core metadata storage, you’ll likely end up in a situation where you need to create a WP_Query based on data from your custom tables.

The basic approach here is to:

Write some SQL that queries all post IDs matching your conditions, then;
Pass an array of post IDs to the post__in WP_Query arg.

This approach can result in significantly faster WP_Query queries when compared to using meta queries. I ran a few tests against a database containing 1 million records and I saw a 5-second meta query reduce to 1.3 seconds.

Writing your own SQL queries

Query helpers are something we have planned for a future release but at the time of writing, it’s necessary to put your SQL-writing hat on and write some very basic SELECT statements.

If you haven’t written much SQL before, don’t worry. Basic SQL is really easy and you get the hang of it pretty quickly. If you want to learn the basics, check out our primer on using SQL in WordPress.

To get you started, I put together the Gist below. As always, if you run into any trouble and need help, we’re only a support request away.

	<?php

	// Declare the global wpdb variable
	global $wpdb;

	// Write our custom query. In this query, we're only selecting the post_id field of each row that matches our set of
	// conditions. Note the %s placeholders – these are dynamic and indicate that we'll be injecting strings in their place.
	$SQL = "SELECT `post_id` FROM `wp_custom_db_table`
	WHERE `date_start` >= %s
	AND `location` = %s
	ORDER BY `date_start` ASC;";

	// Use $wpdb's prepare() method to replace the placeholders with our actual data. Doing it this way protects against
	// injection hacks as the prepare() method santizes the data accordingly. The output is a prepared, sanitized SQL
	// statement ready to be executed.
	$SQL = $wpdb->prepare( $SQL, current_time( 'Ymd' ), 'some location' );

	// Query the database with our prepared SQL statement, fetching the first column of the matched rows. In our case, we
	// only queried the post_id field of each row so we know that the post_id fields will be the first column. The result
	// here is an array of post_ids (provided we have a match)
	$post_ids = $wpdb->get_col( $SQL );

	if ( $post_ids ) {

	// When calling WP_Query, we no longer need to worry about specifying a post type because we know exactly which post
	// IDs we're after. We've also already ensured the correct order of our post IDs through the ORDER BY xxx ASC
	// portion of our SQL query. So, we just tell WP_Query to return the WP_Post objects in the order of the post IDs we
	// pass to it.
	$query = new WP_Query( [
	'post__in' => $post_ids,
	'post_type' => 'any',
	'posts_per_page' => 4,
	'orderby' => 'post__in',
	] );

	// The rest is exactly as you normally would handle a WP_Query object.
	if ( $query->have_posts() ) {
	while ( $query->have_posts() ) {
	$query->the_post();

	// Do yo thang…
	}

	wp_reset_postdata();
	}

	} else {
	// …
	}

view raw how-to-query-custom-database-tables.php hosted with ❤ by GitHub

Writing your own SQL queries

Good dev stuff, delivered.