mysql - laravel - 查询构建器交叉连接子查询

Question

我做了一个 sql 查询，并希望使用构建器在 laravel 中构建它，

我在子查询上使用了交叉联接来获取两个日期之间的所有行数，如果匹配，则填充数据，否则用 null 填充。

SQL:

select 
    username,
    date_gen as date,
    skill_exp.*
from users cross join (
SELECT
    date_gen
from (
    select DATE_ADD(curdate() - INTERVAL 10 DAY, INTERVAL (@i:=@i+1)-1 DAY) as `date_gen`
    from information_schema.columns,(SELECT @i:=0) gen_sub 
    where DATE_ADD(curdate() - INTERVAL 10 DAY,INTERVAL @i DAY) BETWEEN curdate() - INTERVAL 10 DAY AND curdate()
    ) date_generator
) date_gen
left join skill_exp on users.id = skill_exp.user_id and date_gen.date_gen = skill_exp.date
where username = 'thebloodeyes'

我怎样才能在 Laravel 中制作子部分，以便我可以在 Skill_exp 的左连接上使用它

子查询部分:

SELECT
    date_gen
from (
    select DATE_ADD(curdate() - INTERVAL 10 DAY, INTERVAL (@i:=@i+1)-1 DAY) as `date_gen`
    from information_schema.columns,(SELECT @i:=0) gen_sub 
    where DATE_ADD(curdate() - INTERVAL 10 DAY,INTERVAL @i DAY) BETWEEN curdate() - INTERVAL 10 DAY AND curdate()
) date_generator;

结果子查询: 结果总查询:

Answer 1

我遗漏了数据库将处理丢失的日期并将其移至 php。如果有一些错误或者在数据库中处理这个错误会更可取，请告诉我

执行时间:0.035382032394409ms~
请求api时间:105ms~

开始日期:2013-06-01结束日期:2025-07-01数据库日期总计:1, 2018-10-21

        $user = User::with(['exp' => function ($query) {
            $query->whereDate('date', '>=', '2018-10-21');
        }])
            ->where('username', '=', $id)
            ->firstOrFail();


        return Helper::addMissingDates("2013-06-01", "2025-07-01", $user->exp);

addMissingDates 函数:

    public static function addMissingDates($dateStart, $dateEnd, $expTables) {
        $newExpTable = [];
        $begin = new DateTime( $dateStart );
        $end = new DateTime($dateEnd);
        $end = $end->modify( '+1 day' );

        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);

        foreach($daterange as $date) {
            foreach ($expTables as $expTable) {
                if($date->format("Y-m-d") === $expTable->date) {
                    $newExpTable[] = $expTable;
                    continue 2;
                }
            }
            $exp = new \stdClass();
            $exp->found = false;
            $exp->date = $date->format("Y-m-d");
            $newExpTable[] = $exp;
        }


        return $newExpTable;
    }