Need Advice: query to count totals of relationship by date of outer query

I have two models: Lead & LeadAccepted

A Lead can have many LeadAccepted and a LeadAccepted belongs to a Lead.

Now I am trying to display an aggregated report of accepted leads, and include a total_leads property that should be the number of leads on the day that the accepted leads are grouped by...

Where am I going wrong here:

LeadAccepted::query()

            ->selectRaw('COUNT(created_at) AS total_sold')

            ->addSelect([
                'total_leads' => Lead::selectRaw('COUNT(id)')
                    ->whereRaw('DATE(leads.created_at) = DATE(created_at)')
                    ->distinct()
            ])

            ->selectRaw('SUM(revenue) AS total_revenue')

            ->selectRaw('DATE(created_at) AS day')
            ->groupBy('day')
            ->orderByDesc('day');

The above gives me a result like:

| total_sold | total_leads | total_revenue | day        |
|------------|-------------|---------------|------------|
|          1 |          11 |          2049 | 2023-01-18 |
|          2 |          11 |          3348 | 2023-01-17 |

What is expect though, would be 10 total leads on Jan 17, and 1 total leads on Jan 18:

| total_sold | total_leads | total_revenue | day        |
|------------|-------------|---------------|------------|
|          1 |          1 |          2049 | 2023-01-18 |
|          2 |          10 |          3348 | 2023-01-17 |

tisuchi

3 years ago

Level 70

@madsem Does it work for you?

LeadAccepted::query()
            ->selectRaw('COUNT(lead_accepted.created_at) AS total_sold')
            ->addSelect([
                'total_leads' => Lead::selectRaw('COUNT(DISTINCT leads.id)')
                    ->leftJoin('lead_accepted', function ($join) {
                        $join->on('leads.id', '=', 'lead_accepted.lead_id');
                    })
                    ->whereRaw('DATE(leads.created_at) = DATE(lead_accepted.created_at)')
                    ->distinct()
            ])
            ->selectRaw('SUM(revenue) AS total_revenue')
            ->selectRaw('DATE(lead_accepted.created_at) AS day')
            ->groupBy('day')
            ->orderByDesc('day');

madsem

3 years ago

Level 4

@tisuchi thank you :)

Sadly no, the result is now:

| total_sold | total_leads | total_revenue | day        |
|------------|-------------|---------------|------------|
|          1 |           2 |          2049 | 2023-01-18 |
|          2 |           2 |          3348 | 2023-01-17 |

So now it only counts the leads that also have lead_accepted relationship. But not all Lead models, have accepted status. Hmmmmm :D

tisuchi

3 years ago

Level 70

@madsem How about you try this?

        LeadAccepted::query()
            ->selectRaw('COUNT(lead_accepted.created_at) AS total_sold')
            ->addSelect([
                'total_leads' => DB::table(DB::raw('(SELECT COUNT(*) as total_leads, DATE(created_at) as day FROM leads GROUP BY day) as leads_count'))
                    ->leftJoin('lead_accepted', 'leads_count.day', '=', 'DATE(lead_accepted.created_at)')
                    ->value('total_leads')
            ])
            ->selectRaw('SUM(revenue) AS total_revenue')
            ->selectRaw('DATE(lead_accepted.created_at) AS day')
            ->groupBy('day')
            ->orderByDesc('day');

madsem

3 years ago

Level 4

@tisuchi

That gives an error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DATE(lead_accepted.created_at)' in 'on clause'

Please or to participate in this conversation.