Reply To: Combine more subfields in a single db query

• frafor

• June 24, 2014 at 7:50 pm

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.