How to use an expression in select count(…)? (Doctrine)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have User and UserGroup entities. Each user has a group (UserGroup), and each user has a boss (User).
I would like to get a list of groups, and decide if the current user is the boss of someone in the group. I already implemented this in SQL, and now I tried to solve it using Doctrine. This is what I tried:
$qb = em()->createQueryBuilder()
->select(array('gr.id AS group_id', 'gr.name AS group_name', 'COUNT(us.boss = :current_user_id)>0 AS is_boss'))
->from('AppEntityUserGroup', 'ug')
->leftJoin('AppEntityUser', 'us', 'WITH', 'us.group = ug.id')
->setParameter('current_user_id', $_SESSION['uid'])
->groupby('gr.id')
;
$groups = $qb->getQuery()->getScalarResult();
Unfortunately I get an uncaught QueryException, and I have no idea how to fix that. How is it possible to put an expression inside the COUNT(...) function?
php sql doctrine-orm
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
add a comment |
I have User and UserGroup entities. Each user has a group (UserGroup), and each user has a boss (User).
I would like to get a list of groups, and decide if the current user is the boss of someone in the group. I already implemented this in SQL, and now I tried to solve it using Doctrine. This is what I tried:
$qb = em()->createQueryBuilder()
->select(array('gr.id AS group_id', 'gr.name AS group_name', 'COUNT(us.boss = :current_user_id)>0 AS is_boss'))
->from('AppEntityUserGroup', 'ug')
->leftJoin('AppEntityUser', 'us', 'WITH', 'us.group = ug.id')
->setParameter('current_user_id', $_SESSION['uid'])
->groupby('gr.id')
;
$groups = $qb->getQuery()->getScalarResult();
Unfortunately I get an uncaught QueryException, and I have no idea how to fix that. How is it possible to put an expression inside the COUNT(...) function?
php sql doctrine-orm
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
What is the error message? And you shouldgroupBywith all columns, not only id.
– Felippe Duarte
Nov 26 '18 at 19:37
It works without theCOUNT(...)part. The error message just contains the DQL query:SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM AppEntityUserGroup ug LEFT JOIN AppEntityUser us WITH us.group = ug.id GROUP BY gr.id
– Iter Ator
Nov 26 '18 at 19:45
add a comment |
I have User and UserGroup entities. Each user has a group (UserGroup), and each user has a boss (User).
I would like to get a list of groups, and decide if the current user is the boss of someone in the group. I already implemented this in SQL, and now I tried to solve it using Doctrine. This is what I tried:
$qb = em()->createQueryBuilder()
->select(array('gr.id AS group_id', 'gr.name AS group_name', 'COUNT(us.boss = :current_user_id)>0 AS is_boss'))
->from('AppEntityUserGroup', 'ug')
->leftJoin('AppEntityUser', 'us', 'WITH', 'us.group = ug.id')
->setParameter('current_user_id', $_SESSION['uid'])
->groupby('gr.id')
;
$groups = $qb->getQuery()->getScalarResult();
Unfortunately I get an uncaught QueryException, and I have no idea how to fix that. How is it possible to put an expression inside the COUNT(...) function?
php sql doctrine-orm
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
I have User and UserGroup entities. Each user has a group (UserGroup), and each user has a boss (User).
I would like to get a list of groups, and decide if the current user is the boss of someone in the group. I already implemented this in SQL, and now I tried to solve it using Doctrine. This is what I tried:
$qb = em()->createQueryBuilder()
->select(array('gr.id AS group_id', 'gr.name AS group_name', 'COUNT(us.boss = :current_user_id)>0 AS is_boss'))
->from('AppEntityUserGroup', 'ug')
->leftJoin('AppEntityUser', 'us', 'WITH', 'us.group = ug.id')
->setParameter('current_user_id', $_SESSION['uid'])
->groupby('gr.id')
;
$groups = $qb->getQuery()->getScalarResult();
Unfortunately I get an uncaught QueryException, and I have no idea how to fix that. How is it possible to put an expression inside the COUNT(...) function?
php sql doctrine-orm
php sql doctrine-orm
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
edited Nov 27 '18 at 13:22
Iter Ator
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
asked Nov 26 '18 at 19:35
Iter AtorIter Ator
2,14673580
2,14673580
What is the error message? And you shouldgroupBywith all columns, not only id.
– Felippe Duarte
Nov 26 '18 at 19:37
It works without theCOUNT(...)part. The error message just contains the DQL query:SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM AppEntityUserGroup ug LEFT JOIN AppEntityUser us WITH us.group = ug.id GROUP BY gr.id
– Iter Ator
Nov 26 '18 at 19:45
add a comment |
What is the error message? And you shouldgroupBywith all columns, not only id.
– Felippe Duarte
Nov 26 '18 at 19:37
It works without theCOUNT(...)part. The error message just contains the DQL query:SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM AppEntityUserGroup ug LEFT JOIN AppEntityUser us WITH us.group = ug.id GROUP BY gr.id
– Iter Ator
Nov 26 '18 at 19:45
What is the error message? And you should
groupBy with all columns, not only id.– Felippe Duarte
Nov 26 '18 at 19:37
What is the error message? And you should
groupBy with all columns, not only id.– Felippe Duarte
Nov 26 '18 at 19:37
It works without the
COUNT(...) part. The error message just contains the DQL query: SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM AppEntityUserGroup ug LEFT JOIN AppEntityUser us WITH us.group = ug.id GROUP BY gr.id– Iter Ator
Nov 26 '18 at 19:45
It works without the
COUNT(...) part. The error message just contains the DQL query: SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM AppEntityUserGroup ug LEFT JOIN AppEntityUser us WITH us.group = ug.id GROUP BY gr.id– Iter Ator
Nov 26 '18 at 19:45
add a comment |
1 Answer
1
active
oldest
votes
You can use Mysql case function to do this:
$queryBuilder->addSelect('case when COUNT(case when us.boss = :current_user_id then 1 else 0 end)>0 then 1 else 0 end AS is_boss');
Reference
- Is it possible to specify condition in Count()?
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
add a comment |
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%2f53487866%2fhow-to-use-an-expression-in-select-count-doctrine%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use Mysql case function to do this:
$queryBuilder->addSelect('case when COUNT(case when us.boss = :current_user_id then 1 else 0 end)>0 then 1 else 0 end AS is_boss');
Reference
- Is it possible to specify condition in Count()?
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
add a comment |
You can use Mysql case function to do this:
$queryBuilder->addSelect('case when COUNT(case when us.boss = :current_user_id then 1 else 0 end)>0 then 1 else 0 end AS is_boss');
Reference
- Is it possible to specify condition in Count()?
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
add a comment |
You can use Mysql case function to do this:
$queryBuilder->addSelect('case when COUNT(case when us.boss = :current_user_id then 1 else 0 end)>0 then 1 else 0 end AS is_boss');
Reference
- Is it possible to specify condition in Count()?
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
You can use Mysql case function to do this:
$queryBuilder->addSelect('case when COUNT(case when us.boss = :current_user_id then 1 else 0 end)>0 then 1 else 0 end AS is_boss');
Reference
- Is it possible to specify condition in Count()?
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
answered Nov 27 '18 at 22:32
Jannes BotisJannes Botis
8,30421227
8,30421227
add a comment |
add a comment |
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%2f53487866%2fhow-to-use-an-expression-in-select-count-doctrine%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
What is the error message? And you should
groupBywith all columns, not only id.– Felippe Duarte
Nov 26 '18 at 19:37
It works without the
COUNT(...)part. The error message just contains the DQL query:SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM AppEntityUserGroup ug LEFT JOIN AppEntityUser us WITH us.group = ug.id GROUP BY gr.id– Iter Ator
Nov 26 '18 at 19:45