CODEWITHSUNDEEP
phpmysqllaravel
Tifa Ahmed
Tifa Ahmed

Reputation: 81

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();

Upvotes: 0

Views: 501

Answers (2)

Bilal Ahmed
Bilal Ahmed

Reputation: 4066

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()

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35573

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

Upvotes: 2

Related Questions