Array to string转换错误：ClickHouse与SakilaDB跨库关联查询问题

问题场景 #

从ClickHouse查询dim_tahun和dim_lokasi表数据，通过foreach将字段存入数组后，用$id_lokasi数组作为条件查询SakilaDB时触发“Array to string conversion”错误。原代码如下：

$factpelanggan = DB::connection('clickhouse')
    ->select('SELECT t.id_tahun, t.tahun, l.id_lokasi
from dim_tahun t, dim_lokasi l');

foreach ($factpelanggan as $value) {
    $id_tahun[] = $value['id_tahun'];
    $id_lokasi[] = $value['id_lokasi'];
    $tahun[] = $value['tahun'];
}


$factpelanggan2 = DB::connection('sakiladb')
->select("SELECT YEAR(ren.rental_date) as YEAR , negara.country , count(customer_id) as jml_pelanggan
from rental ren 
where negara.country_id = $id_lokasi
inner join inventory inven on ren.inventory_id = inven.inventory_id 
inner join store toko on inven.store_id = toko.store_id
inner join address alamat on toko.address_id =alamat.address_id
inner join city kota on alamat.city_id =kota.city_id
inner join country negara on kota.country_id = negara.country_id
group by year(ren.rental_date) , negara.country 

");

错误原因 #

1. 数组直接拼接进SQL：$id_lokasi是数组类型，直接放到字符串SQL里会触发“数组转字符串”的转换错误，SQL无法识别数组格式。
2. SQL语法错误：WHERE子句写在了JOIN之前，此时negara表还未关联，SQL会找不到negara.country_id字段。
3. 条件逻辑错误：如果要匹配多个id_lokasi值，应该用IN而非=，=只能匹配单个值。

修复方案 #

方案1：安全拼接数组（需确保数组元素安全） #

先将数组转成逗号分隔的字符串，调整SQL中WHERE的位置，改用IN条件：

$factpelanggan = DB::connection('clickhouse')
    ->select('SELECT t.id_tahun, t.tahun, l.id_lokasi
from dim_tahun t, dim_lokasi l');

$id_lokasi = [];
foreach ($factpelanggan as $value) {
    $id_lokasi[] = $value['id_lokasi'];
}

// 过滤非数值元素，防止SQL注入
$id_lokasi_str = implode(',', array_map('intval', $id_lokasi));

$factpelanggan2 = DB::connection('sakiladb')
->select("SELECT YEAR(ren.rental_date) as YEAR , negara.country , count(customer_id) as jml_pelanggan
from rental ren 
inner join inventory inven on ren.inventory_id = inven.inventory_id 
inner join store toko on inven.store_id = toko.store_id
inner join address alamat on toko.address_id =alamat.address_id
inner join city kota on alamat.city_id =kota.city_id
inner join country negara on kota.country_id = negara.country_id
where negara.country_id IN ($id_lokasi_str)
group by year(ren.rental_date) , negara.country 
");

方案2：使用查询构建器（推荐，防SQL注入） #

用Laravel的查询构建器来处理关联和条件，自动处理数组参数：

$factpelanggan = DB::connection('clickhouse')
    ->select('SELECT t.id_tahun, t.tahun, l.id_lokasi
from dim_tahun t, dim_lokasi l');

$id_lokasi = array_column($factpelanggan, 'id_lokasi');

$factpelanggan2 = DB::connection('sakiladb')
    ->table('rental as ren')
    ->join('inventory as inven', 'ren.inventory_id', '=', 'inven.inventory_id')
    ->join('store as toko', 'inven.store_id', '=', 'toko.store_id')
    ->join('address as alamat', 'toko.address_id', '=', 'alamat.address_id')
    ->join('city as kota', 'alamat.city_id', '=', 'kota.city_id')
    ->join('country as negara', 'kota.country_id', '=', 'negara.country_id')
    ->whereIn('negara.country_id', $id_lokasi)
    ->selectRaw('YEAR(ren.rental_date) as YEAR, negara.country, count(customer_id) as jml_pelanggan')
    ->groupByRaw('year(ren.rental_date), negara.country')
    ->get();

内容的提问来源于stack exchange，提问作者Ronaldo Firmansyah