wordpress ajax relationship query

Question

Using the relationship field from Advanced Custom Fields I link artists to events. Both these artist and events are custom post types. For each event, the post IDs of the related artists are stored in an array as a custom meta field (lineup_artists).

On each event page I list all the artists. When you click on an artist, I'd like to show all the events where you can find this artist (through an AJAX call which shows the results in a bootstrap modal). I've tested the AJAX call and it's working, but there's something wrong with the query (takes very long to complete).

In my function I have:

$ArtistID = $_POST['ArtistID']; // Gets the ArtistID from the AJAX call

$meta_query = array(
    'key'       => 'lineup_artists',
    'value'     => '"' . $ArtistID .'"',
    'compare'   => 'LIKE'
);

    $args = array(
        'post_type'         => 'events',
        'meta_query'        => array($meta_query),
        'posts_per_page'    => 5,
        'post_status'       => 'publish',   
);

If I dump the results of wp_query, I get the following sql query:

SELECT SQL_CALC_FOUND_ROWS yt_posts.ID FROM yt_posts 

INNER JOIN yt_postmeta ON (yt_posts.ID = yt_postmeta.post_id)

WHERE 1=1 

AND yt_posts.post_type = 'events' 
AND (yt_posts.post_status = 'publish') 

AND ( (yt_postmeta.meta_key = 'lineup_artists' AND CAST(yt_postmeta.meta_value AS CHAR) LIKE '%\"17497\"%') ) 

GROUP BY yt_posts.ID ORDER BY yt_posts.post_date DESC LIMIT 0, 5

When I paste this query in phpmyadmin it takes very long to complete (I've never seen it finish because it takes so long).

Is this because the artist IDs are stored as an array? Someone told me that this is not very efficient and that these relations should be stored in a separate table (which I don't know how to do). Is there something wrong with my query or is this a very inefficient way of querying relations?

---

EDIT: In reply to kaiser (let's use ACDC as an example :-)):

In my event page I have this html/php for each artist (there can be 100+ artists on each event page. $artistID and $artistName are generated in the foreach loop.):

<a class="yt-artist" data-target="#modalArtist" data-toggle="modal" id="ArtistAttr" data-id="<?php echo $artistID; ?>" data-name="<?php echo $artistName ?>"><?php echo $artistName; ?></a>

This code is generated in a foreach loop to get all the artists:

$lineup = get_sub_field('lineup_artists'); // The relationship artists are in a repeater field
if($lineup):
foreach($lineup as $artist):
<a class=... etc

which will result in:

<a class="yt-artist" data-target="#modalArtist" data-toggle="modal" id="ArtistAttr" data-id="17497" data-name="ACDC">ACDC</a>

So clicking an artist shows the modal, and when the modal is shown the AJAX js is fired:

jQuery(document).ready(function() 
{
    $(document).on('click','.yt-artist',function()
    {
        var ArtistID = $(this).data('id');
            var ArtistName = $(this).data('name')
        $('#modalArtist').on('shown',function()
        { 
            jQuery.ajax(
            {  
                type: 'POST',  
                url: 'http://xxx/wp-admin/admin-ajax.php',  
                data: 
                {  
                    action: 'yt_ajax_artist_events_eventpage',  
                    ArtistID: ArtistID,
                                    ArtistName: ArtistName,
                },
                success: function(data, textStatus, XMLHttpRequest)
                {  
                    jQuery('#modalArtist').html(data);  
                },  
                error: function(MLHttpRequest, textStatus, errorThrown)
                {  
                alert(errorThrown);
                }
            });
        });

});

And the function (includes some HTML to inject in the modal):

function yt_ajax_artist_events_eventpage(){

    $ArtistID = $_POST['ArtistID'];
    $ArtistName = $_POST['ArtistName'];

    $meta_query = array(
        'key'       => 'lineup_artists',
        'value'     => $ArtistID,
        'compare'   => 'LIKE'
    );

    $args = array(
        'post_type'         => 'festivals',
        'meta_query'        => array($meta_query),
        'posts_per_page'    => 5,
        'post_status'       => 'publish',   
    );

    $results = '
    <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
        <h2>'.$ArtistName.'</h2>
    </div>
    <div class="modal-body">';
    $loop = new WP_Query($args);
    while ($loop->have_posts()) : $loop->the_post();

    $results .= '<h3>'.get_the_title().'</h3>';

    endwhile;
    wp_reset_query();
    $results .= '</div>';
    die($results);
}  
add_action( 'wp_ajax_nopriv_yt_ajax_artist_events_eventpage', 'yt_ajax_artist_events_eventpage' );  
add_action( 'wp_ajax_yt_ajax_artist_events_eventpage', 'yt_ajax_artist_events_eventpage' );

Answer 1

Look at the very last part of that query-- LIKE '%\"17497\"%'. You are searching for any characters, followed by literally "17497", followed by any characters. I am pretty sure that is not what you want. I think you want any characters, followed by 17497 without the quotes, followed by any characters.

That means the meta_query is written incorrectly.

$meta_query = array(
    'key'       => 'lineup_artists',
    'value'     => $ArtistID,
    'compare'   => 'LIKE'
);

You don't want to be adding your own quotes like that.

You also want to be checking that $ArtistID has a value before trying to use it, and you should be doing some data sanitization and validation before using information in $_POST. $_POST data is not safe data.

Answer 2

Just from reading the documentation I saw that there's get_field('relationship').

$related = get_field( 'relationship' );
foreach ( $related as $r )
{
    setup_postdata( $r );

    // Do stuff
}
wp_reset_postdata();

Inside your AJAX callback you could as well simply call it like this:

$related = get_field( 'relationship', get_the_ID() );

or get the posts related to a specific artist - in case your ArtistID is correct - with

$related = get_field( 'relationship', absint( esc_attr( $_POST['ArtistID'] ) ) );

Answer 3

A LIKE query on the meta_values is going to be very slow pretty much regardless of what you do.

This is not the intended use of meta values, basically. Meta values are not indexed. Meta keys are indexed. Values are intended to be data that is displayed or used, not data that is queried by.

You'd be better off storing the data as a custom taxonomy if you need to query it in this fashion. Taxonomies will be much faster with regards to your query. However, you will have to do some custom coding, presumably, instead of using ACF to generate the code for you.

Answer 4

I've been discussing this problem with the ACF author. The problem was; it's not possible to query a relationship field with custom post types which is IN a repeater field. Therefor $lineup = get_sub_field('lineup') won't work.

If the relationship field is on the root level this meta_query:

$meta_query = array(
    'key'       => 'lineup_artists',
    'value'     => '"' . $ArtistID .'"',
    'compare'   => 'LIKE'
);

will work. Another option the author gave is to use the posts2posts plugin. It creates a table in the WP DB for relationships, and it has an API to query them which works really well.