Mixing
Symfony query with ONLY_FULL_GROUP_BY ON
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I want to retrive a collection of object as result of cities grouped by name, my query is
public function getDistinctCitiesName()
{
$qb = $this->createQueryBuilder("cc");
->add('groupBy', 'cc.name');
return $qb;
}
When run i have an exception
request.CRITICAL: Uncaught PHP Exception
DoctrineDBALExceptionDriverException: "An exception occurred while
executing 'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.postal_code
AS postal_code_2, c0_.country AS country_3, c0_.state AS state_4,
c0_.visible AS visible_5 FROM cities c0_ GROUP BY c0_.name':
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1
of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'www.c0_.id' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by"
I can't disable the only_full_group_by mode, so is there a solution ?
mysql symfony doctrine-orm group-by
edited Jan 3 at 20:04
jkucharovic
3,89812644
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
|
show 6 more comments
I want to retrive a collection of object as result of cities grouped by name, my query is
public function getDistinctCitiesName()
{
$qb = $this->createQueryBuilder("cc");
->add('groupBy', 'cc.name');
return $qb;
}
When run i have an exception
request.CRITICAL: Uncaught PHP Exception
DoctrineDBALExceptionDriverException: "An exception occurred while
executing 'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.postal_code
AS postal_code_2, c0_.country AS country_3, c0_.state AS state_4,
c0_.visible AS visible_5 FROM cities c0_ GROUP BY c0_.name':
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1
of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'www.c0_.id' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by"
I can't disable the only_full_group_by mode, so is there a solution ?
mysql symfony doctrine-orm group-by
edited Jan 3 at 20:04
jkucharovic
3,89812644
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
Your query doesn't contain any aggregated columns, it is not allowed into only_full_group_by mode. Consider rewriting your query to include aggregates
– Flying
Jan 3 at 14:24
thanks, but rewriting query does not return a collection on object
– Hansen mattias
Jan 3 at 14:47
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by thisgroupByclause?
– Flying
Jan 3 at 14:53
I need a collection to use it in form field 'query_builder'
– Hansen mattias
Jan 3 at 15:01
1
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to useselect distinctand fetch single column of values to avoid loading unnecessary data in application
– Flying
Jan 3 at 17:03
|
show 6 more comments
I want to retrive a collection of object as result of cities grouped by name, my query is
public function getDistinctCitiesName()
{
$qb = $this->createQueryBuilder("cc");
->add('groupBy', 'cc.name');
return $qb;
}
When run i have an exception
request.CRITICAL: Uncaught PHP Exception
DoctrineDBALExceptionDriverException: "An exception occurred while
executing 'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.postal_code
AS postal_code_2, c0_.country AS country_3, c0_.state AS state_4,
c0_.visible AS visible_5 FROM cities c0_ GROUP BY c0_.name':
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1
of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'www.c0_.id' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by"
I can't disable the only_full_group_by mode, so is there a solution ?
mysql symfony doctrine-orm group-by
edited Jan 3 at 20:04
jkucharovic
3,89812644
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
I want to retrive a collection of object as result of cities grouped by name, my query is
public function getDistinctCitiesName()
{
$qb = $this->createQueryBuilder("cc");
->add('groupBy', 'cc.name');
return $qb;
}
When run i have an exception
request.CRITICAL: Uncaught PHP Exception
DoctrineDBALExceptionDriverException: "An exception occurred while
executing 'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.postal_code
AS postal_code_2, c0_.country AS country_3, c0_.state AS state_4,
c0_.visible AS visible_5 FROM cities c0_ GROUP BY c0_.name':
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1
of SELECT list is not in GROUP BY clause and contains nonaggregated
column 'www.c0_.id' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by"
I can't disable the only_full_group_by mode, so is there a solution ?
mysql symfony doctrine-orm group-by
mysql symfony doctrine-orm group-by
edited Jan 3 at 20:04
jkucharovic
3,89812644
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
edited Jan 3 at 20:04
jkucharovic
3,89812644
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
edited Jan 3 at 20:04
jkucharovic
3,89812644
edited Jan 3 at 20:04
jkucharovic
3,89812644
edited Jan 3 at 20:04
jkucharovic
3,89812644
3,89812644
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
asked Jan 3 at 14:08
Hansen mattiasHansen mattias
34
34
Your query doesn't contain any aggregated columns, it is not allowed into only_full_group_by mode. Consider rewriting your query to include aggregates
– Flying
Jan 3 at 14:24
thanks, but rewriting query does not return a collection on object
– Hansen mattias
Jan 3 at 14:47
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by thisgroupByclause?
– Flying
Jan 3 at 14:53
I need a collection to use it in form field 'query_builder'
– Hansen mattias
Jan 3 at 15:01
1
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to useselect distinctand fetch single column of values to avoid loading unnecessary data in application
– Flying
Jan 3 at 17:03
|
show 6 more comments
Your query doesn't contain any aggregated columns, it is not allowed into only_full_group_by mode. Consider rewriting your query to include aggregates
– Flying
Jan 3 at 14:24
thanks, but rewriting query does not return a collection on object
– Hansen mattias
Jan 3 at 14:47
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by thisgroupByclause?
– Flying
Jan 3 at 14:53
I need a collection to use it in form field 'query_builder'
– Hansen mattias
Jan 3 at 15:01
1
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to useselect distinctand fetch single column of values to avoid loading unnecessary data in application
– Flying
Jan 3 at 17:03
Your query doesn't contain any aggregated columns, it is not allowed into only_full_group_by mode. Consider rewriting your query to include aggregates
– Flying
Jan 3 at 14:24
Your query doesn't contain any aggregated columns, it is not allowed into only_full_group_by mode. Consider rewriting your query to include aggregates
– Flying
Jan 3 at 14:24
thanks, but rewriting query does not return a collection on object
– Hansen mattias
Jan 3 at 14:47
thanks, but rewriting query does not return a collection on object
– Hansen mattias
Jan 3 at 14:47
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by this
groupBy clause?– Flying
Jan 3 at 14:53
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by this
groupBy clause?– Flying
Jan 3 at 14:53
I need a collection to use it in form field 'query_builder'
– Hansen mattias
Jan 3 at 15:01
I need a collection to use it in form field 'query_builder'
– Hansen mattias
Jan 3 at 15:01
1
1
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to use
select distinct and fetch single column of values to avoid loading unnecessary data in application– Flying
Jan 3 at 17:03
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to use
select distinct and fetch single column of values to avoid loading unnecessary data in application– Flying
Jan 3 at 17:03
|
show 6 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023944%2fsymfony-query-with-only-full-group-by-on%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023944%2fsymfony-query-with-only-full-group-by-on%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Your query doesn't contain any aggregated columns, it is not allowed into only_full_group_by mode. Consider rewriting your query to include aggregates
– Flying
Jan 3 at 14:24
thanks, but rewriting query does not return a collection on object
– Hansen mattias
Jan 3 at 14:47
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by this
groupByclause?– Flying
Jan 3 at 14:53
I need a collection to use it in form field 'query_builder'
– Hansen mattias
Jan 3 at 15:01
1
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to use
select distinctand fetch single column of values to avoid loading unnecessary data in application– Flying
Jan 3 at 17:03