Level 2
That's because any column you use in a select statement must appear in the group by clause. So select * and group by don't get along.
Remove groupBy('cardid') in your relationship and see how that goes.
1 like
Sep 11, 2016
14
Level 1
SQLSTATE[42000]: Syntax error or access violation isn't in GROUP BY
Hello,
what does this error mean, how can i fix it?
QueryException in Connection.php line 761:
SQLSTATE[42000]: Syntax error or access violation: 1055 'd0170cbc.tcg_deck_cards.id' isn't in GROUP BY (SQL: select * from `tcg_deck_cards` where `tcg_deck_cards`.`deckid` in (862) group by `cardid` order by `amount` desc)
Controller
$deck = Deck::with('Cards.Card.Edition')
->where('id', '=', $deckid)
->first();
return $deck;
DeckModel
public function Cards() {
return $this->hasMany('App\Models\Pokemon_TCG\Deck\DeckCards', 'deckid')
->groupBy('cardid')
->orderBy('amount', 'desc');
}
Thank You
Level 1
That's working perfect but i need to group by just this field.
How would i do this
Level 2
Why would you need to group by cardid?
What's the final result you expect?
What do you want to do when you get all cards of a given deck?
If you really want to group by then you need to group by all columns of your cards table.
Level 1
Lets say id | cardid | value X | 1 | 2,99 X | 1 | 4,99
so i want that this cards avg value an not shown twice so i just need to group by the cardid an not everything
return $this->hasMany('App\Models\Pokemon_TCG\Card\CardValue', 'cardid')
->select('date', DB::raw('AVG(value) as value'))
->groupBy(DB::raw('WEEK(FROM_UNIXTIME(date))'))
->orderBy('date', 'asc');
this results in
SQLSTATE[42000]: Syntax error or access violation: 1055 'd0170cbc.card_value.date' isn't in GROUP BY (SQL: select `date`, AVG(value) as value from `card_value` where `card_value`.`cardid` in (7987) group by WEEK(FROM_UNIXTIME(date)) order by `date` asc)
SQLSTATE[42000]: Syntax error or access violation: 1055 'd0170cbc.card_value.date' isn't in GROUP BY
SQLSTATE[42000]: Syntax error or access violation: 1055 'd0170cbc.card_value.date' isn't in GROUP BY
The query which i'd like to transfor to laravel is the following:
SELECT date, AVG(value) as value FROM card_value WHERE cardid = 7987 GROUP BY WEEK(FROM_UNIXTIME(`date`)) ORDER BY date ASC
Level 1
Anybody got an idea?
Level 10
@Rocky you resolved your problem? I have the same issue
Level 4
Are you using MySQL 5.7? Because in 5.7 there were some changes.
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
You can also follow this guide: https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2
To begin you can try to do some changes in app/database.php
strict => false
17 likes
Level 1
@geowrgetudor How did you know that.. It has fixed my problem
Level 4
@frenadeveloper It is a hack, not recommended though :)
Level 1
great help thank you very much for your help
Level 1
Instead of app/database.php, look into config/database.php
2 likes
Level 1
Edit your applications's database config file config/database.php
In mysql array, set strict => false to disable MySQL's strict mode
2 likes
Level 1
Thanks it worked for my problem.
Level 1
@Abdul-Musawar not succes in my project, my project use laravel 8
Please or to participate in this conversation.