Find all posts via SQL beginning with A of type B

Question

I have problem with post and taxonomy query. What i need is to get all post's from custom post type which starts with A and have defined term. Here is my sql:

SELECT * FROM $wpdb->posts, $wpdb->terms
    WHERE post_title LIKE 'А%'
    AND post_type = 'filmi-i-serialii'
    AND post_status = 'publish'
    AND slug = 'igralni';

But this returns me all posts in filmi-i-serialii post-type and not only those that are in the taxonomy igralni.

Answer 1

Try this..

SELECT * 
FROM $wpdb->posts p 
INNER JOIN $wpdb->term_relationships tr 
  ON p.ID = tr.object_id
INNER JOIN $wpdb->term_taxonomy tt 
  ON tr.term_taxonomy_id=tt.term_taxonomy_id 
inner join  $wpdb->terms t 
  on t.term_id=tt.term_id and t.slug='igralni'
WHERE 
  p.post_title LIKE 'А%'        
  AND p.post_type = 'filmi-i-serialii' 
  AND p.post_status = 'publish'

Answer 2

I would read this question as an attempt to restrict the query to posts that are in the taxonomy named igralni, as the question reads "only those that are in the taxonomy igralni", not "those that are tagged igralni". However, the accepted answer actually returns results from the post_tag taxonomy where the term is igralni.

Given that interpretation, this would be what I consider the right way to do it-- with a filter on posts_where. You may need more conditions but this is the basic idea.

function posts_by_letter_wpse_100462($where,$qry) {
  $ppl = $qry->get('posts_by_letter_where');
  if (!empty($ppl)) {
    $where .= " AND post_title LIKE '{$ppl}%' ";
  }
  return $where;
}
add_filter('posts_where','posts_by_letter_wpse_100462',1,2);

$args = array(
  'post_type' => 'filmi-i-serialii',
  'posts_per_page' => 10,
  'posts_by_letter_where' => 'm',
  'tax_query' => array(
    array(
      'taxonomy' => 'post_tag',
      'field' => 'slug',
      'terms' => 'igralni'
    )
  )
);
$wp_query = new WP_Query($args);

Notice that I added a parameter to WP_Query's arguments. Those are passed through unmodified but don't do anything unless you do something with them-- very convenient but undocumented and perhaps unintentional, so caution is advised.

If you really must do it with pure SQL, then run the query (with or without the filter), dump the SQL, steal it, and modify it.

var_dump($wp_query->request); die;