Conditionally get a value from one field but get each value of other columns

I need to show the stats of messages in campaigns, summing data in Responses

A Message may be used by many campaigns.

A Campaign has an end_date and a message id

Each Campaign may have many Responses with fields that will be summed.

Each row contains the sums for each message and the end_date ONLY if all campaigns with that message_id are ended ( have a Campaign-> end_date that is not null)

Results Grouped by Message_id. working in this form except for conditionally showing end_date

$resultsData = DB::table('results  as lg')
->leftJoin('campaigns as cp','campaign_id', '=','cp.id')
->leftJoin('messages as msg','message_id', '=','msg.id')

// this is where I need a end_date  ONLY is every campaign with this message has an end_date
->selectRaw('DATE_FORMAT(MIN(cp.end_date),"%b %d") as msg_end')
//  Sample sum below
->selectRaw('sum(lg.replied) as sumReplied') 
->groupBy('Message')
->get();

So how do I optionally show the 'cp.end_date'?

LaryAI

1 year ago

Best Answer

Level 58

To conditionally show the end_date only if all campaigns with a specific message_id have an end_date that is not null, you can use a combination of SQL functions and conditional logic. Specifically, you can use a CASE statement to check if there are any campaigns with a null end_date for each message_id. If there are none, you can display the minimum end_date.

Here's how you can modify your query to achieve this:

$resultsData = DB::table('results as lg')
    ->leftJoin('campaigns as cp', 'campaign_id', '=', 'cp.id')
    ->leftJoin('messages as msg', 'message_id', '=', 'msg.id')
    ->selectRaw('
        CASE 
            WHEN COUNT(cp.end_date) = COUNT(*) THEN DATE_FORMAT(MIN(cp.end_date), "%b %d")
            ELSE NULL
        END as msg_end
    ')
    ->selectRaw('SUM(lg.replied) as sumReplied')
    ->groupBy('message_id')
    ->get();

Explanation:

COUNT(cp.end_date) = COUNT(*): This checks if the count of non-null end_date values is equal to the total count of rows for each message_id. If they are equal, it means there are no null end_date values for that message_id.
DATE_FORMAT(MIN(cp.end_date), "%b %d"): This formats the minimum end_date for the message_id if all end_date values are non-null.
ELSE NULL: If there are any null end_date values, it returns NULL for msg_end.

This query will ensure that msg_end is only populated when all campaigns for a given message_id have an end_date.

Please or to participate in this conversation.