Eloquent examples to satisfy MySQL's ONLY_FULL_GROUP_BY mode

In two previous tips, I talked about why you should keep MySQL's ONLY_FULL_GROUP_BY mode enabled and how to use the ANY_VALUE() function to safely bend the rules when you know better than the database engine.

Both of those tips shared code examples in SQL, but that prompted a question from a reader about how to apply these fixes using Eloquent.

I'll repeat the SQL examples here, with the Eloquent equivalent below each one, but make sure to go back and read the original tips for the full context.

Here's the first example where we want to get the most recent message in each thread along with the two participants:

-- SQL query
SELECT thread_id, 
       MAX(id) AS most_recent, 
       MIN(sender_id) AS participant_1, 
       MAX(receiver_id) AS participant_2
FROM messages
GROUP BY thread_id
ORDER BY most_recent DESC;

// Eloquent equivalent
Message::query()
    ->select(
        'thread_id', 
        DB::raw('MAX(id) AS most_recent'), 
        DB::raw('MIN(sender_id) AS participant_1'), 
        DB::raw('MAX(receiver_id) AS participant_2'))
    ->groupBy('thread_id')
    ->orderByDesc('most_recent')
    ->get();

Keep in mind that even with the Eloquent syntax, we're still going to get back just an integer value for participant_1 and participant_2. We'd need to do a little more work if we wanted those participants to be hydrated as User objects, like we might get with a sender() or receiver() relationship on the Message model.

And here's the second example where we want to count the number of pending orders for each customer, while asserting that the customer_name column is not ambiguous:

-- SQL query
SELECT  customer_id,
        ANY_VALUE(customer_name) AS customer_name,
        COUNT(*) AS order_count
FROM    pending_orders
GROUP BY customer_id;

// Eloquent equivalent
PendingOrder::query()
    ->select(
        'customer_id', 
        DB::raw('ANY_VALUE(customer_name) AS customer_name'), 
        DB::raw('COUNT(*) AS order_count'))
    ->groupBy('customer_id')
    ->get();

Here to help,

Joel

P.S. I'm happy to answer email questions, but you can also ask your Laravel question in the Mastering Laravel Slack community.