Как в Laravel связать 3 таблицы и объединить строки по дате?

Question

[apr1cot]

Добрый день. Уже третьи сутки бьюсь над казалось бы простой задачей.

У меня имеются 3 таблицы. сampaign, keitaros и googles. Через отношения "многие ко многим" я получаю все связанные по campaign_id строки. С этим проблем нет.

Скриншот выборки

У таблиц "keitaros" и 'googles" есть по 4 записи. Две записи за 15.08.2023 число, и еще две записи за 16.08.2023

Проблема заключается в том, что я никак не могу объединить эти записи по датам.

У меня получилось сделать так, и это работает, но мне нужно, чтобы выборка была в рамках одной коллекции.

$campaigns = Campaign::with('keitaros', 'googles')->find(1);

$campaigns->googles->map(function ($item) {
    dump($item->select('created_at', DB::raw('SUM(cost) as total_cost'))->groupBy('created_at')->get());
});

$campaigns->keitaros->map(function ($item) {
    dump($item->select('created_at', DB::raw('SUM(income) as total_income'))->groupBy('created_at')->get());
});

Comments

[Дмитрий]

можно через джоин это оформить. однако стоит задаться вопросом почему у вас получилось так что похоже одинаковая структура лежит в разных таблицах?

Через джоин как то так

$campaign = Campaign::with('keitaros', 'googles')->find(1);
$campaign->keitaros()->join('googles', function($joinClause){
       $joinClause->on('googles.campaign_id', '=', 'keitaros.campaign_id')->where('googles.date', '=', 'keitaros.date');
})->selectRaw('SUM(keitaros.income) as keitaros_total_income, SUM(googles.income) as googles_total_income');

[apr1cot]

Дмитрий, Спасибо! К сожалению, не сработало. Сейчас буду додумывать

однако стоит задаться вопросом почему у вас получилось так что похоже одинаковая структура лежит в разных таблицах?

Хороший вопрос. У меня как раз появилась идея, чтобы засунуть всё в 1 таблицу, и создать промежуточную для объединения.

[Дмитрий]

apr1cot,
1. ну я писал навскидку не зная ни моделей ни связей, ничего. это показать принцип.
2. Ну бритва оккама хорошо работает в it - не надо умножать сущности сверх необходимого
3. Не понял. Если у вас у таблицы keitaros и googles несут один смысл, и схожи по структуре - у вас должна быть одна таблица - а в ней поле type=googles или keitaros

[apr1cot]

Дмитрий,

Не понял. Если у вас у таблицы keitaros и googles несут один смысл, и схожи по структуре - у вас должна быть одна таблица - а в ней поле type=googles или keitaros

Блин. Точно! Можно же проще сделать, и через type сортировать и группировать. Спасибо!))

А разделил таблицы я потому, что в таблицу keitaros приходит postbak(доходов) с одного ресурса(трекера), а в таблицу google приходят ежедневные расходы от google adwords)) Вот я подумал, что лучше всего разделить на разные таблицы, а потом связывать их.

Answer 1

[apr1cot]

Спасибо Дмитрий за подсказку. В итоге сделал вот так:

Вместо трёх таблиц создал одну, и сделал выборку с помощью groupBy

$postbacks = DB::table('postbacks as p')
            ->select(
                DB::raw('DATE(p.created_at) as date'),
                'u.name as user_name',
                'c.name as campaign_name',
                'c.domain',
                'pr.name as provider_name',
                'pr.commission',
                DB::raw('SUM(p.income) AS t_income'),
                DB::raw('SUM(p.cost) AS t_cost')
            )
            ->join('campaigns as c', 'p.campaign_id', '=', 'c.id')
            ->join('providers as pr', 'c.provider_id', '=', 'pr.id')
            ->join('users as u', 'c.user_id', '=', 'u.id')
            ->whereBetween('p.created_at', ['2023-08-15', '2023-08-17'])
            ->where('c.user_id', 1)
            ->groupBy(DB::raw('DATE(p.created_at)'), 'c.id')
            ->orderBy('date', 'ASC')
            ->get();

Comments

[Akina]

Ну-ну... "у меня ещё есть таблица postbacks, но я вам об этом не расскажу..."

Убиццо.

[Akina]

Запрос в принципе - кривой. Неполная группировка. Вот обновится MySQL под системой до 8 версии - и начнутся проблемы с SQL Mode...

[apr1cot]

Akina, Прочитайте переписку под вопросом. Таблицу postbacks я создал тогда, когда мне Дмитрий подсказал, что можно сделать.

[apr1cot]

Akina,

Запрос в принципе - кривой. Неполная группировка. Вот обновится MySQL под системой до 8 версии - и начнутся проблемы с SQL Mode...

