"Array to string conversion"我想从 2 个数据库连接并建立关系，但我真的不知道正确的条件如何

Question

我从'clickhouse'获取数据，并将其存储在foreach数组中。之后，我尝试从'sakiladb'中获取数据，该数据与我在1之前存储的'clickhouse'中的数据具有相同的条件

$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 
");

错误通知

Answer 1

您声明$id_locasi为数组，但您试图在where条件中选择单个值。这就是为什么会出现误差。你可以按以下方法试试。

$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 In ( implode(',',$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 
");

Answer 2

你在查询中使用数组的where条件，这就是为什么你得到这个错误将代码更改为:

$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'];
}
$str_id_lokasi = implode(',',$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 
where negara.country_id In $str_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 
");