Laravel raw query to eloquent model

Question

SELECT
    *
FROM
    sub_task_information st
WHERE
    created_at = (
        SELECT MAX(created_at) 
       FROM sub_task_information st2 
       WHERE st2.sub_task_id = st.sub_task_id 
             AND st2.user_id = st.user_id
    )

Need help to convert this raw query into eloquent model SubTaskInfo?

Answer 1

You can easily test queries with the tinker shell without even hitting your database. Just use ->toSql() instead of ->get() to return the query.

This query has two tricky parts: The filtering by a subquery and the column comparison.

The first is not really documented at the moment, but the where method covers that use case if you use it like this: ->where(column, Closure).

For the second part, you need to use the ->whereColumn method like this ->where(column1, column2).

// Using plain Query Builder
$data = DB::table('sub_task_information', 'st')
    ->where('created_at', function ($query) {
        return $query->selectRaw('max(created_at)')
                     ->from('sub_task_information', 'st2')
                     ->whereColumn('st2.sub_task_id', 'st.sub_task_id')
                     ->whereColumn('st2.user_id', 'st.user_id');
    })
    ->get();

This is just using the Query Builder. If you want to use Eloquent, you'll need a model that is mapped to the sub_task_informations table.

If you have a model, for example App\Models\SubTaskInformation, you can still do the same query.

Instead of just replace DB::table('sub_task_information', 'st')... with SubTaskInformation::from('sub_task_information', 'st')

Usually you don't need the from method but in this query, you want to add an alias to your table in the outer select.

// Using an Eloquent model while aliasing the table.
$data = SubTaskInformation::from('sub_task_information', 'st')
    ->where('created_at', function ($query) {
        return $query->selectRaw('max(created_at)')
                     ->from('sub_task_information', 'st2')
                     ->whereColumn('st2.sub_task_id', 'st.sub_task_id')
                     ->whereColumn('st2.user_id', 'st.user_id');
    })
    ->get();