WP_Query and the Database API

<?php
// Run: wp eval-file book-query.php
$query = new WP_Query(
	array(
		'post_type'      => 'lp_book',
		'post_status'    => 'publish',
		'posts_per_page' => 12,
		'paged'          => 1,
		'tax_query'      => array(
			'relation' => 'AND',
			array( 'taxonomy' => 'lp_genre', 'field' => 'slug', 'terms' => array( 'fantasy', 'science-fiction' ) ),
			array( 'taxonomy' => 'lp_series', 'operator' => 'NOT EXISTS' ),
		),
		'meta_query'     => array(
			'relation'  => 'AND',
			'length'    => array( 'key' => 'lp_pages', 'value' => array( 300, 600 ), 'compare' => 'BETWEEN', 'type' => 'NUMERIC' ),
			'published' => array( 'key' => 'lp_published_on', 'value' => '2020-01-01', 'compare' => '>=', 'type' => 'DATE' ),
		),
		'date_query'     => array(
			array( 'column' => 'post_date_gmt', 'after' => '90 days ago', 'inclusive' => true ),
		),
		'orderby'        => array( 'published' => 'DESC', 'title' => 'ASC' ),
	)
);

printf( "found_posts=%d max_num_pages=%d post_count=%d\n", $query->found_posts, $query->max_num_pages, $query->post_count );
while ( $query->have_posts() ) {
	$query->the_post();
	printf( "%s  %s  %d pages\n", get_post_meta( get_the_ID(), 'lp_published_on', true ), get_the_title(), get_post_meta( get_the_ID(), 'lp_pages', true ) );
}
wp_reset_postdata();

echo $query->request, "\n";

found_posts=82 max_num_pages=7 post_count=12
2026-08-18  Book 17634  577 pages
2026-06-11  Book 17648  593 pages
2026-04-06  Book 18682  464 pages
...

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1
  AND ( wp_posts.post_date_gmt >= '2026-07-30 15:18:44' )
  AND ( wp_term_relationships.term_taxonomy_id IN (2,6)
        AND NOT EXISTS (
          SELECT 1 FROM wp_term_relationships
          INNER JOIN wp_term_taxonomy
            ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
          WHERE wp_term_taxonomy.taxonomy = 'lp_series'
            AND wp_term_relationships.object_id = wp_posts.ID ) )
  AND ( ( wp_postmeta.meta_key = 'lp_pages'
          AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '300' AND '600' )
        AND ( mt1.meta_key = 'lp_published_on' AND CAST(mt1.meta_value AS DATE) >= '2020-01-01' ) )
  AND wp_posts.post_type = 'lp_book' AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY CAST(mt1.meta_value AS DATE) DESC, wp_posts.post_title ASC
LIMIT 0, 12

<?php
// Needs SAVEQUERIES. Run: wp eval-file lean-query.php
global $wpdb;

$args = array(
	'post_type'      => 'lp_book',
	'posts_per_page' => 12,
	'tax_query'      => array( array( 'taxonomy' => 'lp_genre', 'field' => 'slug', 'terms' => 'fantasy' ) ),
);
$lean = array(
	'fields'                 => 'ids',  // IDs only: no post objects are built.
	'no_found_rows'          => true,   // No SQL_CALC_FOUND_ROWS and no FOUND_ROWS() query.
	'update_post_meta_cache' => false,  // Do not preload the meta of the results.
	'update_post_term_cache' => false,  // Do not preload their terms.
);

foreach ( array( 'default' => array(), 'lean' => $lean ) as $label => $extra ) {
	wp_cache_flush(); // Same cold start for both runs.
	$before = count( $wpdb->queries );
	$query  = new WP_Query( array_merge( $args, $extra, array( 'cache_results' => false ) ) );
	$run    = array_slice( $wpdb->queries, $before );
	printf( "%-8s queries=%d time=%.2f ms\n", $label, count( $run ), 1000 * array_sum( array_column( $run, 1 ) ) );
}

default  queries=9 time=5.03 ms
lean     queries=5 time=3.63 ms

<?php
// lumen-catalog/src/Stock.php: data access for the {prefix}lp_stock movement history.
namespace Lumen\Catalog;

