Doctrine Query with subquery
I'm wondering if this is the right way to have a subquery in Doctrine. I have two entities, one called User and one called Log. There's a mapping from Log to User but not the other way around (ManyToOne).
I need a list of all users, which had log entries within a certain period. This is my current solution:
// UserRepository.php
/**
* @return User
*/
public function findLogUsersByCategory(int $category, DateTime $start, DateTime $end): array
{
return $this->createQueryBuilder('e')
->where('e.id IN (:users)')
->setParameter('users', $this->_em->createQueryBuilder()
->select('u.id')
->from(Log::class, 'l')
->join('l.user', 'u')
->where('l.category = :category')
->setParameter('category', $category)
->andWhere('l.created BETWEEN :start AND :end')
->setParameter('start', $start)
->setParameter('end', $end)
->orderBy('l.category', 'ASC')
->addOrderBy('l.id', 'ASC')
->groupBy('u.id')
->getQuery()->getResult()
)
->getQuery()
->getResult();
}
The query works like this, however I don't like, that it will execute 2 queries (first the inner, write result to memory in PHP and use it as parameter for the outer query). I've tried to use ->getDQL() instead of the ->getQuery()->getResult() for the inner query, but then I don't get any results (I'm guessing the parameters for the inner query aren't processed when calling getDQL()).
So the question would be, how to avoid having PHP temporary store the result of the inner query and instead let the DB server work it's magic?
php sql mysql doctrine symfony3
asked 2 days ago
wawawawa
1206
add a comment |
I'm wondering if this is the right way to have a subquery in Doctrine. I have two entities, one called User and one called Log. There's a mapping from Log to User but not the other way around (ManyToOne).
I need a list of all users, which had log entries within a certain period. This is my current solution:
// UserRepository.php
/**
* @return User
*/
public function findLogUsersByCategory(int $category, DateTime $start, DateTime $end): array
{
return $this->createQueryBuilder('e')
->where('e.id IN (:users)')
->setParameter('users', $this->_em->createQueryBuilder()
->select('u.id')
->from(Log::class, 'l')
->join('l.user', 'u')
->where('l.category = :category')
->setParameter('category', $category)
->andWhere('l.created BETWEEN :start AND :end')
->setParameter('start', $start)
->setParameter('end', $end)
->orderBy('l.category', 'ASC')
->addOrderBy('l.id', 'ASC')
->groupBy('u.id')
->getQuery()->getResult()
)
->getQuery()
->getResult();
}
The query works like this, however I don't like, that it will execute 2 queries (first the inner, write result to memory in PHP and use it as parameter for the outer query). I've tried to use ->getDQL() instead of the ->getQuery()->getResult() for the inner query, but then I don't get any results (I'm guessing the parameters for the inner query aren't processed when calling getDQL()).
So the question would be, how to avoid having PHP temporary store the result of the inner query and instead let the DB server work it's magic?
php sql mysql doctrine symfony3
asked 2 days ago
wawawawa
1206
add a comment |
I'm wondering if this is the right way to have a subquery in Doctrine. I have two entities, one called User and one called Log. There's a mapping from Log to User but not the other way around (ManyToOne).
I need a list of all users, which had log entries within a certain period. This is my current solution:
// UserRepository.php
/**
* @return User
*/
public function findLogUsersByCategory(int $category, DateTime $start, DateTime $end): array
{
return $this->createQueryBuilder('e')
->where('e.id IN (:users)')
->setParameter('users', $this->_em->createQueryBuilder()
->select('u.id')
->from(Log::class, 'l')
->join('l.user', 'u')
->where('l.category = :category')
->setParameter('category', $category)
->andWhere('l.created BETWEEN :start AND :end')
->setParameter('start', $start)
->setParameter('end', $end)
->orderBy('l.category', 'ASC')
->addOrderBy('l.id', 'ASC')
->groupBy('u.id')
->getQuery()->getResult()
)
->getQuery()
->getResult();
}
The query works like this, however I don't like, that it will execute 2 queries (first the inner, write result to memory in PHP and use it as parameter for the outer query). I've tried to use ->getDQL() instead of the ->getQuery()->getResult() for the inner query, but then I don't get any results (I'm guessing the parameters for the inner query aren't processed when calling getDQL()).
So the question would be, how to avoid having PHP temporary store the result of the inner query and instead let the DB server work it's magic?
php sql mysql doctrine symfony3
asked 2 days ago
wawawawa
1206
I'm wondering if this is the right way to have a subquery in Doctrine. I have two entities, one called User and one called Log. There's a mapping from Log to User but not the other way around (ManyToOne).
I need a list of all users, which had log entries within a certain period. This is my current solution:
// UserRepository.php
/**
* @return User
*/
public function findLogUsersByCategory(int $category, DateTime $start, DateTime $end): array
{
return $this->createQueryBuilder('e')
->where('e.id IN (:users)')
->setParameter('users', $this->_em->createQueryBuilder()
->select('u.id')
->from(Log::class, 'l')
->join('l.user', 'u')
->where('l.category = :category')
->setParameter('category', $category)
->andWhere('l.created BETWEEN :start AND :end')
->setParameter('start', $start)
->setParameter('end', $end)
->orderBy('l.category', 'ASC')
->addOrderBy('l.id', 'ASC')
->groupBy('u.id')
->getQuery()->getResult()
)
->getQuery()
->getResult();
}
The query works like this, however I don't like, that it will execute 2 queries (first the inner, write result to memory in PHP and use it as parameter for the outer query). I've tried to use ->getDQL() instead of the ->getQuery()->getResult() for the inner query, but then I don't get any results (I'm guessing the parameters for the inner query aren't processed when calling getDQL()).
So the question would be, how to avoid having PHP temporary store the result of the inner query and instead let the DB server work it's magic?
php sql mysql doctrine symfony3
php sql mysql doctrine symfony3
asked 2 days ago
wawawawa
1206
asked 2 days ago
wawawawa
1206
asked 2 days ago
wawawawa
1206
asked 2 days ago
wawawawa
1206
asked 2 days ago
wawawawa
1206
1206
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fcodereview.stackexchange.com%2fquestions%2f211136%2fdoctrine-query-with-subquery%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 Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f211136%2fdoctrine-query-with-subquery%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
