Support

Account

Home Forums Add-ons Repeater Field Combine more subfields in a single db query

Solved

Combine more subfields in a single db query

  • Hi there,

    I’ve followed the tutorial here: http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/

    To retrieve posts by a specific sub-field value.

    However, i need what i’d call a “double match” 🙂 I have to get posts according to 2 subfield values. How can i combine these queries in one?

    Another thing that would be nice to add in that query, is to check the post_status key in the wp_posts table.

    Unlickily i don’t have any MySQL knowledge and my attempt to solve the problem failed!

    Can you help me? 🙂

  • I was able to manage it somehow. This is the sql query. It retrieve all the published posts of post_type “attivita”, according to the language (i’m using polylang) and to the “dayname” key_value.

    $language = pll_current_language();
    $rows = $wpdb->get_results($wpdb->prepare( 
                "
                SELECT * 
                FROM wp_posts
    			INNER JOIN wp_postmeta m1
    				ON (wp_posts.ID = m1.post_id)
    			INNER JOIN wp_term_relationships wtr 
    				ON (wp_posts.ID = wtr.object_id)
    			INNER JOIN wp_term_taxonomy wtt 
    				ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id)
    			INNER JOIN wp_terms wt 
    				ON (wt.term_id = wtt.term_id)
    			WHERE 
    				wp_posts.post_type = 'attivita'
    			AND wp_posts.post_status = 'publish'
    			AND wtt.taxonomy = 'language' 
    			AND wt.slug = %s
    			AND (m1.meta_key LIKE %s AND meta_value = %s)
                ",
    			$language,
    			'orario_%_giorno', // meta_name: $ParentName_$RowNumber_$ChildName
                $dayname // meta_value: 'type_3' for example
            ));

    Now i’m trying to display posts according to the starting time, stored as a subfield (attivita means activity, it’s a sort of calendar). all the activities take place in the morning (starting 9am) or in the afternoon (starting 3pm).

    So the code above is inserted in a while loop that counts day-by-day at each loop, and inside the while loop i have two foreach that displays the activities within each single day, for the morning and the afternoon respectively.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.