defined( 'ABSPATH' ) || exit;

/**
 * Records and reads stock movements. The current level of a book is its lp_stock post meta;
 * the table keeps one row per change. Every value reaches SQL through a placeholder.
 */
final class Stock {

	/** Full table name for the current site: wp_lp_stock, or wp_2_lp_stock on a sub-site. */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'lp_stock';
	}

	/** Sets the current level and logs the change. Movement ID, 0 when the level did not change, false on error. */
	public static function record( int $book_id, int $quantity, string $sku = '' ) {
		global $wpdb;
		$quantity = max( 0, $quantity );
		$previous = (int) get_post_meta( $book_id, 'lp_stock', true );
		if ( $quantity === $previous && metadata_exists( 'post', $book_id, 'lp_stock' ) ) {
			return 0;
		}
		$inserted = $wpdb->insert(
			self::table(),
			array(
				'book_id'         => $book_id,
				'distributor_sku' => sanitize_text_field( $sku ),
				'delta'           => $quantity - $previous,
				'quantity'        => $quantity,
				'changed_at'      => current_time( 'mysql', true ),
			),
			array( '%d', '%s', '%d', '%d', '%s' )
		);
		if ( false === $inserted ) {
			return false;
		}
		update_post_meta( $book_id, 'lp_stock', $quantity );
		return (int) $wpdb->insert_id;
	}

	/** The newest movement of a book as an associative array, or null when it has none. */
	public static function last_movement( int $book_id ): ?array {
		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT id, book_id, distributor_sku, delta, quantity, changed_at FROM %i
				WHERE book_id = %d ORDER BY changed_at DESC, id DESC LIMIT 1',
				self::table(),
				$book_id
			),
			ARRAY_A
		);
	}

	/** Rows changed (0 when every movement already had this SKU), or false on error. wpdb::update() prepares the values. */
	public static function set_sku( int $book_id, string $sku ) {
		global $wpdb;
		return $wpdb->update(
			self::table(),
			array( 'distributor_sku' => sanitize_text_field( $sku ) ),
			array( 'book_id' => $book_id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/** Net change per book since a UTC date, keyed by book ID; books without movements are absent. */
	public static function net_changes( array $book_ids, string $since ): array {
		global $wpdb;
		$book_ids = array_values( array_filter( array_map( 'absint', $book_ids ) ) );
		if ( ! $book_ids ) {
			return array();
		}
		$placeholders = implode( ', ', array_fill( 0, count( $book_ids ), '%d' ) );
		$rows         = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT book_id, SUM(delta) AS net FROM %i WHERE changed_at >= %s AND book_id IN ($placeholders) GROUP BY book_id", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				self::table(),
				$since,
				...$book_ids
			),
			OBJECT_K
		);
		return array_map( static fn( $row ) => (int) $row->net, $rows );
	}

	/** Published books whose stock fell the most since a UTC date, largest fall first. */
	public static function largest_falls( string $since, int $limit = 10 ): array {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT p.ID, p.post_title, SUM(s.delta) AS net FROM %i AS s
				INNER JOIN %i AS p ON p.ID = s.book_id
				WHERE s.changed_at >= %s AND p.post_type = %s AND p.post_status = %s
				GROUP BY p.ID, p.post_title HAVING net < 0
				ORDER BY net ASC, p.post_title ASC LIMIT %d',
				self::table(),
				$wpdb->posts,
				$since,
				'lp_book',
				'publish',
				$limit
			)
		);
	}
}

int(1)                                      # record( 4, 7, 'DST-LEDGER-01' ): movement ID 1, delta -5
int(0)                                      # record( 4, 7 ) again: the level did not change, nothing logged
int(1)                                      # set_sku( 4, 'DST-LEDGER-02' )
int(0)                                      # the same again: nothing changed, which is not an error
{"id":"1","book_id":"4","distributor_sku":"DST-LEDGER-02","delta":"-5","quantity":"7","changed_at":"2026-10-28 19:53:14"}
string(1) "7"                               # get_post_meta( 4, 'lp_stock', true ): the current level
{"4":-5,"5":-13}                            # after record( 5, 170 ) and record( 6, 0 ); unknown IDs are absent
[{"ID":"5","post_title":"The Glass Orchard","net":"-13"},{"ID":"4","post_title":"The Lighthouse Ledger","net":"-5"},{"ID":"6","post_title":"The Quiet Lighthouse","net":"-3"}]
NULL                                        # get_var() of a query naming a missing column
last_error=Unknown column 'nope' in 'SELECT'

