Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

はじめに

 最近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');
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away