Order "Project" (parent) table by closest deadline date in "ProjectModule" table (child)

There are two tables: PROJECTS

id
title
...

and PROJECT_MODULES

id
project_id
deadline_at
...

I'm trying to create scope method for PROJECT model to order it by closest deadline (in future). I have the scope method for PROJECT_MODULES model:

public function scopeWithFutureDeadlines($query)
    {
        $query->whereDate('deadline_at', '>=', Carbon::now());
    }

and it works OK. Then I made it for PROJECTS too:

public function scopeOrderByNextDeadline(Builder $query)
    {
        $query->withMin('modules', 'deadline_at')
->orderBy('modules_min_deadline_at');
    }

but it is incorrect, cause i want to sort only by deadlines in future. Any suggestions?

andiliang

4 years ago

Level 39

@er_nesto you want to order the PROJECT_MODULES deadline when fetching data from project right ? then you can use join or sub query

    return User::query()->orderBy( function($query){
        $query->select('created_at')
            ->from('orders')
            ->latest()
                ->whereColumn('user_id', 'users.id')
                ->limit(1);
    },'desc')->get();

I can do above subquery ordering in my local

maybe in your case you may try below:

Project::orderBy( function($query){

        $query->select('deadline_at')
            ->from('PROJECT_MODULES ') // table name of PROJECT_MODULES 
            ->latest()
                ->whereColumn('project_id', 'PROJECT_MODULES .id') //  I assume foreign ID here you need put the real foreign id
                ->limit(1);
},'desc')->get();

1 like

er_nesto

4 years ago

Level 1

@andiliang THANK YOU! YES! This is almost that i'm looking for!

you want to order the PROJECT_MODULES deadline when fetching data from project right ?

not exactly, i'll try to explain it better =)

I have projects and project_modules tables (project_modules has index [project_id]).

Each project may have or may have not modules.
Each module has [deadline_at] (obviously, deadline date).

And I want to get all the projects in this order:

Project #7: deadline in 2 hours
Project #3: deadline in 5 days
Project #5: deadline in 1 month
Project #6: last deadline was 3 days ago
Project #4: last deadline was 1 week ago
Project #2: no deadlines (no modules)
Project #1: no deadlines (no modules)

(Basically, I don't care about orderting the projects in past and projects without modules, but it looks beter in that order)

Based on your answer i have made a scope method in my Project model:

public function scopeOrderByNextDeadline(Builder $query)
    {
        $query->orderBy(function (\Illuminate\Database\Query\Builder $query) {
            $query->select('deadline_at')
                ->from('project_modules')
                ->whereColumn('projects.id', 'project_modules.project_id')
                ->where('deadline_at', '>', Carbon::now()->toDateTimeString())
                ->limit(1);
        });
    }

...and it is almost correct, but projects without modules go first. It is pretty complicated for me now, because i'm working on it too much time and my brains reject any ideas =D Thank you so much, again!

andiliang

4 years ago

Level 39

@er_nesto you may try to revert the ordering by remove 'desc'


    return User::query()->orderBy( function($query){
        $query->select('created_at')
            ->from('orders')
            ->latest()
                ->whereColumn('user_id', 'users.id')
                ->limit(1);
    })->get();

will it help ? thanks

er_nesto

4 years ago

Level 1

@andiliang no, cause in this case the modules with deadlines will be ordered in wrong way....

Please or to participate in this conversation.