lundi 27 mars 2017

HAVING throws error in Laravel not in PHPMyAdmin

Below code (code 1, without the Laravel specific code) works flawless in the PHPMyAdmin "SQL-question section", but when I run it as RAW in my Laravel installation I get the following error:

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)

Code 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");

I have tried to add ip under the GROUP BY -row, but than get the error Unknown column 'ip' in 'group statement. How come it throw an error in Laravel syntax?



from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2mIBd7O
via IFTTT

Aucun commentaire:

Enregistrer un commentaire