Querying Relationship Fields with SQL

• chriscarvache

• April 1, 2017 at 1:14 am

Hello!

This might be a bit off topic but I figured I’d ask here.

I am in the process of developing a WordPress site that makes heavy use of custom data both native to WordPress (and ACF) and custom tables.

The problem I am experiencing is accessing the data in a relationship field and having it match to a post ID. The query I am using looks like this.

SELECT
  wp_shzat400yd_data_questions.*,
  wp_shzat400yd_posts.post_content,
  wp_shzat400yd_posts.post_title,
  wp_shzat400yd_postmeta.post_id AS section_id,
  wp_shzat400yd_postmeta.meta_value,
  wp_shzat400yd_postmeta.meta_key
FROM wp_shzat400yd_data_questions
  INNER JOIN wp_shzat400yd_posts
    ON wp_shzat400yd_data_questions.question_id = wp_shzat400yd_posts.ID
  INNER JOIN wp_shzat400yd_postmeta
    ON wp_shzat400yd_posts.ID LIKE CONCAT('%', wp_shzat400yd_postmeta.meta_value, '%')
WHERE wp_shzat400yd_postmeta.meta_key = 'questions';

In this query, I need to match the wp_shzat400yd_postmeta.post_id to the wp_shzat400yd_postmeta.meta_value. Since the relationship field stores data as a serialized array, my thought was to match it using a join with a LIKE operator. However, this provides zero results. Just as a note the WHERE clause refers to the questions field (which is the relationship field).

Any ideas on how to get this to work?

• chriscarvache

• April 1, 2017 at 1:54 am

Edit. The above query should use a LEFT JOIN towards the end.

SELECT
  wp_shzat400yd_data_questions.*,
  wp_shzat400yd_posts.post_content,
  wp_shzat400yd_posts.post_title,
  wp_shzat400yd_postmeta.post_id AS section_id,
  wp_shzat400yd_postmeta.meta_value,
  wp_shzat400yd_postmeta.meta_key
FROM wp_shzat400yd_data_questions
  INNER JOIN wp_shzat400yd_posts
    ON wp_shzat400yd_data_questions.question_id = wp_shzat400yd_posts.ID
  LEFT JOIN wp_shzat400yd_postmeta
    ON wp_shzat400yd_posts.ID LIKE CONCAT('%', wp_shzat400yd_postmeta.meta_value, '%')
WHERE wp_shzat400yd_postmeta.meta_key = 'questions';