ちょっと複雑なSQLをEloquentに落とし込むためのメモ

はじめに

　最近CTFのscoreboardとかを制作している際に複雑なSQL文を書き集計やグラフ化、データ取得をしないと行けないことがあり、「SQLで書けば一発！」と高をくくっていたが最後...Eloquentにとっつかまりました...
　完成はしていないのでまだまだいっぱいこんな事案が出てくるとは思いますが何かしらの足しになればと思い投稿します。

WHERE (col1='a' OR col1='b') AND (col2='b' OR col2='a')

sample.sql

SELECT *
FROM   table_a
WHERE  (col1='a' OR col1='b') 
AND    (col2='b' OR col2='a');

demoService.php

$val_a = 'a';
$val_b = 'b';
$query = Sample::where( function ($query) use ($val_a, $val_b) {

             $query->orWhere('col1', =, $val_a)
                   ->orWhere('col1', =, $val_b);

         } )->where( function ($query) use ($val_a, $val_b) {

             $query->orWhere('col2', =, $val_a)
                   ->orWhere('col2', =, $val_b) ;

         } )

Where自体がANDと同じ役割を持っているのでWhereの内部でorWhereを利用することにより( or )AND( or )を実現している。

LEFT JOIN (副文)

sql.sql

SELECT    *
FROM      table_a
LEFT JOIN ( SELECT * FROM table_2 WHERE id < 9 ) AS JOIN_Table 
ON        JOIN_Table.col1 = table_a.col2;

demoService.php

$sub_query = SampleA::where('id', '<', 9);
$query     = SampleB::leftJoin(
                 \DB::raw(
                     "( { $sub_query->toSql() } ) AS JOIN_Table"
                 ),
                 JOIN_Table.col1, =, table_a.col2
             );
return $query->mergeBindings($sub_query->getQuery())->get();

SELECT in CASE

sql.sql

SELECT (
    CASE 
        WHEN table_a.name IS NOT NULL 
        THEN 1 
        ELSE 0 
    END
) AS isExist 
FROM table_a;

demoService.php

$query = Sample::selectRaw::('( 
             CASE 
                 WHEN userSolve.problem_id IS NOT NULL T
                 HEN 1 
                 ELSE 0 
             END 
         ) AS isSolve');