Converting MySQL Query to Eloquent

|date_time |   callid   |   event   |
|----------|------------|-----------|
|1528669182|1528669531.4|ENTERQUEUE |
|1528669199|1528669531.4|EXITWITHKEY|
|1528669215|1528669531.4|CALLBACK   |
|1528669232|1527293006.0|ENTERQUEUE |
|1528669244|1527293006.0|ABANDON    |

In the example table above, I'd like to get the most recent items for each callid in the query. In the example above I should only get the CALLBACK event for callid 1528669531.4 and ABANDON for callid 1527293006.0

Why? I mean, why take a perfectly working query, convert it to eloquent, just so that php has to parse it back down (wasting time/resources) to your original working sql? What do you gain?

Mainly just to be flexible and make sections of the query adjustable.

Just use parameter binding. The only "variable" in there is event.

$bindings = ['event' => 'CALLBACK'];

$result = DB::unprepared("SELECT *
    FROM queue_log
    WHERE date_time IN (
        SELECT MAX(date_time)
        FROM queue_log
        GROUP BY callid
    )
    AND event = :event", $bindings);

Now just change the event you want in the $bindings.

@Nicholas this look like this

$first = DB::table('queue_log')->max('date_time')->groupBy('callid')->get()->toArray();

$query = DB::table('queue_log')
            ->whereIn('date_time', $first)
            ->where('event','CALLBACK')
            ->get();

Here it's in Eloquent:

    $items = Items::whereIn('date_time', function($query){
            $query->selectRaw('MAX(date_time)')->groupBy('callid');
    })->where('event', 'CALLBACK')->get();

You need to use a subquery, and you need to use selectRaw for the max date_time instead of just $query->max('date_time') because it renames it to aggregate which will give you an sql error.

Now which is more straightforward to read and understand what's going on? I didn't say it couldn't be done in eloquent, just that it's a waste to do it. It's like saying "I have this English, that I want to convert to Chinese, so that I can convert it back to English everytime I need it". It's also a lot less readable than the raw sql query, and takes resources to convert back to raw sql. There is no advantage to doing it, only disadvantages. But, hey, at least it's "in eloquent" now...

@Cronix

Well, I come from stack overflow, and for me it's important to answer the users question and then have a discussion why something else would be better. However, the point that it takes "more resources" is laughable at best to be honest.

Let's agree to disagree.

@Cronix

I disagree. And I will your example to explain.

I have this piece of text that is english, and yes, when I do work with the piece of text, that work will be in english. This is where we diverge... because we all know Latin is used in most major languages... at least parts and pieces, and latin is also used in science and other branches of study... so screw all our languages... lets just do everything in latin.

Thats your logic. My logic, is to use the tools of the framework. Laravel has provided us a nice tool in Eloquent, and sure, if you want to go with straight mysql, go for it... but mixing is a bad idea.

My advise to the OP is simple, is this specific query different on your system? As in, is the majority of your application using eloquent or mysql? If it is using eloquent, then I would suggest converting. If you are using mostly mysql... id advise to see what your end goal is... are you going to be converting everything to use eloquent models? OR will it mostly stay as mysql. Do not jump to a conclusion because somebody says the system will convert it... you want uniformity in your code... otherwise you will hate yourself when you must maintain it... trust me... i have learned this from experience.

However, the point that it takes "more resources" is laughable at best to be honest.

Then you are just simply wrong. Converting from eloquent back to raw sql takes resources. Explain why it wouldn't take extra resources to do that.

@Cronix

Let me correct myself then. I didn't mean it doesn't take more resources at all. All I meant is that it's negligible. Believe what you want, but in 99% use cases, it doesn't matter.

However, if you really need that optimization, by all means go for it.

Honestly the underlying query is the same and the data sets will be so small there is not really a performance impact on the application.