How to get the results with subqueries using laravel eloquent
I have been trying to get the results using subqueries. I need to perform two subqueries but so far I haven't been able to get the results.
The following query yields the desired result:
SELECT x.responsible_cook_id, x.d
FROM (
SELECT
responsible_cook_id,
count(*) d
FROM orders
GROUP BY responsible_cook_id
ORDER BY count(*) ASC
) as x
WHERE x.responsible_cook_id IN (
SELECT ID
FROM users
WHERE type = "cook" AND shift_active = 1
)
ORDER BY x.d;
So far I have tried using this approach to perform the same query via Eloquent:
$fSubquery = Order::select('responsible_cook_id, count(*) as d')->groupBy('responsible_cook_id')->orderByRaw('count(*) ASC');
$sSubquery = User::where('type', 'cook')->where('shift_active', 1);
$users = DB::table(DB::raw("({$fSubquery->toSql()}) as x"))
->mergeBindings($fSubquery->getQuery())
->whereRaw("x.responsible_cook_id IN {$sSubquery->toSql()}")
->mergeBindings($sSubquery->getQuery())
->select('x.responsible_cook_id, x.d')
->orderByRaw('ORDER BY x.d')->get();
There isn't any result returned by this last query. Is there any way to perform these subqueries and get the results?
laravel
asked Nov 12 at 1:26
The dude
3801515
add a comment |
I have been trying to get the results using subqueries. I need to perform two subqueries but so far I haven't been able to get the results.
The following query yields the desired result:
SELECT x.responsible_cook_id, x.d
FROM (
SELECT
responsible_cook_id,
count(*) d
FROM orders
GROUP BY responsible_cook_id
ORDER BY count(*) ASC
) as x
WHERE x.responsible_cook_id IN (
SELECT ID
FROM users
WHERE type = "cook" AND shift_active = 1
)
ORDER BY x.d;
So far I have tried using this approach to perform the same query via Eloquent:
$fSubquery = Order::select('responsible_cook_id, count(*) as d')->groupBy('responsible_cook_id')->orderByRaw('count(*) ASC');
$sSubquery = User::where('type', 'cook')->where('shift_active', 1);
$users = DB::table(DB::raw("({$fSubquery->toSql()}) as x"))
->mergeBindings($fSubquery->getQuery())
->whereRaw("x.responsible_cook_id IN {$sSubquery->toSql()}")
->mergeBindings($sSubquery->getQuery())
->select('x.responsible_cook_id, x.d')
->orderByRaw('ORDER BY x.d')->get();
There isn't any result returned by this last query. Is there any way to perform these subqueries and get the results?
laravel
asked Nov 12 at 1:26
The dude
3801515
add a comment |
I have been trying to get the results using subqueries. I need to perform two subqueries but so far I haven't been able to get the results.
The following query yields the desired result:
SELECT x.responsible_cook_id, x.d
FROM (
SELECT
responsible_cook_id,
count(*) d
FROM orders
GROUP BY responsible_cook_id
ORDER BY count(*) ASC
) as x
WHERE x.responsible_cook_id IN (
SELECT ID
FROM users
WHERE type = "cook" AND shift_active = 1
)
ORDER BY x.d;
So far I have tried using this approach to perform the same query via Eloquent:
$fSubquery = Order::select('responsible_cook_id, count(*) as d')->groupBy('responsible_cook_id')->orderByRaw('count(*) ASC');
$sSubquery = User::where('type', 'cook')->where('shift_active', 1);
$users = DB::table(DB::raw("({$fSubquery->toSql()}) as x"))
->mergeBindings($fSubquery->getQuery())
->whereRaw("x.responsible_cook_id IN {$sSubquery->toSql()}")
->mergeBindings($sSubquery->getQuery())
->select('x.responsible_cook_id, x.d')
->orderByRaw('ORDER BY x.d')->get();
There isn't any result returned by this last query. Is there any way to perform these subqueries and get the results?
laravel
asked Nov 12 at 1:26
The dude
3801515
I have been trying to get the results using subqueries. I need to perform two subqueries but so far I haven't been able to get the results.
The following query yields the desired result:
SELECT x.responsible_cook_id, x.d
FROM (
SELECT
responsible_cook_id,
count(*) d
FROM orders
GROUP BY responsible_cook_id
ORDER BY count(*) ASC
) as x
WHERE x.responsible_cook_id IN (
SELECT ID
FROM users
WHERE type = "cook" AND shift_active = 1
)
ORDER BY x.d;
So far I have tried using this approach to perform the same query via Eloquent:
$fSubquery = Order::select('responsible_cook_id, count(*) as d')->groupBy('responsible_cook_id')->orderByRaw('count(*) ASC');
$sSubquery = User::where('type', 'cook')->where('shift_active', 1);
$users = DB::table(DB::raw("({$fSubquery->toSql()}) as x"))
->mergeBindings($fSubquery->getQuery())
->whereRaw("x.responsible_cook_id IN {$sSubquery->toSql()}")
->mergeBindings($sSubquery->getQuery())
->select('x.responsible_cook_id, x.d')
->orderByRaw('ORDER BY x.d')->get();
There isn't any result returned by this last query. Is there any way to perform these subqueries and get the results?
laravel
laravel
asked Nov 12 at 1:26
The dude
3801515
asked Nov 12 at 1:26
The dude
3801515
asked Nov 12 at 1:26
The dude
3801515
asked Nov 12 at 1:26
The dude
3801515
asked Nov 12 at 1:26
The dude
3801515
3801515
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Well, I couldn't solve the question above but this stays as a piece of learning that sometimes when you let it go it just happens to work itself out and sometimes even help you.
Instead of doing 3 queries (2 subqueries + outter query) I was able to reduce all of it to only one query using Laravel's Eloquent
Here is what happened
// Get all cookers that have an active shift
$cookersWorking = User::where('type', 'cook')
->where('shift_active', 1)
->select('id')
->get();
After getting all the cookers that have an active shift I was able to get all the cookers that have the least amount of dishes to make:
// We will get the cooker that has the least orders to prepare
$cookerWithLessDishes = Order::select('responsible_cook_id', DB::raw('count(*) as d'))
->whereNull('end') // added this but is not in the original question
->whereIn('responsible_cook_id', $cookersWorking->pluck('id'))
->groupBy('responsible_cook_id')
->orderByRaw('count(*) ASC')
->first();
I think I was able to reduce this to two queries. If I'm wrong please correct me.
Happy coding
answered Nov 14 at 0:56
The dude
3801515
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%2f53254923%2fhow-to-get-the-results-with-subqueries-using-laravel-eloquent%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
Well, I couldn't solve the question above but this stays as a piece of learning that sometimes when you let it go it just happens to work itself out and sometimes even help you.
Instead of doing 3 queries (2 subqueries + outter query) I was able to reduce all of it to only one query using Laravel's Eloquent
Here is what happened
// Get all cookers that have an active shift
$cookersWorking = User::where('type', 'cook')
->where('shift_active', 1)
->select('id')
->get();
After getting all the cookers that have an active shift I was able to get all the cookers that have the least amount of dishes to make:
// We will get the cooker that has the least orders to prepare
$cookerWithLessDishes = Order::select('responsible_cook_id', DB::raw('count(*) as d'))
->whereNull('end') // added this but is not in the original question
->whereIn('responsible_cook_id', $cookersWorking->pluck('id'))
->groupBy('responsible_cook_id')
->orderByRaw('count(*) ASC')
->first();
I think I was able to reduce this to two queries. If I'm wrong please correct me.
Happy coding
answered Nov 14 at 0:56
The dude
3801515
add a comment |
Well, I couldn't solve the question above but this stays as a piece of learning that sometimes when you let it go it just happens to work itself out and sometimes even help you.
Instead of doing 3 queries (2 subqueries + outter query) I was able to reduce all of it to only one query using Laravel's Eloquent
Here is what happened
// Get all cookers that have an active shift
$cookersWorking = User::where('type', 'cook')
->where('shift_active', 1)
->select('id')
->get();
After getting all the cookers that have an active shift I was able to get all the cookers that have the least amount of dishes to make:
// We will get the cooker that has the least orders to prepare
$cookerWithLessDishes = Order::select('responsible_cook_id', DB::raw('count(*) as d'))
->whereNull('end') // added this but is not in the original question
->whereIn('responsible_cook_id', $cookersWorking->pluck('id'))
->groupBy('responsible_cook_id')
->orderByRaw('count(*) ASC')
->first();
I think I was able to reduce this to two queries. If I'm wrong please correct me.
Happy coding
answered Nov 14 at 0:56
The dude
3801515
add a comment |
Well, I couldn't solve the question above but this stays as a piece of learning that sometimes when you let it go it just happens to work itself out and sometimes even help you.
Instead of doing 3 queries (2 subqueries + outter query) I was able to reduce all of it to only one query using Laravel's Eloquent
Here is what happened
// Get all cookers that have an active shift
$cookersWorking = User::where('type', 'cook')
->where('shift_active', 1)
->select('id')
->get();
After getting all the cookers that have an active shift I was able to get all the cookers that have the least amount of dishes to make:
// We will get the cooker that has the least orders to prepare
$cookerWithLessDishes = Order::select('responsible_cook_id', DB::raw('count(*) as d'))
->whereNull('end') // added this but is not in the original question
->whereIn('responsible_cook_id', $cookersWorking->pluck('id'))
->groupBy('responsible_cook_id')
->orderByRaw('count(*) ASC')
->first();
I think I was able to reduce this to two queries. If I'm wrong please correct me.
Happy coding
answered Nov 14 at 0:56
The dude
3801515
Well, I couldn't solve the question above but this stays as a piece of learning that sometimes when you let it go it just happens to work itself out and sometimes even help you.
Instead of doing 3 queries (2 subqueries + outter query) I was able to reduce all of it to only one query using Laravel's Eloquent
Here is what happened
// Get all cookers that have an active shift
$cookersWorking = User::where('type', 'cook')
->where('shift_active', 1)
->select('id')
->get();
After getting all the cookers that have an active shift I was able to get all the cookers that have the least amount of dishes to make:
// We will get the cooker that has the least orders to prepare
$cookerWithLessDishes = Order::select('responsible_cook_id', DB::raw('count(*) as d'))
->whereNull('end') // added this but is not in the original question
->whereIn('responsible_cook_id', $cookersWorking->pluck('id'))
->groupBy('responsible_cook_id')
->orderByRaw('count(*) ASC')
->first();
I think I was able to reduce this to two queries. If I'm wrong please correct me.
Happy coding
answered Nov 14 at 0:56
The dude
3801515
answered Nov 14 at 0:56
The dude
3801515
answered Nov 14 at 0:56
The dude
3801515
answered Nov 14 at 0:56
The dude
3801515
3801515
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53254923%2fhow-to-get-the-results-with-subqueries-using-laravel-eloquent%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
