Início / coding / Perguntas / 79564251
Accepted
Toniq
Asked: 2025-04-09 19:44:00 +0800 CST

MySQL SELECT WHERE IN misturado com outro espaço reservado

  • 772

Esta consulta é válida e retorna os resultados esperados:

$arr = [7,1];//dynamic
$vids = explode(',',$arr);
$in = implode(',', array_fill(0, count($ids), '%d'));

$stmt = $wpdb->prepare("
            SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count
            FROM $media_table as mt
            LEFT JOIN $comments_table ct ON ct.media_id = mt.id
            LEFT JOIN $like_table lt ON lt.media_id = mt.id
            WHERE mt.id IN ($in)
            GROUP BY mt.id 
            ORDER BY $sortOrder $sortDirection", $vids);

Se eu print_r($stmt)obtiver:

     "SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count FROM wp_mvp_reel_media as mt LEFT JOIN wp_mvp_reel_comments ct ON ct.media_id = mt.id LEFT JOIN wp_mvp_reel_like lt ON lt.media_id = mt.id WHERE mt.id IN (7,1) GROUP BY mt.id ORDER BY order_id ASC"

Quando adiciono MAX(lt.user_id = %d) AS user_liked)- a consulta falha.

Se I print_r($stmt), uma string vazia será retornada "":

    $stmt = $wpdb->prepare("
                SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count, MAX(lt.user_id = %d) AS user_liked
                FROM $media_table as mt
                LEFT JOIN $comments_table ct ON ct.media_id = mt.id
                LEFT JOIN $like_table lt ON lt.media_id = mt.id
                WHERE mt.id IN ($in)
                GROUP BY mt.id 
                ORDER BY $sortOrder $sortDirection", $user_id, $vids);
wordpress

1 respostas

  1. Best Answer

    Você precisa fornecer todos os argumentos para wpdb->prepare como uma lista simples na ordem correta. O $user_id deve vir primeiro, seguido por cada elemento individual do array $vids.

    
global $wpdb;


$vids = [7, 1]; 


if (!is_array($vids) || empty($vids)) {
    
    echo "Error: No valid media IDs provided.";
    
} else {
    
    $vids = array_map('intval', $vids);
    $user_id = (int) $user_id; 

    
    $in_placeholders = implode(',', array_fill(0, count($vids), '%d'));

    
    $sql_template = "
        SELECT mt.id, mt.title, mt.options,
               COUNT(DISTINCT ct.id) as comment_count,
               COUNT(DISTINCT lt.id) as like_count,
               MAX(CASE WHEN lt.user_id = %d THEN 1 ELSE 0 END) AS user_liked -- Placeholder 1 for user_id
               -- Using MAX(CASE...) is slightly more robust than MAX(lt.user_id = %d)
               -- but MAX(lt.user_id = %d) should also work in MySQL/MariaDB.
        FROM {$media_table} as mt
        LEFT JOIN {$comments_table} ct ON ct.media_id = mt.id
        LEFT JOIN {$like_table} lt ON lt.media_id = mt.id
        WHERE mt.id IN ({$in_placeholders}) -- Placeholders 2, 3, ... N for vids
        GROUP BY mt.id
        ORDER BY {$sortOrder} {$sortDirection}"; 

    
    $args = array_merge([$user_id], $vids); 

    
    $stmt = $wpdb->prepare($sql_template, $args);

    
    if (empty($stmt)) {
         echo "Error preparing statement: " . $wpdb->last_error;
         
    } else {
        

        
        $results = $wpdb->get_results($stmt);

        
    }
}
    • 3

relate perguntas