包含COALESCE的sql语句怎么构造？

ericliu 更新于2023-06-18

问题描述

我想将如下Sql语句用查询构造器写，要怎么写？

SELECT n.id, n.Links, n.Image, n.created_at, COALESCE(l.Title, n.Title) AS Title, COALESCE(l.Description, n.Description) AS Description FROM news AS n LEFT JOIN news_lang AS l ON n.id = l.Nid AND l.Lang = '".addslashes($Lang)."' ORDER BY n.updated_at DESC LIMIT 4

主要是COALESCE这知道如何用，下面这样直接写会报错

Db::table('news')->select(['news.id', 'news.Title', 'COALESCE(news.Title, news_lang.Title) AS Title', 'news.Links', 'news.Image', 'news.Description', 'news.created_at',])->leftJoin('news_lang',function ($join) {$join->on('news.id','=','news_lang.Nid')->where('news_lang.Lang', '=', 'ko');})->where('Status', 1)->where('news_lang.Lang',$Lang)->orderBy('updated_at', 'DESC')->limit(4)->get()->all();

报错信息如下：PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'snsii_COALESCE(news.Title, news.Title)' in 'field list' in

原因是生成器自动加了表前缀：

select `snsii_news`.`id`, `snsii_news`.`Title`, `snsii_COALESCE(news`.`Title, news`.`Title)` as `Title`, `snsii_news`.`Links`, `snsii_news`.`Image`, `snsii_news`.`Description`, `snsii_news_lang`.`Description` as `DescriptionLang`, `snsii_news`.`created_at` from `snsii_news` left join `snsii_news_lang` on `snsii_news`.`id` = `snsii_news_lang`.`Nid` and `snsii_news_lang`.`Lang` = ko where `Status` = 1 and `snsii_news_lang`.`Lang` = ja order by `updated_at` desc limit 4)

 489  1 0

1个回答

稚出 2023-06-19

think-orm

Db::table('news')
    ->alias('n')
    ->leftJoin('news_lang l', "n.id = l.Nid AND l.Lang = '{$Lang}'")
    ->field('n.id, n.Links, n.Image, n.created_at, COALESCE(l.Title, n.Title) AS Title, COALESCE(l.Description, n.Description) AS Description')
    ->order('n.updated_at DESC')
    ->limit(4)
    ->select();

ericliu 2023-06-19

BadMethodCallException: Call to undefined method Illuminate\Database\Query\Builder::alias() in /www/wwwroot/abc/vendor/illuminate/support/Traits/ForwardsCalls.php:50
ericliu 2023-06-19

alias 和 field 都没有吧？
ericliu 2023-06-19

搞定了，问chatGPT，他告诉我用->selectRaw好了