<?php
// Needs SAVEQUERIES. Run: wp eval-file measure.php
global $wpdb;

// Median time of a query over 11 runs, then its EXPLAIN plan.
$measure = static function ( string $label, callable $run ) use ( $wpdb ) {
	$ms = array();
	for ( $i = 0; $i < 11; $i++ ) {
		$wpdb->queries = array();
		$run();
		$ms[] = 1000 * end( $wpdb->queries )[1];
	}
	sort( $ms );
	printf( "%s: median %.2f ms\n", $label, $ms[5] );
	foreach ( $wpdb->get_results( 'EXPLAIN ' . end( $wpdb->queries )[0], ARRAY_A ) as $row ) {
		printf( "  %-21s %-6s %-16s %-6s %s\n", $row['table'], $row['type'], $row['key'], $row['rows'], $row['Extra'] );
	}
};
$books = array( 'post_type' => 'lp_book', 'cache_results' => false, 'no_found_rows' => true, 'fields' => 'ids' );

// 1. The archive of the pre_get_posts example, then the default order.
$measure( 'archive by lp_published_on', static fn() => new WP_Query( $books + array(
	'posts_per_page' => 24,
	'meta_query'     => array( 'published' => array( 'key' => 'lp_published_on', 'type' => 'DATE' ) ),
	'orderby'        => array( 'published' => 'DESC', 'title' => 'ASC' ),
) ) );
$measure( 'archive by post_date', static fn() => new WP_Query( $books + array( 'posts_per_page' => 24 ) ) );

// 2. The 20 fantasy books with the fewest units under 5: post meta, then the lp_stock_levels comparison table.
$measure( 'low stock from postmeta', static fn() => new WP_Query( $books + array(
	'posts_per_page' => 20,
	'tax_query'      => array( array( 'taxonomy' => 'lp_genre', 'field' => 'slug', 'terms' => 'fantasy' ) ),
	'meta_query'     => array( 'stock' => array( 'key' => 'lp_stock', 'value' => 5, 'compare' => '<', 'type' => 'NUMERIC' ) ),
	'orderby'        => array( 'stock' => 'ASC', 'ID' => 'ASC' ),
) ) );
$sql = $wpdb->prepare(
	'SELECT p.ID FROM %i AS s
	INNER JOIN %i AS p ON p.ID = s.book_id
	INNER JOIN %i AS tr ON tr.object_id = p.ID AND tr.term_taxonomy_id = %d
	WHERE s.quantity < %d AND p.post_type = %s AND p.post_status = %s
	ORDER BY s.quantity ASC, p.ID ASC LIMIT 20',
	$wpdb->prefix . 'lp_stock_levels',
	$wpdb->posts,
	$wpdb->term_relationships,
	get_term_by( 'slug', 'fantasy', 'lp_genre' )->term_taxonomy_id,
	5,
	'lp_book',
	'publish'
);
$measure( 'low stock from lp_stock_levels', static fn() => $wpdb->get_col( $sql ) );

archive by lp_published_on: median 54.30 ms
  wp_postmeta           range  meta_key         37844  Using where; Using temporary; Using filesort
  wp_posts              eq_ref PRIMARY          1      Using where
archive by post_date: median 0.10 ms
  wp_posts              ref    type_status_date 19913  Using where; Using index
low stock from postmeta: median 12.87 ms
  wp_term_relationships ref    term_taxonomy_id 3308   Using where; Using index; Using temporary; Using filesort
  wp_posts              eq_ref PRIMARY          1      Using where
  wp_postmeta           ref    post_id          4      Using where
low stock from lp_stock_levels: median 2.16 ms
  tr                    ref    term_taxonomy_id 3308   Using index; Using temporary; Using filesort
  s                     eq_ref PRIMARY          1      Using where
  p                     eq_ref PRIMARY          1      Using where