Feature: "Query Loop Block" - "Eager Load" option for "Post" & "Post Meta / Images" (SQL N+1 Query Problem)

[camya]

What problem does this address?

Loading "Post Images" inside a "Query Loop" custom block leads to a SQL N+1 query problem.

If a custom block is added to the "Query Loop Block", and this block wants to show the "Post Thumbnail" or other "Post Meta Data", this triggers an SQL query for each block. (SQL N+1 Problem)

This could be fixed by adding a "Query Loop" option to eager load them. (via SQL "IN" clause)

Via Dropdown

Query Loop "Preload Post Data" Dropdown (Eager load options)

• None
• Posts
• Posts + Meta

Instead of a dropdown, it's also possible to add this option by a filter.

Via Filter (pseudo code)

add_filter('configure_block_core/query', static function($config, $context, $post) {
  // Activates eager loading posts and post meta for every core/query block. Use $context or $post to limit.
  $config['eager_load'] = 'posts_meta';
  return $config;
}, 10, 3);

If any custom block inside the Query Loop later requests one of the eager loaded records (See below), the cached version is retuned and no additional SQL query is triggered.

Current situation (not optimised)

"Query Loop Block" added for 5 posts - Included "Custom Block" triggers + 11 SQL Queries

The user adds a "Query Loop Block", and WordPress will execute the SQL query like the one below.

SELECT ... FROM wp_posts ... LIMIT 0, 5

... the select returns the Post IDs: 1, 2, 3, 4, 5

In order to show the Post title and Post Thumbnail, the custom block now queries the "Post" and "Post Meta" records from the database. (via get_post() and get_the_post_thumbnail())

SELECT ... FROM wp_posts WHERE ID = 1
SELECT ... FROM wp_postmeta WHERE post_id IN (1)

SELECT ... FROM wp_posts WHERE ID = 2
SELECT ... FROM wp_postmeta WHERE post_id IN (2)

SELECT ... FROM wp_posts WHERE ID = 3
SELECT ... FROM wp_postmeta WHERE post_id IN (3)

SELECT ... FROM wp_posts WHERE ID = 4
SELECT ... FROM wp_postmeta WHERE post_id IN (4)

SELECT ... FROM wp_posts WHERE ID = 5
SELECT ... FROM wp_postmeta WHERE post_id IN (5)

What is your proposed solution? (Eager loading)

The proposed solution will optimise the SQL queries by using eager loading "IN" queries. (+ caching)

"Query Loop Block" added for 5 posts (Eager loading enabled) - Included custom Block triggers only + 3 SQL Queries

SELECT ... FROM wp_posts ... LIMIT 0, 5

... the select returns the Post IDs: 1, 2, 3, 4, 5

SELECT ... FROM wp_posts WHERE ID IN (1,2,3,4,5)
SELECT ... FROM wp_postmeta WHERE post_id IN (1,2,3,4,5)

If any custom block instance inside the Query Loop later requests one of the eager loaded records, the already loaded cached record is retuned and no additional SQL query is triggered.

[spacedmonkey]

This may have been fixed in #40572