mysql - HAVING 在 Laravel 中抛出错误，而不是在 PHPMyAdmin 中

Question

下面的代码(代码 1，没有 Laravel 特定代码)在 PHPMyAdmin“SQL 问题部分”中完美运行，但是当我在 Laravel 安装中以 RAW 格式运行它时，我收到以下错误:

SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'ip' is used in HAVING clause (SQL: SELECT
d.id,
d.domain,
d.date_expiration,
d.date_added,
MAX(CASE WHEN (dm.meta_key = 'domain') THEN dm.meta_value ELSE NULL END) AS domain,
MAX(CASE WHEN (dm.meta_key = 'ip') THEN dm.meta_value ELSE NULL END) AS ip,
MAX(CASE WHEN (dm.meta_key = 'link') THEN dm.meta_value ELSE NULL END) AS link,
MAX(CASE WHEN (dm.meta_key = 'net') THEN dm.meta_value ELSE NULL END) AS net
FROM
domain d
INNER JOIN domain_meta dm
ON d.id = dm.domain_id
WHERE 
d.date_expiration >= '2017-03-21'
GROUP BY d.id, d.domain, d.date_expiration, d.date_added
HAVING ip > 4
ORDER BY d.date_expiration ASC
LIMIT 1000)

代码 1

$domains = DB::select("SELECT
    d.id,
    d.domain,
    d.date_expiration,
    d.date_added,
    MAX(CASE WHEN (dm.meta_key = 'domain') THEN dm.meta_value ELSE NULL END) AS domain,
    MAX(CASE WHEN (dm.meta_key = 'ip') THEN dm.meta_value ELSE NULL END) AS ip,
    MAX(CASE WHEN (dm.meta_key = 'link') THEN dm.meta_value ELSE NULL END) AS link,
    MAX(CASE WHEN (dm.meta_key = 'net') THEN dm.meta_value ELSE NULL END) AS net
FROM
    domain d
INNER JOIN domain_meta dm
ON d.id = dm.domain_id
WHERE 
    d.date_expiration >= '2017-03-21'
GROUP BY d.id, d.domain, d.date_expiration, d.date_added
HAVING ip > 4
ORDER BY d.date_expiration ASC
LIMIT 1000");

我尝试在 GROUP BY 行下添加 ip，但出现错误 Unknown column 'ip' in 'group statement。为什么会在 Laravel 语法中抛出错误？

Answer 1

尽量不要在 having 子句中使用别名

  $domains = DB::select("SELECT
      d.id,
      d.domain,
      d.date_expiration,
      d.date_added,
      MAX(CASE WHEN (dm.meta_key = 'domain') THEN dm.meta_value ELSE NULL END) AS domain,
      MAX(CASE WHEN (dm.meta_key = 'ip') THEN dm.meta_value ELSE NULL END) AS ip,
      MAX(CASE WHEN (dm.meta_key = 'link') THEN dm.meta_value ELSE NULL END) AS link,
      MAX(CASE WHEN (dm.meta_key = 'net') THEN dm.meta_value ELSE NULL END) AS net
  FROM
      domain d
  INNER JOIN domain_meta dm
  ON d.id = dm.domain_id
  WHERE 
      d.date_expiration >= '2017-03-21'
  GROUP BY d.id, d.domain, d.date_expiration, d.date_added
  HAVING MAX(CASE WHEN (dm.meta_key = 'ip') THEN dm.meta_value ELSE NULL END) > 4
  ORDER BY d.date_expiration ASC
  LIMIT 1000");