get the duplicate data using whereIn form sql database
i have array of ids [1,1,2,1,1,1]
when i using where In don't repeat the data i get data of [1,2]
only ,how to make it get [1,1,2,1,1,1]
in the same order?
$selected_tasknotes_persons_made_comment = tasksNote::where("noted_task", request()->task_id )->get()->pluck('user_id');
// $selected_tasknotes_persons_made_comment = [1,1,2,1,1,1]
$persons_gave_notes = User::whereIn('id', $selected_tasknotes_persons_made_comment )->get();
php mysql laravel
add a comment |
i have array of ids [1,1,2,1,1,1]
when i using where In don't repeat the data i get data of [1,2]
only ,how to make it get [1,1,2,1,1,1]
in the same order?
$selected_tasknotes_persons_made_comment = tasksNote::where("noted_task", request()->task_id )->get()->pluck('user_id');
// $selected_tasknotes_persons_made_comment = [1,1,2,1,1,1]
$persons_gave_notes = User::whereIn('id', $selected_tasknotes_persons_made_comment )->get();
php mysql laravel
add a comment |
i have array of ids [1,1,2,1,1,1]
when i using where In don't repeat the data i get data of [1,2]
only ,how to make it get [1,1,2,1,1,1]
in the same order?
$selected_tasknotes_persons_made_comment = tasksNote::where("noted_task", request()->task_id )->get()->pluck('user_id');
// $selected_tasknotes_persons_made_comment = [1,1,2,1,1,1]
$persons_gave_notes = User::whereIn('id', $selected_tasknotes_persons_made_comment )->get();
php mysql laravel
i have array of ids [1,1,2,1,1,1]
when i using where In don't repeat the data i get data of [1,2]
only ,how to make it get [1,1,2,1,1,1]
in the same order?
$selected_tasknotes_persons_made_comment = tasksNote::where("noted_task", request()->task_id )->get()->pluck('user_id');
// $selected_tasknotes_persons_made_comment = [1,1,2,1,1,1]
$persons_gave_notes = User::whereIn('id', $selected_tasknotes_persons_made_comment )->get();
php mysql laravel
php mysql laravel
edited Nov 14 '18 at 4:58
Bilal Ahmed
3,25231535
3,25231535
asked Nov 14 '18 at 4:18
Tifa AhmedTifa Ahmed
265
265
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
In SQL your query might look like this:
select id
from sometable
where id IN(1,1,2,1,1,1)
What the IN(1,1,2,1,1,1)
means in SQL is this:
select id
from sometable
where (id = 1 or id = 1 or id = 2 or id = 1 or id = 1 or id = 1)
If there is only one row in the table with an id = 1, you will only get that one row returned. It does NOT get multiplied by the number of times you filter for that row.
Note also that typically (by convention) the column id
is UNIQUE in a table, so there probably only is one row where id = 1 and one row where id = 2.
Additionally, in SQL tables are "unordered sets", there is simply no guarantee at all that you will get a resultset in any particular or predictable order UNTIL you apply an order by
clause.
In short, you are expecting something that SQL simply does not do.
You could insert your array values into a table (or a "derived table") with one value per row in that table (i.e. 6 rows in your example) THEN join that to the source data and THEN you would get more rows. e.g.
select tablex.id
from tablex
inner join (
select 1 as n, 0 as sortby union all
select 1 as n, 1 as sortby union all
select 2 as n, 2 as sortby union all
select 1 as n, 3 as sortby union all
select 1 as n, 4 as sortby union all
select 1 as n, 5 as sortby
) myarray on tablex.id = myarray.n
order by
myarray.sortby
add a comment |
As per your query User::whereIn('id', $selected_tasknotes_persons_made_comment )->get()
that's mean it's return uniqueness results, if you want to get duplicate records then follow below instruction:
you can execute raw query in laravel to get repeated values in wherein using UNION ALL
$sql_query= 'SELECT u.*
FROM User u
JOIN ( SELECT 1 AS id UNION ALL SELECT 1 AS id
UNION ALL SELECT 2 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
) i ON i.id= u.id';
So, you can execute raw query like this
$results = DB::select($sql_query);
Another way (using eloquent method): I'm little bit confuse about below query but you can try this way using havingRaw
User::whereIn('id', $selected_tasknotes_persons_made_comment )
->havingRaw('count(*) > 1'); })->get()
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%2f53293138%2fget-the-duplicate-data-using-wherein-form-sql-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
In SQL your query might look like this:
select id
from sometable
where id IN(1,1,2,1,1,1)
What the IN(1,1,2,1,1,1)
means in SQL is this:
select id
from sometable
where (id = 1 or id = 1 or id = 2 or id = 1 or id = 1 or id = 1)
If there is only one row in the table with an id = 1, you will only get that one row returned. It does NOT get multiplied by the number of times you filter for that row.
Note also that typically (by convention) the column id
is UNIQUE in a table, so there probably only is one row where id = 1 and one row where id = 2.
Additionally, in SQL tables are "unordered sets", there is simply no guarantee at all that you will get a resultset in any particular or predictable order UNTIL you apply an order by
clause.
In short, you are expecting something that SQL simply does not do.
You could insert your array values into a table (or a "derived table") with one value per row in that table (i.e. 6 rows in your example) THEN join that to the source data and THEN you would get more rows. e.g.
select tablex.id
from tablex
inner join (
select 1 as n, 0 as sortby union all
select 1 as n, 1 as sortby union all
select 2 as n, 2 as sortby union all
select 1 as n, 3 as sortby union all
select 1 as n, 4 as sortby union all
select 1 as n, 5 as sortby
) myarray on tablex.id = myarray.n
order by
myarray.sortby
add a comment |
In SQL your query might look like this:
select id
from sometable
where id IN(1,1,2,1,1,1)
What the IN(1,1,2,1,1,1)
means in SQL is this:
select id
from sometable
where (id = 1 or id = 1 or id = 2 or id = 1 or id = 1 or id = 1)
If there is only one row in the table with an id = 1, you will only get that one row returned. It does NOT get multiplied by the number of times you filter for that row.
Note also that typically (by convention) the column id
is UNIQUE in a table, so there probably only is one row where id = 1 and one row where id = 2.
Additionally, in SQL tables are "unordered sets", there is simply no guarantee at all that you will get a resultset in any particular or predictable order UNTIL you apply an order by
clause.
In short, you are expecting something that SQL simply does not do.
You could insert your array values into a table (or a "derived table") with one value per row in that table (i.e. 6 rows in your example) THEN join that to the source data and THEN you would get more rows. e.g.
select tablex.id
from tablex
inner join (
select 1 as n, 0 as sortby union all
select 1 as n, 1 as sortby union all
select 2 as n, 2 as sortby union all
select 1 as n, 3 as sortby union all
select 1 as n, 4 as sortby union all
select 1 as n, 5 as sortby
) myarray on tablex.id = myarray.n
order by
myarray.sortby
add a comment |
In SQL your query might look like this:
select id
from sometable
where id IN(1,1,2,1,1,1)
What the IN(1,1,2,1,1,1)
means in SQL is this:
select id
from sometable
where (id = 1 or id = 1 or id = 2 or id = 1 or id = 1 or id = 1)
If there is only one row in the table with an id = 1, you will only get that one row returned. It does NOT get multiplied by the number of times you filter for that row.
Note also that typically (by convention) the column id
is UNIQUE in a table, so there probably only is one row where id = 1 and one row where id = 2.
Additionally, in SQL tables are "unordered sets", there is simply no guarantee at all that you will get a resultset in any particular or predictable order UNTIL you apply an order by
clause.
In short, you are expecting something that SQL simply does not do.
You could insert your array values into a table (or a "derived table") with one value per row in that table (i.e. 6 rows in your example) THEN join that to the source data and THEN you would get more rows. e.g.
select tablex.id
from tablex
inner join (
select 1 as n, 0 as sortby union all
select 1 as n, 1 as sortby union all
select 2 as n, 2 as sortby union all
select 1 as n, 3 as sortby union all
select 1 as n, 4 as sortby union all
select 1 as n, 5 as sortby
) myarray on tablex.id = myarray.n
order by
myarray.sortby
In SQL your query might look like this:
select id
from sometable
where id IN(1,1,2,1,1,1)
What the IN(1,1,2,1,1,1)
means in SQL is this:
select id
from sometable
where (id = 1 or id = 1 or id = 2 or id = 1 or id = 1 or id = 1)
If there is only one row in the table with an id = 1, you will only get that one row returned. It does NOT get multiplied by the number of times you filter for that row.
Note also that typically (by convention) the column id
is UNIQUE in a table, so there probably only is one row where id = 1 and one row where id = 2.
Additionally, in SQL tables are "unordered sets", there is simply no guarantee at all that you will get a resultset in any particular or predictable order UNTIL you apply an order by
clause.
In short, you are expecting something that SQL simply does not do.
You could insert your array values into a table (or a "derived table") with one value per row in that table (i.e. 6 rows in your example) THEN join that to the source data and THEN you would get more rows. e.g.
select tablex.id
from tablex
inner join (
select 1 as n, 0 as sortby union all
select 1 as n, 1 as sortby union all
select 2 as n, 2 as sortby union all
select 1 as n, 3 as sortby union all
select 1 as n, 4 as sortby union all
select 1 as n, 5 as sortby
) myarray on tablex.id = myarray.n
order by
myarray.sortby
edited Nov 14 '18 at 4:39
answered Nov 14 '18 at 4:31
Used_By_AlreadyUsed_By_Already
23k21938
23k21938
add a comment |
add a comment |
As per your query User::whereIn('id', $selected_tasknotes_persons_made_comment )->get()
that's mean it's return uniqueness results, if you want to get duplicate records then follow below instruction:
you can execute raw query in laravel to get repeated values in wherein using UNION ALL
$sql_query= 'SELECT u.*
FROM User u
JOIN ( SELECT 1 AS id UNION ALL SELECT 1 AS id
UNION ALL SELECT 2 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
) i ON i.id= u.id';
So, you can execute raw query like this
$results = DB::select($sql_query);
Another way (using eloquent method): I'm little bit confuse about below query but you can try this way using havingRaw
User::whereIn('id', $selected_tasknotes_persons_made_comment )
->havingRaw('count(*) > 1'); })->get()
add a comment |
As per your query User::whereIn('id', $selected_tasknotes_persons_made_comment )->get()
that's mean it's return uniqueness results, if you want to get duplicate records then follow below instruction:
you can execute raw query in laravel to get repeated values in wherein using UNION ALL
$sql_query= 'SELECT u.*
FROM User u
JOIN ( SELECT 1 AS id UNION ALL SELECT 1 AS id
UNION ALL SELECT 2 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
) i ON i.id= u.id';
So, you can execute raw query like this
$results = DB::select($sql_query);
Another way (using eloquent method): I'm little bit confuse about below query but you can try this way using havingRaw
User::whereIn('id', $selected_tasknotes_persons_made_comment )
->havingRaw('count(*) > 1'); })->get()
add a comment |
As per your query User::whereIn('id', $selected_tasknotes_persons_made_comment )->get()
that's mean it's return uniqueness results, if you want to get duplicate records then follow below instruction:
you can execute raw query in laravel to get repeated values in wherein using UNION ALL
$sql_query= 'SELECT u.*
FROM User u
JOIN ( SELECT 1 AS id UNION ALL SELECT 1 AS id
UNION ALL SELECT 2 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
) i ON i.id= u.id';
So, you can execute raw query like this
$results = DB::select($sql_query);
Another way (using eloquent method): I'm little bit confuse about below query but you can try this way using havingRaw
User::whereIn('id', $selected_tasknotes_persons_made_comment )
->havingRaw('count(*) > 1'); })->get()
As per your query User::whereIn('id', $selected_tasknotes_persons_made_comment )->get()
that's mean it's return uniqueness results, if you want to get duplicate records then follow below instruction:
you can execute raw query in laravel to get repeated values in wherein using UNION ALL
$sql_query= 'SELECT u.*
FROM User u
JOIN ( SELECT 1 AS id UNION ALL SELECT 1 AS id
UNION ALL SELECT 2 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
UNION ALL SELECT 1 AS id
) i ON i.id= u.id';
So, you can execute raw query like this
$results = DB::select($sql_query);
Another way (using eloquent method): I'm little bit confuse about below query but you can try this way using havingRaw
User::whereIn('id', $selected_tasknotes_persons_made_comment )
->havingRaw('count(*) > 1'); })->get()
edited Nov 14 '18 at 4:51
answered Nov 14 '18 at 4:39
Bilal AhmedBilal Ahmed
3,25231535
3,25231535
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%2f53293138%2fget-the-duplicate-data-using-wherein-form-sql-database%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