Level 73
This is the kicker
->where('parent_id', $this->brandId)
This might shed some light
https://chartio.com/learn/databases/how-to-find-duplicate-values-in-a-sql-table/
1 like
Dec 22, 2021
3
Level 1
Laravel Query : find duplicates records based on multi conditions and group the result duplicates
I have a table of Shops and there is some duplicates records and i need to get them out but with some conditions :
- Get duplicates based on Name and Address
OR
- if they have same Type AND Status
i have used this solution but it only get 1 record and i want to get all duplicate records and also group them.
Shop::where(function ($query) {
$query->whereIn('id', function ( $query ) {
$query->select('id')
->from('brands')
->where('parent_id', $this->brandId)
->groupBy(['name', 'adress'])
->havingRaw('count(*) > 1');
})
->orWhereIn('id', function ( $query ) {
$query->select('id')
->from('brands')
->where('parent_id', $this->brandId)
->groupBy(['type', 'status'])
->havingRaw('count(*) > 1 ');
});
})
->get();
Level 1
@Tray2 Thank you, but using mysql i did this but the issue is my (OR) conditions i don't know how to apply them here. like the result here is not applying the (OR) condition even if i use left join
select
brands.*
from
brands
join (
select
name,
adress,
count(*)
from
brands
group by
name,
adress
having
count(*) > 1
) brands1 on brands.name = brands1.name
and brands.adress = brands1.adress
join (
select
type,
status,
id_on_platform,
count(*)
from
brands
group by
status,
type
having
count(*) > 1
) brands2 on brands.status = brands2.status
and brands.type = brands2.type
WHERE
brands.parent_id = 7182
Level 73
@anejjar You should use a union or a union all instead of a join to make it one query. Then you can wrap that select with another one and use where on it
SELECT *
FROM (SELECT *
FROM table1
WHERE <condition>
UNION
SELECT *
FROM table1
WHERE <condition>)
WHERE <condition>
Please or to participate in this conversation.