Select where not in with select

Question

Im trying to do this SELECT IN SQL SERVER


SELECT cast(datediff(DAY, min([fbh].FBH_DATA_INICIAL), CASE
                                                           WHEN max([fbh].FBH_DATA_Final) = '9999-12-31' THEN cast(getdate() AS date)
                                                           ELSE max([fbh].FBH_DATA_Final)
                                                       END)AS FLOAT) / CAST(365 AS FLOAT) AS duration
FROM [funcionario] AS [f]
INNER JOIN [funcionario_banda_historico] AS [fbh] ON [f].[FUN_ID] = [fbh].[FUN_ID]
AND [f].[FUN_BANDA] = [fbh].[FUN_BANDA]
WHERE NOT EXISTS
    (SELECT 1
     FROM funcionario_banda_historico t1
     WHERE t1.fun_id = [fbh].fun_id
       AND t1.FBH_DATA_INICIAL > [fbh].FBH_DATA_Final
       AND t1.FUN_BANDA <> [fbh].FUN_BANDA )
  AND [f].[FUN_ID] = '9999999'
GROUP BY f.fun_id,
         [f].[FUN_BANDA]

Im trying to do this select with where not in and select in LARAVEL

protected function getYearsInBand($userId) {
    $fbh = DB::table('funcionario as f')
            ->join('funcionario_banda_historico as fbh', function($join) {
                $join->on('f.FUN_ID', '=', 'fbh.FUN_ID');
                $join->on('f.FUN_BANDA', '=', 'fbh.FUN_BANDA');
            })
            ->selectRaw('cast(datediff(day,min([fbh].FBH_DATA_INICIAL),case when max([fbh].FBH_DATA_Final) = "9999-12-31" then cast(getdate() as date) else max([fbh].FBH_DATA_Final) end)AS FLOAT)/CAST(365 AS FLOAT) AS duration')
            ->where('f.FUN_ID', $userId)
            ->whereNotIn('1', function($q) {
                $q->select('*')
                ->from('funcionario_banda_historico as t1')
                ->where('t1.fun_id = [fbh].fun_id')
                ->where('t1.FBH_DATA_INICIAL > [fbh].FBH_DATA_Final')
                ->where('t1.FUN_BANDA <> [fbh].FUN_BANDA')
                ->get();
            })
            ->groupBy('f.FUN_ID', 'f.FUN_BANDA')
            ->first();
    
    if (!$fbh) {
        \Log::debug('funcionario_banda_historico not found'); // Grava no log
        return 0;
    }

    return $fbh->duration;
}

and i had the error Invalid column name 'fun_id = [fbh]'

can you help me?

score 2 · Answer 1 · answered Jun 30 '20 at 17:06

I think it's not correct syntax for "where" clause in Laravel. Instead you should do:

->whereRaw('t1.FUN_ID = fbh.FUN_ID')
->whereRaw('t1.FBH_DATA_INICIAL > fbh.FBH_DATA_Final')
->whereRaw('t1.FUN_BANDA <> fbh.FUN_BANDA')

OR:

->where('t1.FUN_ID', 'fbh.FUN_ID')
->where('t1.FBH_DATA_INICIAL', '>', 'fbh.FBH_DATA_Final')
->where('t1.FUN_BANDA', '<>', 'fbh.FUN_BANDA')

Notice that I also change "fun_id" to uppercase like everywhere in your code. I don't know if "[fbh]" is correct in Laravel, try to type just "fbh". For raw example you may also need to add Database prefix this way - "DB::getTablePrefix()"

Select where not in with select

1 Answers1