Optimising Laravel query
Picked up Laravel recently for an API project and have got it all set up and working, and Ive been migrating an API written from a php function with a concatenated SQL string into Laravel query builder, all is fine apart from one part.
I have a table of stock, and a features table, so I have defined a hasMany relationship on the model of stock to the features, and when I pass in a string array of features to the query I need to loop over each feature and then get back all the stock items that have those features.
Using a whereIn is not what I need as that will give me stock that has only one of the features. If I use a standard where that will give me 0 results as soon as multiple features are added.
I have achieved what I want with the following code, but its terribly slow to execute but I am unsure of a better way to get the same result:
foreach($value as $v){
$builder->whereHas('features', function($query) use ($v) {
$query->where('code', $v);
);
}
php laravel
asked Nov 24 '18 at 21:56
pettmanpettman
14
add a comment |
Picked up Laravel recently for an API project and have got it all set up and working, and Ive been migrating an API written from a php function with a concatenated SQL string into Laravel query builder, all is fine apart from one part.
I have a table of stock, and a features table, so I have defined a hasMany relationship on the model of stock to the features, and when I pass in a string array of features to the query I need to loop over each feature and then get back all the stock items that have those features.
Using a whereIn is not what I need as that will give me stock that has only one of the features. If I use a standard where that will give me 0 results as soon as multiple features are added.
I have achieved what I want with the following code, but its terribly slow to execute but I am unsure of a better way to get the same result:
foreach($value as $v){
$builder->whereHas('features', function($query) use ($v) {
$query->where('code', $v);
);
}
php laravel
asked Nov 24 '18 at 21:56
pettmanpettman
14
I think in this case you need to define the reverse relationship between features and stock as well and then using eloquent you can fetch the related results.
– Dhaval Chheda
Nov 25 '18 at 4:53
add a comment |
Picked up Laravel recently for an API project and have got it all set up and working, and Ive been migrating an API written from a php function with a concatenated SQL string into Laravel query builder, all is fine apart from one part.
I have a table of stock, and a features table, so I have defined a hasMany relationship on the model of stock to the features, and when I pass in a string array of features to the query I need to loop over each feature and then get back all the stock items that have those features.
Using a whereIn is not what I need as that will give me stock that has only one of the features. If I use a standard where that will give me 0 results as soon as multiple features are added.
I have achieved what I want with the following code, but its terribly slow to execute but I am unsure of a better way to get the same result:
foreach($value as $v){
$builder->whereHas('features', function($query) use ($v) {
$query->where('code', $v);
);
}
php laravel
asked Nov 24 '18 at 21:56
pettmanpettman
14
Picked up Laravel recently for an API project and have got it all set up and working, and Ive been migrating an API written from a php function with a concatenated SQL string into Laravel query builder, all is fine apart from one part.
I have a table of stock, and a features table, so I have defined a hasMany relationship on the model of stock to the features, and when I pass in a string array of features to the query I need to loop over each feature and then get back all the stock items that have those features.
Using a whereIn is not what I need as that will give me stock that has only one of the features. If I use a standard where that will give me 0 results as soon as multiple features are added.
I have achieved what I want with the following code, but its terribly slow to execute but I am unsure of a better way to get the same result:
foreach($value as $v){
$builder->whereHas('features', function($query) use ($v) {
$query->where('code', $v);
);
}
php laravel
php laravel
asked Nov 24 '18 at 21:56
pettmanpettman
14
asked Nov 24 '18 at 21:56
pettmanpettman
14
asked Nov 24 '18 at 21:56
pettmanpettman
14
asked Nov 24 '18 at 21:56
pettmanpettman
14
asked Nov 24 '18 at 21:56
pettmanpettman
14
14
I think in this case you need to define the reverse relationship between features and stock as well and then using eloquent you can fetch the related results.
– Dhaval Chheda
Nov 25 '18 at 4:53
add a comment |
I think in this case you need to define the reverse relationship between features and stock as well and then using eloquent you can fetch the related results.
– Dhaval Chheda
Nov 25 '18 at 4:53
I think in this case you need to define the reverse relationship between features and stock as well and then using eloquent you can fetch the related results.
– Dhaval Chheda
Nov 25 '18 at 4:53
I think in this case you need to define the reverse relationship between features and stock as well and then using eloquent you can fetch the related results.
– Dhaval Chheda
Nov 25 '18 at 4:53
add a comment |
1 Answer
1
active
oldest
votes
Use query builder instead of Eloquent to optimize queries...
For example:
DB::table('stock')->join('features',function($join){
$join->on('features.id','=','stock.feature_id')
->where('code', $v);
})
->select('') <--- also select columns here..
->get();
Docs: https://laravel.com/docs/5.7/queries
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
Thanks for this, How can I apply the result of that in a look back into aBuilderobject?
– pettman
Nov 25 '18 at 0:00
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
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%2f53462699%2foptimising-laravel-query%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
Use query builder instead of Eloquent to optimize queries...
For example:
DB::table('stock')->join('features',function($join){
$join->on('features.id','=','stock.feature_id')
->where('code', $v);
})
->select('') <--- also select columns here..
->get();
Docs: https://laravel.com/docs/5.7/queries
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
Thanks for this, How can I apply the result of that in a look back into aBuilderobject?
– pettman
Nov 25 '18 at 0:00
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
add a comment |
Use query builder instead of Eloquent to optimize queries...
For example:
DB::table('stock')->join('features',function($join){
$join->on('features.id','=','stock.feature_id')
->where('code', $v);
})
->select('') <--- also select columns here..
->get();
Docs: https://laravel.com/docs/5.7/queries
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
Thanks for this, How can I apply the result of that in a look back into aBuilderobject?
– pettman
Nov 25 '18 at 0:00
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
add a comment |
Use query builder instead of Eloquent to optimize queries...
For example:
DB::table('stock')->join('features',function($join){
$join->on('features.id','=','stock.feature_id')
->where('code', $v);
})
->select('') <--- also select columns here..
->get();
Docs: https://laravel.com/docs/5.7/queries
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
Use query builder instead of Eloquent to optimize queries...
For example:
DB::table('stock')->join('features',function($join){
$join->on('features.id','=','stock.feature_id')
->where('code', $v);
})
->select('') <--- also select columns here..
->get();
Docs: https://laravel.com/docs/5.7/queries
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
answered Nov 24 '18 at 23:29
Walter CejasWalter Cejas
418310
418310
Thanks for this, How can I apply the result of that in a look back into aBuilderobject?
– pettman
Nov 25 '18 at 0:00
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
add a comment |
Thanks for this, How can I apply the result of that in a look back into aBuilderobject?
– pettman
Nov 25 '18 at 0:00
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
Thanks for this, How can I apply the result of that in a look back into a
Builder object?– pettman
Nov 25 '18 at 0:00
Thanks for this, How can I apply the result of that in a look back into a
Builder object?– pettman
Nov 25 '18 at 0:00
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
If it makes any difference I used this site (m.dotdev.co/…) to base my API on
– pettman
Nov 25 '18 at 12:10
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
You receive a collection or std object but it's not the same than Eloquent's collection .. that difference is the cause of the performance
– Walter Cejas
Nov 25 '18 at 16:13
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
Yea I thought that would be the case, what is going to be my best approach here? the way its setup is very Eloquent and neat, but this change seems to throw it off
– pettman
Nov 25 '18 at 20:42
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%2f53462699%2foptimising-laravel-query%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
I think in this case you need to define the reverse relationship between features and stock as well and then using eloquent you can fetch the related results.
– Dhaval Chheda
Nov 25 '18 at 4:53