Сейчас стоит MySQL - 8.0. Всё работает. На проде вообще стоит MySQL 8.1. Тоже всё прекрасно работает

[Дмитрий]

apr1cot, насчет группировки Akina, то прав. То что у вас в селекте - должно быть в групп.

[apr1cot]

Дмитрий, А какие проблемы могут возникнуть, не подскажете? Просто интересно, что именно я сделал не так.

[Дмитрий]

apr1cot, что вам покажет SELECT @@sql_mode;

[Дмитрий]

apr1cot, когда увидите что там нет ONLY_FULL_GROUP_BY
то я уже писал на тему: Как в sail исправить SET GLOBAL sql_mode?

[apr1cot]

Дмитрий,

[Дмитрий]

apr1cot, тогда интересно почему он у вас не орет что у вас в select есть поля не перечисленные на этапе group by. может вы на уровне ларки меняете?

[apr1cot]

Дмитрий, хмм. может методы ларавела исправляют ошибку. сейчас конечный запрос, который отправляет сам ларавел выглядит таким образом

select DATE(p.created_at) as date, `u`.`name` as `user_name`, `c`.`name` as `campaign_name`, `c`.`domain`, `pr`.`name` as `provider_name`, `pr`.`commission`, SUM(p.income) AS t_income, SUM(p.cost) AS t_cost from `postbacks` as `p` inner join `campaigns` as `c` on `p`.`campaign_id` = `c`.`id` inner join `providers` as `pr` on `c`.`provider_id` = `pr`.`id` inner join `users` as `u` on `c`.`user_id` = `u`.`id` where `p`.`created_at` between '2023-08-15' and '2023-08-17' and `c`.`user_id` = 1 group by DATE(p.created_at), `c`.`id` order by `date` asc

[apr1cot]

Дмитрий, Вот сам исполненный запрос. хмм

[Дмитрий]

apr1cot, а это что покажет? SELECT @@SESSION.sql_mode;

[apr1cot]

Дмитрий,

[Дмитрий]

apr1cot, нет, ларка не будет менять список полей или группировок. но по факту у вас в select должно быть то что в group
если вы выбираете `c`.`name` - вы должны группировать по ней

[Дмитрий]

apr1cot, а select version(); ?

[apr1cot]

Дмитрий,

[Дмитрий]

apr1cot, ну значит я совсем забыл mysql при ONLY_FULL_GROUP_BY - он по идее должен выбрасывать ошибку если в селект есть поля которых нет в груп

[apr1cot]

нет, ларка не будет менять список полей или группировок. но по факту у вас в select должно быть то что в group
если вы выбираете `c`.`name` - вы должны группировать по ней

Дмитрий, а я группирую по DATE(p.created_at)

[Дмитрий]

apr1cot, я вижу. и при этом выбираете поле c.name - и это неверно - почему, давал ссылку в ответе

[apr1cot]

я вижу. и при этом выбираете поле c.name - и это неверно - почему, давал ссылку в ответе

Дмитрий, понятно. спасибо. c.name я взял из другой таблицы campaigns. может тут всё дело в join.

ну значит я совсем забыл mysql при ONLY_FULL_GROUP_BY - он по идее должен выбрасывать ошибку если в селект есть поля которых нет в груп

Дмитрий, Да, понимаю о чём вы. У меня вчера такое было. То что ты указываешь в SELECT, должно быть и в GROUP BY, а если не указать, то выводит ошибку. Может всё дело в JOIN, который подключает дополнительно таблицы. Я в SQL не очёнь силён. Сейчас самое главное, что всё работает))

[Дмитрий]

apr1cot, забудьте про sql. в голове сделайте двумерный массив пробуйте выбрать все строки с с какой-нибудь группировкой. И понимание придет. Если вы думаете join как то меняет сиутацию - ну два двумерных массива, и соединение по какому нибудь ключу. В DB будет ровно тоже самое.

[apr1cot]

Дмитрий, Понятно. Спасибо.

Answer 2

[testergood]

join неуместен?
https://laravel.com/docs/10.x/queries#joins

или я неправильно задачу понял...

Comments

[apr1cot]

Через join никак запрос не могу додумать. Мне нужно таблицах 'keitaros' и 'googles' объединить одинаковые записи по дате. т.е в этих таблицах у меня сейчас по 4 записи, а мне нужно отсортировать их так, чтобы в итоге получилось всего 2 записи(так как 2 записи содержат дату за 15.08.23, и еще 2 записи за 16.08.23)

[testergood]

А какой запрос был придуман?

[apr1cot]

testergood, Уже его удалил( В кратце, связывал через joinLeft все таблицы, и пытался сгруппировать по дате(повторяющиеся значения) через groupBy

Сейчас попробую новый составить.