mysql - 在 PhpMyAdmin 中运行时，WHERE IN 给我不同的结果

Question

当我在 phpMyAdmin 中运行以下查询时

    SELECT 
        locations.name,
        locations.id,
        locations.lon,
        locations.lat,
        locations_categories.category_id as catId,
        locations_categories.location_id as locId,
        haversine(40,90,locations.lon,locations.lat) as distance
    FROM locations_categories, locations  
    WHERE locations.id = locations_categories.location_id
    AND locations_categories.category_id IN ("9","1","7")  
    ORDER BY `distance` ASC

它给了我以下正确的结果。

但是当我在我的 Laravel 应用程序中运行以下命令时，它只返回 catId 为 9 的位置。

        // dump($request->categories);
        $categories = implode('","',$request->categories);

        //dd($categories);


        $statement = <<<'ENDSTATEMENT'
        SELECT 
            locations.name,
            locations.id,
            locations.lon,
            locations.lat,
            locations_categories.category_id as catId,
            locations_categories.location_id as locId,
            haversine(?,?,locations.lon,locations.lat) as distance
        FROM locations_categories, locations  
        WHERE locations.id = locations_categories.location_id
        AND locations_categories.category_id IN (?)  
        ORDER BY `distance` ASC
ENDSTATEMENT;

    $locations = DB::select($statement, array($request->lon, $request->lat, $categories));

结果

dd($categories);

是下面的截图

我的问题是，为什么这可以工作并返回 PhpMyAdmin 中所需的所有值，但在应用程序中运行时却不能？

Answer 1

在这里使用 Laravel 代码，而不是单个原始查询。这使您可以利用 whereIn 函数，该函数可以将 PHP 值数组正确绑定(bind)到 WHERE 表达式。

$locations = DB::table('locations_categories as lc')
    ->join('locations', 'locations.id', '=', 'lc.location_id')
    ->whereIn('lc.category_id', $request->categories)
    ->selectRaw('locations.name, locations.id, locations.lon, locations.lat,'
        . 'lc.category_id as catId,'
        . 'lc.location_id as locId,'
        . 'haversine(?, ?, locations.lon,locations.lat) as distance',
    [$request->lon, $request->lat])
->get();