Eloquent getting Syntax error or access violation error

Question

in laravel 5 when in try to implementing this sql command which i don't get any error on phpmyadmin :

SELECT shoppings.*, sum(shoppings.ordering_count) 
FROM `shoppings` join products on products.id = shoppings.product_id 
where `shoppings`.`user_ordering_ip` = '127.0.0.1'

with this query on laravel as:

$userShoppings = \DB::table('shoppings')
    ->join('products', 'shoppings.product_id', '=', 'products.id')
    ->select('*', \DB::raw('sum(shoppings.ordering_count)'))
    ->where('shoppings.user_ordering_ip', "'".request()->ip()."'")
    ->get();

i get this error:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of 
GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is 
illegal if there is no GROUP BY clause

Possible duplicate of [MySQL #1140 - Mixing of GROUP columns](https://stackoverflow.com/questions/1244169/mysql-1140-mixing-of-group-columns) — Maraboc, Dec 27 '17 at 11:16
@Maraboc : May be he wants sum of all rows, using `groupBy` would definitely fix this problem but result sum will not be affected. — freelancer, Dec 27 '17 at 11:31

score 1 · Accepted Answer · edited Dec 27 '17 at 18:02

1

Try with following query, without ' quotes here request()->ip, I doubt you are not calling ip() method here, and also dont use sum method here

$userShoppings = \DB::table('shoppings as s')
    ->join('products as p', 's.product_id', '=', 'p.id')
    ->select('p.*','s.*')
    ->where('s.user_ordering_ip', request()->ip())
    ->get();

$sum_of_ordering_count = $userShoppings->sum('ordering_count');

edited Dec 27 '17 at 18:02

DolDurma

15,753
51
198
377

answered Dec 27 '17 at 11:19

freelancer

1,174
5
12

what error ? share error ? and I updated `->ip()` to `->ip` – freelancer Dec 27 '17 at 11:21
this error: `Syntax error or access violation: 1140 Mixing of GROUP` – DolDurma Dec 27 '17 at 11:22
@Mahdi.Pishguy : use $sum_of_ordering_count = $userShoppings->sum('ordering_count'); – freelancer Dec 27 '17 at 11:32

score 0 · Answer 2 · answered Dec 27 '17 at 12:09

I think your query is correct.

You can try with below Query Code.

$userShoppings = \DB::table('shoppings')
    ->join('products', 'shoppings.product_id', '=', 'products.id')
    ->select('shoppings.*', \DB::raw('sum(shoppings.ordering_count)'))
    ->where('shoppings.user_ordering_ip', '=', '127.0.0.1')
    ->get();

Also it seems there is may be error on request()->ip

Eloquent getting Syntax error or access violation error

2 Answers2