title: wp mysql query find post types by another post type
body: <p>first time asking, so be gentile.</p>
  
  <p>My problem is the admin search in wordpress. I need to find a custom post type based not only if the search term is found on it, but also same custom post type based if that search term is found on another custom post type that the first one is related to.</p>
  
  <p>If someone is not familiar with wordpress database structure, here's an image of it with relationships:
  <a href="https://i.stack.imgur.com/f9F5L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f9F5L.png" alt="enter image description here"></a></p>
  
  <p>I will use just wp_posts and wp_postmeta</p>
  
  <p>So I need to find all <strong>coupons</strong> from wp_posts that have 'lorem' text in title or content -> </p>
  
  <pre><code>`post_type` LIKE 'coupon' AND (`post_title` LIKE '%lorem%' OR `post_content` LIKE '%lorem%')
  </code></pre>
  
  <p>But I also have in wp_posts custom post type <strong>merchant</strong> that is related to <strong>coupons</strong> in wp_postmeta where <code>post_id</code> is coupon id, <code>meta_key</code> is 'merchant' and <code>meta_value</code> is a serialized value like a:1:{i:0;s:4:"3249";} where 3249 is merchant id . (I know, it's weird).</p>
  
  <p>So I need to somehow find out what merchants have, find their related coupons and then those coupons to be added in the initial query:</p>
  
  <pre><code>`post_type` LIKE 'merchant' AND (`post_title` LIKE '%lorem%' OR `post_content` LIKE '%lorem%') 
  </code></pre>
  
  <p>I have managed to find a way, but it takes to long</p>
  
  <pre><code>SELECT SQL_CALC_FOUND_ROWS wp_posts. * 
  FROM wp_posts
  LEFT JOIN wp_postmeta AS pm 
      ON wp_posts.ID = pm.post_id
  RIGHT JOIN wp_posts AS p2 
      ON pm.meta_value LIKE CONCAT(  '%a:1:{i:0;s:', LENGTH( p2.ID ) ,  ':"', p2.ID,  '";}%' ) 
  WHERE 
      ( wp_posts.post_title LIKE  '%lorem%'       
      OR p2.post_title LIKE  '%lorem%'
      OR p2.post_content LIKE  '%lorem%'
      OR wp_posts.post_content LIKE  '%lorem%'
      )
  AND wp_posts.post_type =  'coupon'
  AND p2.post_type =  'merchant'
  AND pm.meta_key =  'merchant'
  ORDER BY wp_posts.post_title LIKE  '%lorem%' DESC , wp_posts.post_date DESC 
  LIMIT 0 , 20
  </code></pre>
  
  <p>for 7 results it takes 1.6027 sec.</p>
  
  <p>Thanks in advance</p>