laravel-使用CTE转换查询，并在Laraver DB查询生成器中合并

TL；TR

我有一个带有字符串的集合，我需要在给定的列中找到它们，并在一个或多个动态列中获取资源，如果没有找到，我需要使用给定的字符串知道，所以我需要执行如下查询：

SELECT COALESCE(p.`en-GB`, '#notFound#') `en-GB`,
       COALESCE(p.`de-DE`, '#notFound#') `de-DE`
FROM (
  SELECT 1 id, 'Eseguito' word UNION ALL
  SELECT 4, 'parola'
) t
LEFT JOIN prova p ON p.`it-IT` = t.word
ORDER BY t.id;

但在房地产领域：

 $refLang = 'it-IT'; <- Just one languages
 $langToTranslate = ['en-GB','de-DE']; <- one or more languages
 $notFoundString = "#notFound#;

这是我的收藏：

dd($group);

^ Illuminate\Support\Collection {#80140 ▼
  #items: array:2 [▼
    26 => array:8 [▼
      "Name" => "Text list entry_1"
      "Parent" => "TestFreni"
      "DefaultEntry" => null
      "Value" => "0"
      "it-IT" => "Eseguito"
      "en-GB" => ""
      "de-DE" => ""
      "FieldInfos" => null
    ]
    27 => array:8 [▶]
  ]
  #escapeWhenCastingToString: false

这是一个例子，通常是固定的，其他语言可以是不同的，只有一种，或者不止一种

而查询将是动态的；比如；这是：

    SELECT 
foreach($langToTranslate as $lang){

COALESCE(p.`$lang`, $notFoundString ) `$lang`,
}


    FROM (
foreach ($grop as $key => $row){
 SELECT $key id, $row[$refLang] word UNION ALL
}
      
    ) t
    LEFT JOIN prova p ON p.`$refLang` = t.word
    ORDER BY t.id;

我不知道是否有办法用laravel query builder构建它，这是我第一次使用cte。

手工作业：

目前，我只准备了一个字符串，以这种方式查询数据库：

   $SelectPart="";

    foreach($LangToTranslate as $lang){
        $SelectPart .= "COALESCE(p.`$lang`, 'not found') `$lang`,";
    }

    $SelectPart = substr($SelectPart, 0, -1); 

  
    foreach($groups as $tmp => $group){

        
        $from = "";
        $boTrick=false;

            
        foreach($group as $id => $key){

            if($boTrick == false){

                $from .= "SELECT $id id, '$key[$refLang]' word UNION ALL ";
                $boTrick = true;
            } else {
                $from .= "SELECT $id, '$key[$refLang]' UNION ALL ";
            }
            
        }
        $from = substr($from, 0, -10);

        $final = "SELECT $SelectPart FROM ( $from ) t LEFT JOIN dictionaries p ON p.`$refLang` = t.word ORDER BY t.id";

$result =  DB::select(DB::raw($final));

dd($reult);

        }

    }

我知道这里面有安全问题，这就是为什么我想避免mysel使用以下方法生成字符串：

$result = DB::select(DB::raw($final));