Describes inefficient sql queries generated by https://github.com/jsindos/acfql

acfql-inefficiency.sql

/**
 * PROBLEM 1: Access posts by WordPress category using the category name
 * Category names are stored in the `wp_terms` table
 *
 * `wp_terms` has many `wp_term_relationships` and has primary key `term_id`
 * `wp_term_relationships` belongs to `wp_terms` via key `term_taxonomy_id`
 *
 * `wp_post` has many `wp_term_relationships` and has primary key `id`
 * `wp_term_relationships` belongs to `wp_post` via key `object_id`
 */

/**
 * graphql query
 */
posts(category: "What We Do", postType: "page") {
  id
  post_title
}

/**
 * resulting sql
 */
 
-- `wp_posts` LEFT OUTER JOIN `wp_term_relationships`
SELECT ...
FROM `wp_posts` LEFT OUTER JOIN `wp_term_relationships`
ON `wp_posts`.`id` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_status` = 'publish'
AND `wp_posts`.`post_type` IN ('page');

-- `wp_terms` accessed using relational ids found in `wp_term_relationships`
SELECT ...
FROM `wp_terms`
WHERE `wp_terms`.`term_id` IN (NULL)
AND `wp_terms`.`name` = 'What We Do'
LIMIT 1;

SELECT ...
FROM `wp_terms`
WHERE `wp_terms`.`term_id` IN (2)
AND `wp_terms`.`name` = 'What We Do'
LIMIT 1;

/**
 * PROBLEM 2: Access advanced custom fields 'repeater field' in an efficient way
 * https://www.advancedcustomfields.com/resources/repeater/
 */

/**
 * graphql query
 */
homePage {
  id
  homepageSlider
}

-- homepageSlider is an ACF repeater field which is stored in `wp_postmeta` in the following way:
meta_id post_id meta_key                                   meta_value
2638    440     homepage_slider_1_homepage_slider_heading  Slider 2
2639    440     _homepage_slider_1_homepage_slider_heading field_5b344a95a5657
2640    440     homepage_slider_1_homepage_slider_text     Sam rules
2641    440     _homepage_slider_1_homepage_slider_text    field_5b344acba5658
2642    440     homepage_slider_1_homepage_slider_image    437
2643    440     _homepage_slider_1_homepage_slider_image   field_5b344ad8a5659
2654    440     homepage_slider_0_homepage_slider_heading  Slider 1
2655    440     _homepage_slider_0_homepage_slider_heading field_5b344a95a5657
2656    440     homepage_slider_0_homepage_slider_text     Chritine has nits.
2657    440     _homepage_slider_0_homepage_slider_text    field_5b344acba5658
2658    440     homepage_slider_0_homepage_slider_image    437
2659    440     _homepage_slider_0_homepage_slider_image   field_5b344ad8a5659
2661    440     _homepage_slider                           field_5b344a7ca5656

-- currently accessed using the following SQL query
SELECT ...
FROM `wp_postmeta`
WHERE `wp_postmeta`.`post_id` = 440;

-- then filtering in ORM
.filter(
  postMeta.meta_key.match(/^_homepage_slider_(\d{1})/)
  && Number(postMeta.meta_key.match(/^_homepage_slider_(\d{1})/)[1])
)

-- with a further SQL call to `wp_posts` to retrieve image subfields by id
SELECT ...
FROM `wp_posts`
WHERE `wp_posts`.`id` = 437;