CODEWITHSUNDEEP
sqllaraveleloquent
Sebastien
Sebastien

Reputation: 1

How to write subquery with same table in Laravel Eloquent?

How can I translate this SQL to Eloquent?

SELECT *
FROM actions A1
WHERE A1.recurring_pattern IS NOT NULL AND
    A1.parent_id IS NOT NULL AND
    A1.due_date = (
        SELECT MIN(due_date)
        FROM actions A2
        WHERE A2.parent_id = A1.parent_id AND 
            due_date > NOW()
    )

Upvotes: 0

Views: 234

Answers (1)

Christian
Christian

Reputation: 831

If I'm not mistaken, what you want to do is to get one action per sibling of actions, which satisfies the conditions:

  • the action must not be past due, but should be due next,
  • the action must have a recurring pattern, and
  • (of course) the action must have a parent.

If that's the case, your query can be simplified to:

SELECT * FROM actions
WHERE recurring_pattern IS NOT NULL
    AND parent_id IS NOT NULL
    AND due_date > NOW()
GROUP BY parent_id
HAVING MIN(due_date)

Basically you SELECT actions that satisfy the outlined conditions above, then GROUP them BY their parents, and finally pick one action per group HAVING the closest due date. This approach to querying eliminates the need to subquery.

Now, onto implementing the simplified query in Laravel Eloquent, we can define an Action model as follows:

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Carbon;

class Action extends Model
{
    /**
     * Scope to actions that have recurring pattern.
     */
    function scopeHasRecurringPattern(Builder $query)
    {
        $query->where('recurring_pattern', '<>', null);
    }

    /**
     * Scope to actions that have parents.
     */
    function scopeHasParent(Builder $query)
    {
        $query->where('parent_id', '<>', null);
    }

    /**
     * Scope to actions not due by the given date.
     */
    function scopeIsNotDueBy(Builder $query, Carbon $now)
    {
        $query->where('due_date', '>', $now);
    }
}

Then finally, we perform the query by doing:

Action::hasRecurringPattern()    // 👈 filter actions having recurring patterns
    ->hasParent()                // 👈 filter actions having parents
    ->isNotDueBy(now())          // 👈 filter actions not past due
    ->groupBy('parent_id')       // 👈 group these filtered actions by identical parents
    ->havingRaw('MIN(due_date)') // 👈 finally pick the action per group having the closest due date
    ->get();

Upvotes: 1

Related Questions