php - 如何在 Laravel 中将 @foreach 添加到 SQL 原始语句

Question

我有一个数组$shop_ids，所以我想遍历并将这些id 添加到SQL 语句的子查询中。 SQL子查询的数量应该等于id的数量。

我希望有这样的查询，但显然 @foreach 不起作用，只是成为 SQL 查询的一部分:

DB::select('select
        pr.product_name,
        pr.brand,
        coalesce(pr.weight, pr.volume) wv,
        sli.quantity
        {{@foreach ($shop_ids as $id)
                ,{{(select
        p.price

        from prices p
            inner join (
                select p1.product_id id, max(p1.created_at) maxed_date

                from prices p1

                group by p1.product_id, p1.shop_id) grouped on grouped.maxed_date=p.created_at

        join shop_names s on s.id=p.shop_id
        where p.product_id=sli.product_id
        and s.id=$id)}}}}

        from shopping_list_items sli
        join products pr on pr.id=sli.product_id

        where sli.shopping_list_id= :slid

        order by 5 asc', ['slid'=>$this->id])

Answer 1

你可以这样做作为例子 -

$ids_query = '';
// generate the queries in loop
foreach ($shop_ids as $id) {
    $ids_query .= ", (select ..... where .. and s.id=$id)";
}
// use in the main query
DB::select("select
        ...,
        sli.quantity
        $ids_query
        from shopping_list_items sli
        ...
        order by 5 asc", ['slid'=>$this->id]);