Translate SQL Search Query to Eloquent Query
1
I would like to have the following query.
select * from `items`
where (`item_name` LIKE 'foo' or `item_description` LIKE 'foo')
and `item_type` = 'type1'
Translated into Eloquent. I have come up with the following statement:
$items = Item::where('item_type', '=', 'type1')
->orWhere('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%')
->sortable(['id' => 'desc'])
->paginate(10);
In the above snippet, $q will be foo (it's a search query).
The problem is that this does not return exactly what I want since it also returns items that belong to another item_type instead of the type1 in my example.
How can I translate the above SQL query in an Eloquent query so that it only returns the items which contain foo and which are of type Type1 only?
php laravel eloquent laravel-query-builder
edited Jan 1 at 22:10
Karl Hill
2,97422242
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
add a comment |
1
I would like to have the following query.
select * from `items`
where (`item_name` LIKE 'foo' or `item_description` LIKE 'foo')
and `item_type` = 'type1'
Translated into Eloquent. I have come up with the following statement:
$items = Item::where('item_type', '=', 'type1')
->orWhere('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%')
->sortable(['id' => 'desc'])
->paginate(10);
In the above snippet, $q will be foo (it's a search query).
The problem is that this does not return exactly what I want since it also returns items that belong to another item_type instead of the type1 in my example.
How can I translate the above SQL query in an Eloquent query so that it only returns the items which contain foo and which are of type Type1 only?
php laravel eloquent laravel-query-builder
edited Jan 1 at 22:10
Karl Hill
2,97422242
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
add a comment |
1
1
1
I would like to have the following query.
select * from `items`
where (`item_name` LIKE 'foo' or `item_description` LIKE 'foo')
and `item_type` = 'type1'
Translated into Eloquent. I have come up with the following statement:
$items = Item::where('item_type', '=', 'type1')
->orWhere('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%')
->sortable(['id' => 'desc'])
->paginate(10);
In the above snippet, $q will be foo (it's a search query).
The problem is that this does not return exactly what I want since it also returns items that belong to another item_type instead of the type1 in my example.
How can I translate the above SQL query in an Eloquent query so that it only returns the items which contain foo and which are of type Type1 only?
php laravel eloquent laravel-query-builder
edited Jan 1 at 22:10
Karl Hill
2,97422242
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
I would like to have the following query.
select * from `items`
where (`item_name` LIKE 'foo' or `item_description` LIKE 'foo')
and `item_type` = 'type1'
Translated into Eloquent. I have come up with the following statement:
$items = Item::where('item_type', '=', 'type1')
->orWhere('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%')
->sortable(['id' => 'desc'])
->paginate(10);
In the above snippet, $q will be foo (it's a search query).
The problem is that this does not return exactly what I want since it also returns items that belong to another item_type instead of the type1 in my example.
How can I translate the above SQL query in an Eloquent query so that it only returns the items which contain foo and which are of type Type1 only?
php laravel eloquent laravel-query-builder
php laravel eloquent laravel-query-builder
edited Jan 1 at 22:10
Karl Hill
2,97422242
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
edited Jan 1 at 22:10
Karl Hill
2,97422242
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
edited Jan 1 at 22:10
Karl Hill
2,97422242
edited Jan 1 at 22:10
Karl Hill
2,97422242
edited Jan 1 at 22:10
Karl Hill
2,97422242
2,97422242
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
asked Jan 1 at 19:21
wiwa1978wiwa1978
441623
441623
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
3
Try this. You need a nested where closure:
$items = Item::where('item_type', '=', 'type1')
->where(function($query) use ($q) {
$query->where('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%');
})
->sortable(['id' => 'desc'])
->paginate(10);
edited Jan 1 at 19:49
HCK
3,56911137
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
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
});
}
});
draft saved
draft discarded
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%2f53998275%2ftranslate-sql-search-query-to-eloquent-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
3
Try this. You need a nested where closure:
$items = Item::where('item_type', '=', 'type1')
->where(function($query) use ($q) {
$query->where('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%');
})
->sortable(['id' => 'desc'])
->paginate(10);
edited Jan 1 at 19:49
HCK
3,56911137
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
add a comment |
3
Try this. You need a nested where closure:
$items = Item::where('item_type', '=', 'type1')
->where(function($query) use ($q) {
$query->where('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%');
})
->sortable(['id' => 'desc'])
->paginate(10);
edited Jan 1 at 19:49
HCK
3,56911137
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
add a comment |
3
3
3
Try this. You need a nested where closure:
$items = Item::where('item_type', '=', 'type1')
->where(function($query) use ($q) {
$query->where('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%');
})
->sortable(['id' => 'desc'])
->paginate(10);
edited Jan 1 at 19:49
HCK
3,56911137
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
Try this. You need a nested where closure:
$items = Item::where('item_type', '=', 'type1')
->where(function($query) use ($q) {
$query->where('item_name','LIKE','%'.$q.'%')
->orWhere('item_description','LIKE','%'.$q.'%');
})
->sortable(['id' => 'desc'])
->paginate(10);
edited Jan 1 at 19:49
HCK
3,56911137
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
edited Jan 1 at 19:49
HCK
3,56911137
edited Jan 1 at 19:49
HCK
3,56911137
edited Jan 1 at 19:49
HCK
3,56911137
3,56911137
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
answered Jan 1 at 19:26
Giorgi LagidzeGiorgi Lagidze
2506
2506
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
add a comment |
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
Thanks sir, much appreciated! This works indeed. Could you provide some explanation on why the nested where closure is required?
– wiwa1978
Jan 1 at 19:33
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
what you had before is wrong, because orWhere("item_name", "LIKE") would come after where("item_type") which means that if even one of these where's results exist, it fetches them. to further understand, this would be your query from your eloquent: "select * from items where "item_type"=type1 or where item_name LIKE $q. in my answered post, it would be where "item_type"=type1 AND (where "item_name" LIKE $q or where "item_description LIKE $q)
– Giorgi Lagidze
Jan 1 at 19:37
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53998275%2ftranslate-sql-search-query-to-eloquent-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
