Yii2 select elements by MAX date

Question

Im struggling with this and tried different groupby options with no success. This is my table

id  date         advertiserID    providerID    companyID
1   2022-03-01   3               9             3
2   2022-08-01   3               9             3
3   2022-02-01   700             52            3
4   2022-12-01   456             15            1
5   2022-11-01   3               9             2

And im trying to select max date for each record with the same advertiser and provider so that, as per above table, it would return:

id  date         advertiserID    providerID    companyID
3   2022-02-01   700             52            3
4   2022-12-01   456             15            1
5   2022-11-01   3               9             2 <--- this is the max date for advid 3 and provid 9

Tried with this but it returns MAX date with wrong id and companyID:

$currentAssoc = CompaniesAdvertisersProvidersAssoc::find()
    ->select("MAX(date) as date, companiesAdvertisersProviders.id as bmAssocID, advertiserID, providerID, companyID, advertisers.name as advertiser, providers.name as provider, companies.id as companyID, companies.name as company, advertisers.image as advertiserImage, providers.image as providerImage, companies.companyLogo as bmImage")
    ->joinWith(['advertiser', 'provider', 'company'], false)
    ->groupBy(['advertiserID', 'providerID'])
    ->orderBy([
        'company'       => SORT_ASC,
        'advertiser'    => SORT_ASC
        ]
    )
    ->asArray()
    ->all();

---

id  date         advertiserID    providerID    companyID
4   2022-12-01   456             15            1
1   2022-11-01   3               9             3 <--- id should be 5 and companyid 2
3   2022-02-01   700             52            3

Thank you in advance,

Answer 1

if you want to see also companyID, it should be also in grouped by section. Try to test simple SQL query which works and then add joins and other columns. This works:

create table aaa (id int, dd date, aid int, pid int, cid int);
insert into aaa values (1, '2022-03-01', 3 ,9 ,3);
insert into aaa values (2, '2022-08-01', 3 ,9 ,3);
insert into aaa values (3, '2022-02-01', 700 ,52,3);
insert into aaa values (4, '2022-12-01', 456 ,15,1);
insert into aaa values (5, '2022-11-01', 3 ,9 ,2);

# select max(dd), aid, pid, cid from aaa group by aid,pid,cid;
    max     | aid | pid | cid 
------------+-----+-----+-----
 2022-12-01 | 456 |  15 |   1
 2022-02-01 | 700 |  52 |   3
 2022-11-01 |   3 |   9 |   2
 2022-08-01 |   3 |   9 |   3
(4 rows)