您当前的位置： 首页 > 慢生活 > 程序人生 网站首页程序人生

webman-查詢構造器-groupBy與having的用法

发布时间：2022-02-05 13:01:28编辑：雪饮阅读（）

 實例：

        public function index(Request $request)
        {
            ini_set('memory_limit','4096M');

           //orderBy
            $data = Db::table('ecms_news')
                ->groupBy('userid')
                ->having('id', '>', 100)
                ->get();
            $gh=isset($data[0]) ? $data[0] : [];


            // 你可以向 groupBy 方法传递多个参数
            $data = Db::table('ecms_news')
                ->groupBy('userid', 'classid')
                ->having('id', '>', 100)
                ->get();
            $gh2=isset($data[0]) ? $data[0] : [];
            $all=compact("gh","gh2");
            var_dump($all);

            return response("ok");
        }
 構造的sql：
select * from `phome_ecms_news` group by `userid` having `id` > 100
select * from `phome_ecms_news` group by `userid`, `classid` having `id` > 100
注意：若出現如下錯誤：
 Next Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or
   access violation: 1055 Expression #1 of SELECT list is not in GROUP BY
   clause and contains nonaggregated column 'workerman.phome_ecms_news.id'
   which is not functionally dependent on columns in GROUP BY clause; this is
   incompatible with sql_mode=only_full_group_by (SQL: select * from
   `phome_ecms_news` group by `userid` having `id` > 100) in
   /www/wwwroot/webman/webman/vendor/illuminate/database/Connection.php:712
   Stack trace:
可能你用的是mysql5.7，可能你sql_mode中有包含only_full_group_by，則將only_full_group_by從sql_model項中去除（一般位於my.cnf文件中）然後重啓mysql服務
此時可能僅僅只是如Navicat Premium 12可以正常執行上面的groupBy/having的sql，而咱們的程序中執行仍舊報錯如上面的錯誤，則可以修改配置config/database.php中一般如connections\mysql\strict為false
然後重啓咱們的webman服務再試。
實例被訪問時：

[root@localhost webman]# /usr/local/php734/bin/php -c /usr/local/php734/lib/php.ini start.php start
Workerman[start.php] start in DEBUG mode
----------------------------------------- WORKERMAN -----------------------------------------
Workerman version:4.0.26          PHP version:7.3.4
------------------------------------------ WORKERS ------------------------------------------
proto   user            worker          listen                 processes    status
tcp     root            webman          http://0.0.0.0:8787    4             [OK]
tcp     root            monitor         none                   1             [OK]
---------------------------------------------------------------------------------------------
Press Ctrl+C to stop. Start success.
訪問了全局中間件！
array(2) {
  ["gh"]=>
  object(stdClass)#54 (37) {
    ["id"]=>
    int(114)
    ["classid"]=>
    int(83)
    ["ttid"]=>
    int(0)
    ["onclick"]=>
    int(62)
    ["plnum"]=>
    int(0)
    ["totaldown"]=>
    int(0)
    ["newspath"]=>
    string(0) ""
    ["filename"]=>
    string(3) "114"
    ["userid"]=>
    int(1)
    ["username"]=>
    string(6) "雪饮"
    ["firsttitle"]=>
    int(0)
    ["isgood"]=>
    int(0)
    ["ispic"]=>
    int(1)
    ["istop"]=>
    int(0)
    ["isqf"]=>
    int(0)
    ["ismember"]=>
    int(0)
    ["isurl"]=>
    int(0)
    ["truetime"]=>
    int(1423920308)
    ["lastdotime"]=>
    int(1425445123)
    ["havehtml"]=>
    int(1)
    ["groupid"]=>
    int(0)
    ["userfen"]=>
    int(0)
    ["titlefont"]=>
    string(0) ""
    ["titleurl"]=>
    string(30) "/manshenghuo/xinshang/114.html"
    ["stb"]=>
    int(1)
    ["fstb"]=>
    int(1)
    ["restb"]=>
    int(1)
    ["keyboard"]=>
    string(0) ""
    ["title"]=>
    string(30) "一个程序员的爱情故事"
    ["newstime"]=>
    int(1423920134)
    ["titlepic"]=>
    string(76) "/d/file/manshenghuo/xinshang/2015-02-14/01e4753ff30b6bf0838b6e55cbe88c39.jpg"
    ["ftitle"]=>
    string(35) "程序员,爱情,雪饮个人博客"
    ["smalltext"]=>
    string(170) " //一个程序员的爱情故事    public class MyLoveStory    {        public static void Main(string[] args)        {            //The ForeStory            int result"
    ["diggtop"]=>
    int(0)
    ["push_weixin_material"]=>
    int(0)
    ["push_baidu_last_time"]=>
    int(0)
    ["push_weixin_material_last_time"]=>
    int(0)
  }
  ["gh2"]=>
  object(stdClass)#56 (37) {
    ["id"]=>
    int(115)
    ["classid"]=>
    int(58)
    ["ttid"]=>
    int(0)
    ["onclick"]=>
    int(5)
    ["plnum"]=>
    int(0)
    ["totaldown"]=>
    int(0)
    ["newspath"]=>
    string(10) "2015-02-15"
    ["filename"]=>
    string(3) "115"
    ["userid"]=>
    int(1)
    ["username"]=>
    string(6) "雪饮"
    ["firsttitle"]=>
    int(0)
    ["isgood"]=>
    int(0)
    ["ispic"]=>
    int(0)
    ["istop"]=>
    int(0)
    ["isqf"]=>
    int(0)
    ["ismember"]=>
    int(0)
    ["isurl"]=>
    int(0)
    ["truetime"]=>
    int(1423976405)
    ["lastdotime"]=>
    int(1445327479)
    ["havehtml"]=>
    int(1)
    ["groupid"]=>
    int(0)
    ["userfen"]=>
    int(0)
    ["titlefont"]=>
    string(0) ""
    ["titleurl"]=>
    string(29) "/guanyuwo/2015-02-15/115.html"
    ["stb"]=>
    int(1)
    ["fstb"]=>
    int(1)
    ["restb"]=>
    int(1)
    ["keyboard"]=>
    string(0) ""
    ["title"]=>
    string(13) "Just about me"
    ["newstime"]=>
    int(1423976373)
    ["titlepic"]=>
    string(0) ""
    ["ftitle"]=>
    string(0) ""
    ["smalltext"]=>
    string(247) "    杨青，女，一个80后草根女站长！09年入行，从业已经有三四年。从搬砖一样的生活方式换成了现在有“单”而居的日子。当然这个单不是单身的单，跟我的职业相比，爱情脱单并"
    ["diggtop"]=>
    int(1)
    ["push_weixin_material"]=>
    int(0)
    ["push_baidu_last_time"]=>
    int(0)
    ["push_weixin_material_last_time"]=>
    int(0)
  }
}