Transform a query with CTE and COALESCE in Laraver DB query builder

Question

TL;TR

I have a collection with some string, I need to find them in a given columnn and get the resoult in one or more dynamic column, if not founded I need to know with a given string, so I need to perform a query like this:

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;

But in realty is:

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

This is my collection:

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

This is an example, usually it-IT is fixed, other languages can be different, just one, or more than one

And query will be dynamic "like" this:

    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;

I don't know if there's a way to build it with laravel query builder, is my first time using cte.

Hand job:

At the moment I just prepare a string to query the database in this way:

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

        }

    }

I know about security issue in this, and this is why I would like to avoid to biuld a string by mysel using:

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