증거물 C-1 근거: 커밋 3524955
커밋 시각 · 35249559a5c078a37af9710b3649bcf8724f791e
의료진 목록이 사람마다, 항목마다 쿼리를 날렸습니다
저장소 자체는 비공개입니다. 이 페이지에 커밋을 기록된 그대로 옮겼습니다.
커밋 메시지
의료진 목록 N+1 쿼리 수정, 데이터 종류마다 쿼리 한 번
변경 내용
git에 기록된 텍스트 파일만 보여 줍니다. +로 시작하는 줄은 추가, −로 시작하는 줄은 삭제입니다.
바뀐 파일
---
site/wp-content/plugins/hc-team-directory/hc-team-directory.php | 175 +++++++++++++++++++---------
tests/phpunit/repair/TeamQueriesTest.php | 80 +++++++++++++
2 files changed, 201 insertions(+), 54 deletions(-)diff --git a/site/wp-content/plugins/hc-team-directory/hc-team-directory.php b/site/wp-content/plugins/hc-team-directory/hc-team-directory.php
index ac37a2c..07b1f04 100644
--- a/site/wp-content/plugins/hc-team-directory/hc-team-directory.php
+++ b/site/wp-content/plugins/hc-team-directory/hc-team-directory.php
@@ -86,68 +86,135 @@ function hc_team_directory_language_name( $term ) {
}
/**
- * The team list (v1.0 loop, 2016).
+ * Published team members in display order, with their meta, terms and photos loaded in bulk.
*
- * @param int $limit Number of members; -1 for all.
- * @param bool $full Whether to show bios and services.
- * @return string
+ * @param int $limit Number of members; -1 for all.
+ * @return WP_Post[]
*/
-function hc_team_directory_render( $limit = -1, $full = true ) {
- global $wpdb;
- $opts = (array) get_option( 'hctd_display', array() );
- $sql = "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'hc_team' AND post_status = 'publish' ORDER BY menu_order ASC, post_title ASC";
- if ( $limit > 0 ) {
- $sql .= ' LIMIT ' . intval( $limit );
+function hc_team_directory_members( $limit = -1 ) {
+ $query = new WP_Query(
+ array(
+ 'post_type' => 'hc_team',
+ 'post_status' => 'publish',
+ 'posts_per_page' => $limit,
+ 'orderby' => array(
+ 'menu_order' => 'ASC',
+ 'title' => 'ASC',
+ ),
+ 'no_found_rows' => true,
+ 'update_post_meta_cache' => true,
+ 'update_post_term_cache' => true,
+ )
+ );
+ update_post_thumbnail_cache( $query );
+ return $query->posts;
+}
+
+/**
+ * Services each team member provides, in one query for all members.
+ *
+ * @param int[] $member_ids Team member IDs.
+ * @return array<int, string[]> Member ID => service titles.
+ */
+function hc_team_directory_services_by_member( $member_ids ) {
+ $map = array_fill_keys( $member_ids, array() );
+ if ( ! $member_ids ) {
+ return $map;
}
- $ids = $wpdb->get_col( $sql );
- $html = '<ul class="team-list' . ( $full ? ' team-list-full' : '' ) . '">';
- foreach ( $ids as $id ) {
- $member = $wpdb->get_row( "SELECT * FROM {$wpdb->posts} WHERE ID = " . $id );
- $role = $wpdb->get_var( "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id = $id AND meta_key = 'hc_role'" );
- $role_ko = $wpdb->get_var( "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id = $id AND meta_key = '_hollis_ko_role'" );
- $bio_ko = $wpdb->get_var( "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id = $id AND meta_key = '_hollis_ko_content'" );
- $photo_id = $wpdb->get_var( "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id = $id AND meta_key = '_thumbnail_id'" );
- $photo = $photo_id ? wp_get_attachment_image(
- (int) $photo_id,
- 'medium',
- false,
- array(
- 'loading' => 'lazy',
- 'sizes' => '(max-width: 600px) 45vw, 260px',
- 'alt' => '',
- )
- ) : '';
- $langs = $wpdb->get_col( "SELECT t.slug FROM {$wpdb->terms} t JOIN {$wpdb->term_taxonomy} tt ON tt.term_id = t.term_id JOIN {$wpdb->term_relationships} tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy = 'hc_language' AND tr.object_id = $id" );
- $services = $wpdb->get_col( "SELECT p.ID FROM {$wpdb->posts} p JOIN {$wpdb->postmeta} m ON m.post_id = p.ID WHERE p.post_type = 'hc_service' AND p.post_status = 'publish' AND m.meta_key = 'hc_provider' AND m.meta_value = '$id' ORDER BY p.menu_order" );
- $ko = function_exists( 'hollis_lang_is_ko' ) && hollis_lang_is_ko();
- $names = array();
- foreach ( $langs as $slug ) {
- $names[] = hc_team_directory_language_name( (object) array( 'slug' => $slug, 'name' => ucfirst( $slug ) ) );
+ $services = get_posts(
+ array(
+ 'post_type' => 'hc_service',
+ 'post_status' => 'publish',
+ 'numberposts' => -1,
+ 'orderby' => 'menu_order',
+ 'order' => 'ASC',
+ 'suppress_filters' => false,
+ 'update_post_term_cache' => false,
+ 'meta_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- a dozen services; one query replaces one per member.
+ array(
+ 'key' => 'hc_provider',
+ 'value' => array_map( 'strval', $member_ids ),
+ 'compare' => 'IN',
+ ),
+ ),
+ )
+ );
+ foreach ( $services as $service ) {
+ foreach ( (array) get_post_meta( $service->ID, 'hc_provider', false ) as $provider ) {
+ $provider = (int) $provider;
+ if ( isset( $map[ $provider ] ) ) {
+ $map[ $provider ][] = get_the_title( $service );
+ }
}
- $titles = array();
- foreach ( $services as $service_id ) {
- $titles[] = get_the_title( get_post( $service_id ) );
+ }
+ return $map;
+}
+
+/**
+ * One team member as a card.
+ *
+ * @param WP_Post $member Team member.
+ * @param string[] $services Titles of the services they provide.
+ * @param bool $full Whether to include the bio and services.
+ * @return string
+ */
+function hc_team_directory_card( $member, $services, $full ) {
+ $role = hc_team_directory_ko( $member->ID, 'role' );
+ if ( '' === $role ) {
+ $role = (string) get_post_meta( $member->ID, 'hc_role', true );
+ }
+ $photo = get_the_post_thumbnail(
+ $member,
+ 'medium',
+ array(
+ 'loading' => 'lazy',
+ 'sizes' => '(max-width: 600px) 45vw, 260px',
+ 'alt' => '',
+ )
+ );
+ $langs = array();
+ foreach ( (array) get_the_terms( $member, 'hc_language' ) as $term ) {
+ if ( $term instanceof WP_Term ) {
+ $langs[] = hc_team_directory_language_name( $term );
}
+ }
- $html .= '<li class="team-card">';
- if ( $photo ) {
- $html .= '<div class="team-photo">' . $photo . '</div>';
+ $html = '<li class="team-card">';
+ $html .= $photo ? '<div class="team-photo">' . $photo . '</div>' : '';
+ $html .= '<h3 class="team-name">' . esc_html( get_the_title( $member ) ) . '</h3>';
+ $html .= '<p class="team-role">' . esc_html( $role ) . '</p>';
+ if ( $full ) {
+ $bio = hc_team_directory_ko( $member->ID, 'content' );
+ if ( '' === $bio ) {
+ $bio = $member->post_content;
}
- $html .= '<h3 class="team-name">' . esc_html( $member->post_title ) . '</h3>';
- $html .= '<p class="team-role">' . esc_html( $ko && $role_ko ? $role_ko : $role ) . '</p>';
- if ( $full ) {
- $bio = $ko && $bio_ko ? $bio_ko : $member->post_content;
- $html .= '<p class="team-bio">' . esc_html( wp_strip_all_tags( $bio ) ) . '</p>';
- if ( ! empty( $opts['show_languages'] ) || $names ) {
- /* translators: %s: comma-separated languages. */
- $html .= '<p class="team-langs">' . esc_html( sprintf( __( 'Speaks: %s', 'hc-team-directory' ), implode( ', ', $names ) ) ) . '</p>';
- }
- if ( $titles ) {
- /* translators: %s: comma-separated services. */
- $html .= '<p class="team-services">' . esc_html( sprintf( __( 'Sees patients for: %s', 'hc-team-directory' ), implode( ', ', $titles ) ) ) . '</p>';
- }
+ $html .= '<p class="team-bio">' . esc_html( wp_strip_all_tags( $bio ) ) . '</p>';
+ if ( $langs ) {
+ /* translators: %s: comma-separated languages. */
+ $html .= '<p class="team-langs">' . esc_html( sprintf( __( 'Speaks: %s', 'hc-team-directory' ), implode( ', ', $langs ) ) ) . '</p>';
+ }
+ if ( $services ) {
+ /* translators: %s: comma-separated services. */
+ $html .= '<p class="team-services">' . esc_html( sprintf( __( 'Sees patients for: %s', 'hc-team-directory' ), implode( ', ', $services ) ) ) . '</p>';
}
- $html .= '</li>';
+ }
+ $html .= '</li>';
+ return $html;
+}
+
+/**
+ * The team list.
+ *
+ * @param int $limit Number of members; -1 for all.
+ * @param bool $full Whether to show bios and services.
+ * @return string
+ */
+function hc_team_directory_render( $limit = -1, $full = true ) {
+ $members = hc_team_directory_members( $limit );
+ $services = $full ? hc_team_directory_services_by_member( wp_list_pluck( $members, 'ID' ) ) : array();
+ $html = '<ul class="team-list' . ( $full ? ' team-list-full' : '' ) . '">';
+ foreach ( $members as $member ) {
+ $html .= hc_team_directory_card( $member, isset( $services[ $member->ID ] ) ? $services[ $member->ID ] : array(), $full );
}
return $html . '</ul>';
}
diff --git a/tests/phpunit/repair/TeamQueriesTest.php b/tests/phpunit/repair/TeamQueriesTest.php
new file mode 100644
index 0000000..e4dd538
--- /dev/null
+++ b/tests/phpunit/repair/TeamQueriesTest.php
@@ -0,0 +1,80 @@
+<?php
+/**
+ * Exhibit C-1: the team list costs a fixed number of queries, however many people are on the team.
+ *
+ * @package Hollis\Tests
+ */
+
+/**
+ * Team loop queries (N+1).
+ */
+class TeamQueriesTest extends WP_UnitTestCase {
+
+ /**
+ * Adds team members with a photo, two languages and two services each.
+ *
+ * @param int $count Members to add.
+ */
+ private function add_members( $count ) {
+ static $photo = null;
+ if ( null === $photo ) {
+ $photo = self::factory()->attachment->create_upload_object( DIR_TESTDATA . '/images/canola.jpg' );
+ }
+ for ( $i = 0; $i < $count; $i++ ) {
+ $member = self::factory()->post->create(
+ array(
+ 'post_type' => 'hc_team',
+ 'post_title' => 'Member ' . wp_generate_password( 6, false ),
+ 'post_content' => 'Bio.',
+ )
+ );
+ update_post_meta( $member, 'hc_role', 'Dental hygienist' );
+ set_post_thumbnail( $member, $photo );
+ wp_set_object_terms( $member, array( 'english', 'korean' ), 'hc_language' );
+ foreach ( array( 'Cleaning', 'Fillings' ) as $title ) {
+ $service = self::factory()->post->create(
+ array(
+ 'post_type' => 'hc_service',
+ 'post_title' => $title,
+ )
+ );
+ add_post_meta( $service, 'hc_provider', (string) $member );
+ }
+ }
+ }
+
+ /**
+ * Queries run while rendering [hc_team] from a cold object cache, as on a site without a persistent cache.
+ *
+ * @param string $html Rendered markup (out).
+ * @return int
+ */
+ private function queries_for_team_list( &$html = '' ) {
+ wp_cache_flush();
+ wp_load_alloptions(); // Every request loads the options first; not part of the team list.
+ global $wpdb;
+ $before = $wpdb->num_queries;
+ $html = do_shortcode( '[hc_team]' );
+ return $wpdb->num_queries - $before;
+ }
+
+ public function test_eight_members_take_at_most_eight_queries() {
+ $this->add_members( 8 );
+ $html = '';
+ $queries = $this->queries_for_team_list( $html );
+ $this->assertLessThanOrEqual( 8, $queries );
+ $this->assertSame( 8, substr_count( $html, 'class="team-card"' ) );
+ $this->assertSame( 8, substr_count( $html, 'Sees patients for: Cleaning, Fillings' ) );
+ $this->assertSame( 8, substr_count( $html, 'Speaks: English, Korean' ) );
+ }
+
+ public function test_query_count_does_not_grow_with_the_team() {
+ $this->add_members( 8 );
+ $eight = $this->queries_for_team_list();
+ $this->add_members( 8 );
+ $html = '';
+ $sixteen = $this->queries_for_team_list( $html );
+ $this->assertSame( 16, substr_count( $html, 'class="team-card"' ) );
+ $this->assertSame( $eight, $sixteen, "8 members: $eight queries, 16 members: $sixteen queries" );
+ }
+}증명 테스트
tests/phpunit/repair/TeamQueriesTest.php
tests/phpunit/repair/TeamQueriesTest.php, 커밋된 그대로.
<?php
/**
* Exhibit C-1: the team list costs a fixed number of queries, however many people are on the team.
*
* @package Hollis\Tests
*/
/**
* Team loop queries (N+1).
*/
class TeamQueriesTest extends WP_UnitTestCase {
/**
* Adds team members with a photo, two languages and two services each.
*
* @param int $count Members to add.
*/
private function add_members( $count ) {
static $photo = null;
if ( null === $photo ) {
$photo = self::factory()->attachment->create_upload_object( DIR_TESTDATA . '/images/canola.jpg' );
}
for ( $i = 0; $i < $count; $i++ ) {
$member = self::factory()->post->create(
array(
'post_type' => 'hc_team',
'post_title' => 'Member ' . wp_generate_password( 6, false ),
'post_content' => 'Bio.',
)
);
update_post_meta( $member, 'hc_role', 'Dental hygienist' );
set_post_thumbnail( $member, $photo );
wp_set_object_terms( $member, array( 'english', 'korean' ), 'hc_language' );
foreach ( array( 'Cleaning', 'Fillings' ) as $title ) {
$service = self::factory()->post->create(
array(
'post_type' => 'hc_service',
'post_title' => $title,
)
);
add_post_meta( $service, 'hc_provider', (string) $member );
}
}
}
/**
* Queries run while rendering [hc_team] from a cold object cache, as on a site without a persistent cache.
*
* @param string $html Rendered markup (out).
* @return int
*/
private function queries_for_team_list( &$html = '' ) {
wp_cache_flush();
wp_load_alloptions(); // Every request loads the options first; not part of the team list.
global $wpdb;
$before = $wpdb->num_queries;
$html = do_shortcode( '[hc_team]' );
return $wpdb->num_queries - $before;
}
public function test_eight_members_take_at_most_eight_queries() {
$this->add_members( 8 );
$html = '';
$queries = $this->queries_for_team_list( $html );
$this->assertLessThanOrEqual( 8, $queries );
$this->assertSame( 8, substr_count( $html, 'class="team-card"' ) );
$this->assertSame( 8, substr_count( $html, 'Sees patients for: Cleaning, Fillings' ) );
$this->assertSame( 8, substr_count( $html, 'Speaks: English, Korean' ) );
}
public function test_query_count_does_not_grow_with_the_team() {
$this->add_members( 8 );
$eight = $this->queries_for_team_list();
$this->add_members( 8 );
$html = '';
$sixteen = $this->queries_for_team_list( $html );
$this->assertSame( 16, substr_count( $html, 'class="team-card"' ) );
$this->assertSame( $eight, $sixteen, "8 members: $eight queries, 16 members: $sixteen queries" );
}
}