I want to do something like this

$query_args = array(
    'post_type' => array( 'cpt_1', 'cpt_2' ),
    'post__in' => array( 1, 3, 5, 7 )
);

where I can specify which post type the post__in applies to.

For example, I want to get all the posts from cpt_1, but I only want the cpt_2 ones that are specified in post__in. Is there an SQL hook (like posts_where) that can help me accomplish this? If not I guess I’ll have to do two queries?

WP_Query is not capable of that kind of logic without a lot of help. There are a few things you can do though:

1. Just run two distinct queries, one for each post type. This is simple and quick but your posts aren’t “mixed” together. I don’t know if that is what you want. You’d then need two separate Loops as well. I am sure you can set this up so I am not posting code for it.

2. Run multiple queries and combine them.

   $post_ids = new WP_Query(array(
     // query for one post type
     'fields' => 'ids',
     // other parameters
   ));
   
   $post_ids_2 = new WP_Query(array(
     // query for the others
     'fields' => 'ids',
     // other parameters
   ));
   // join them
   $post_ids = $post_ids->posts + $post_ids_2->posts;
   $posts_qry = new WP_Query(array('post__in'=> $posts_ids));
   

   There is a performance issue with this technique as you need to run three queries to make it work. Your posts are intermingled and you can use one Loop to display them.

3. Write a lot of SQL– ie. a UNION (Code stolen form another post so it is illustrative only. You will need to alter it.)

   $sql = "(SELECT ID FROM {$wpdb->posts} WHERE post_type="books" AND post_status="publish" LIMIT 3)
   UNION ALL
   (SELECT ID FROM {$wpdb->posts} WHERE post_type="magazines" AND post_status="publish" LIMIT 2)
   UNION ALL
   (SELECT ID FROM {$wpdb->posts} WHERE post_type="videos" AND post_status="publish" LIMIT 1)";
   // be sure to add any other conditions you need
   $ids = $wpdb->get_col($sql);
   

4. Now the fun part– filters. You will need post_fields,
   posts_join, and posts_where to do this, or posts_clauses. The
   latter is by far the simplest.

   function post_in_one_type($clauses) {
     remove_filter('posts_clauses','post_in_one_type');
     global $wpdb;
   
     $clauses['fields'] .= ', cpt2.*';
     $clauses['join'] .= ", {$wpdb->posts} as cpt2 ";
     $where = preg_match(
       "/ AND (.*)/",
       $clauses['where'],
       $matches
     );
   
     $cpt2_where = str_replace($wpdb->posts,'cpt2',$matches[1]);
     $cpt2_where = str_replace('cpt_1','cpt_2',$cpt2_where).' AND cpt2.ID IN (1, 3, 5, 7) ';
     $clauses['where'] = ' AND ('.$matches[1].') || ('.$cpt2_where.') ';
   
     return $clauses;
   }
   add_filter('posts_clauses','post_in_one_type');
   $args = array(
       'post_type' => array( 'cpt_1' )
   );
   $q = new WP_Query($args);
   var_dump($q->request);
   

   It is nearly impossible to test that effectively so no promises. I would try the other solutions first and stick with them unless you really see a performance hit that you just cannot bear.

Related:

https://wordpress.stackexchange.com/a/79960/21376
https://wordpress.stackexchange.com/a/79977/21376