1

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' );
  • 1
    try to save separate post meta key for each artist – Anjum Aug 18 '13 at 20:47
0

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.

  • As I just had to work on something that included the ACF plugin, I guess you got the wrong assumption. For whatever reason, the ACF plugin saves every value twice: Once the _fieldname, which is a metry entry containing some weird ID and the the fieldname value, containing the actual value. So if the plugin is doing something like this, then it's likely searching for the other value instead of the real value. And this weird value normally has some sort of random number in it that is type casted to a char. – kaiser Aug 18 '13 at 23:03
  • I'll try your suggestion (and work on my data sanitization), thanks. I think it uses _fieldname with the weird ID to store the name of the field or relate the field name to a custom field group or something else, and it uses the fieldname value to store the actual value. So if my 'key' is lineup_artists than I should be querying for the right value. I would be querying for the wrong value if I used something like _lineup_artists. I use more queries, for instance to get all the events for a particular location, and they all work really well. So I was surprised this wasn't working. – eskimo Aug 19 '13 at 7:40
  • Update: I've changed 'value' to $ArtistID but it isn't working... – eskimo Aug 19 '13 at 7:44
  • Sorry. "It isn't working" isn't much to go on. – s_ha_dum Aug 19 '13 at 13:47
  • I know, I just pointed out that s_ha_dum's suggestion wasn't working. The last day I've noticed that the problem is a bit more complex. I'm querying a relationship field (custom post type artist) which is IN an ACF repeater field. So I'd like to find ALL events which have this artist postID in their repeater field. This makes things complicated. I'll post the answer as soon as I've found a way to solve this. – eskimo Aug 20 '13 at 15:32
0

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'] ) ) );
  • I never knew about these methods to get the relationshipfield post data, thanks! In my case it won't work however, because I have multiple artists on 1 event page. When an artist is clicked, I use var ArtistID = $(this).data('id'); to get the ID (the a tag for the artist has a data-id="17497") to use in the query which is in the AJAX function. Using your method the AJAX function wouldn't know which artist ID to look for because it didn't know which one was clicked right? After clicking an artist it opens a bootstrap modal in which it should display the events for this artist. – eskimo Aug 19 '13 at 7:32
  • @joostvanhoof Yes, documentations are a developers best friend :) Why wouldn't the AJAX callback know which artist ID to lookup? Just pass it to your callback and do a WP_User_Query if that is the artists-post-types real ID. Please edit your question and add your AJAX callback (the full code). – kaiser Aug 19 '13 at 9:44
  • Artists are not users, they are custom post types. I'll edit my post with the full code, give me a few minutes :-) – eskimo Aug 19 '13 at 10:17
  • Then do a normal WP_Query... – kaiser Aug 19 '13 at 10:47
  • the WP_Query is in the function, see my updated original post. So please note I've boiled down the problem to the query which isn't working. And although the AJAX code is working, I'm not sure if this is the best way to do it. But that's another question, so let's focus on the query first. – eskimo Aug 19 '13 at 10:59
0

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.

  • I query on meta_values a lot of times. For instance, when the location of the event is say New York, and I would like to find all events in New York, than I query for that meta value. Would be impossible to find all events with that location by querying jsut on the meta_key. Since I have over 9000 artist (each is a custom post type) I really don't like to switch to using custom taxonomies... – eskimo Aug 19 '13 at 7:34
  • You may run LIKE queries on meta values a lot but @Otto is nonetheless correct. A LIKE query with wildcards is one of the slowest queries you can run. – s_ha_dum Aug 19 '13 at 13:46
  • If it's something you need to run a query on, then a meta_value is always the wrong choice for storing it. Just because the query system supports it doesn't mean it will always be the right way to do things. The system is generic, your problems are specific. The fit isn't always perfect. – Otto Aug 19 '13 at 15:34
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.