时间戳

首页 > 解决方案 > 我需要帮助将此查询转换为 Laravel 查询生成器

laravel - 我需要帮助将此查询转换为 Laravel 查询生成器

问题描述

如果你愿意，我需要一些帮助来将此查询转换为 Laravel 的查询构建器：

SELECT
      DATE(created_at) AS Date,
      SUM(CASE WHEN `Type` = 'Votes' THEN 1 ELSE 0 END) AS 'Total Votes',
      SUM(CASE WHEN `Type` = 'Users' THEN 1 ELSE 0 END) AS 'Total Users'
    FROM
    (
        SELECT created_at, 'Votes' `Type` FROM votes
        UNION ALL
        SELECT created_at, 'Users'        FROM users
    ) t
    GROUP by DATE(created_at)
    ORDER by DATE(created_at) DESC

有人能帮助我吗？谢谢！！

==================================================== ===============

更新： 实际上我的问题出在这段代码上：

public function getVoteResult($topic_id){
        if($topic_id==null || $topic_id == ""){
            return null;
        }
        $topic = VoteTopicModel::select('*')->where('topic_id', $topic_id)->first();
        $choice = VoteChoiceModel::select('choice_id', 'name')->where('vote_topic_id', $topic_id)->get();
        
        foreach($choice as $choices){
                $choices['count_result'] = VoteChoiceSubmitModel::select('*')->where('vote_choice_id', $choices->choice_id)->count();
        }
        $data = array('topic_id' => $topic_id, 'topic_name' => $topic->name, 'topic_title' => $topic->title, 'choice' => $choice);
        return $data;
    }

它工作得很好，但是，正如你所看到的，我调用了三个表，VoteTopicModel这并不是真正有效的。所以我的朋友说尝试使用那个查询，但我不太明白。VoteChoiceModelVoteChoiceSubmitModel

提前致谢！！为了帮助我...

标签： laravelquery-builderlaravel-query-builder

解决方案

查询看起来非常大，可以快速转换为查询生成器，但您可以通过简单的方式执行此操作。

  DB::select( DB::raw("SELECT
  DATE(created_at) AS Date,
  SUM(CASE WHEN `Type` = 'Votes' THEN 1 ELSE 0 END) AS 'Total Votes',
  SUM(CASE WHEN `Type` = 'Users' THEN 1 ELSE 0 END) AS 'Total Users'
  FROM
    (
     SELECT created_at, 'Votes' `Type` FROM votes
     UNION ALL
     SELECT created_at, 'Users'        FROM users
    ) t
  GROUP by DATE(created_at)
  ORDER by DATE(created_at) DESC") )

推荐阅读