[Proposal] Add joinDerived() method in query builder.

[ajcastro]

This will save you from a lot of converting query builders to raw queries. This works in laravel version 4.2 and I think will also work in laravel 5., tho I have only tested it in 4.2. I think this can also be done in macros in laravel version 5.. Please comment below if you have tested it in laravel version 5.*.

public function scopeJoinDerived($query, $derivedQuery, $table, $one, $operator = null, $two = null, $type = 'inner', $where = false)
{
    $query->join(DB::raw("({$derivedQuery->toSql()}) as `{$table}`"), $one, $operator, $two, $type, $where);
    $join = last($query->getQuery()->joins);
    $join->bindings =  array_merge($derivedQuery->getBindings(), $join->bindings);

    return $query;
}

public function scopeLeftJoinDerived($query, $derivedQuery, $table, $first, $operator = null, $second = null)
{
    return $query->joinDerived($derivedQuery, $table, $first, $operator, $second, 'left');
}

public function scopeJoinDerivedWhere($query, $derivedQuery, $table, $one, $operator = null, $two = null, $type = 'inner')
{
    return $query->joinDerived($derivedQuery, $table, $one, $operator, $two, $type, true);
}

public function scopeLeftJoinDerivedWhere($query, $derivedQuery, $table, $one, $operator = null, $two = null)
{
    return $query->joinDerived($derivedQuery, $table, $one, $operator, $two, 'left', true);
}

After doing so, you may now easily join to your derived tables easily.

class SampleController extends Controller
{
    public function index()
    {
        $countPostQuery = Post::select([
            DB::raw('count(*) as count'),
            DB::raw('author_id'),
        ])
        ->where('is_published', '=', 1)
        ->groupBy('author_id');

        $query = Author::leftJoinDerived($countPostQuery, 'posts', 'posts.author_id', '=', 'authors.id')
            ->where('date_joined', '>=', \Carbon\Carbon::now()->subMonth()->format('Y-m-d'));

        return [
            $query->toSql(),       // see that the query has pdo bindings using ?
            $query->getBindings(), // see that the input parameters are set accordingly
        ];
    }
}

As you can see sql input parameters are properly set with the bindings.

[
    "select * from `authors` left join (select count(*) as count, author_id from `posts` where `is_published` = ? group by `author_id`) as `posts` on `posts`.`author_id` = `authors`.`id` where `date_joined` >= ?",
    [
        1,
        "2017-10-22"
    ]
]

Related post: https://ajcastro29.blogspot.com/2017/11/laravel-join-derived-tables-properly.html

[sisve]

Seriously? This is what Stack Overflow would call a "link only" entry. It has nothing to add at all unless we all go and increase your visitor numbers. And what happens in 6 months when you decide that instagram is better than blogspot, and the url no longer works?

If you're serious, write your proposal in the github issue, not in your personal blog.

This works in laravel version 4.2 and I think will also work in laravel 5., tho I have only tested it in 4.2. I think this can also be done in macros in laravel version 5.. Please comment below if you have tested it in laravel version 5.*.

Where are we expected to write those comments? In your blog post or in this github issue?

Do you see how problematic it is to just link to a blog post?

[ajcastro]

I apologize I didn't realize that immediately. Thanks for pointing that out btw. That's actually a blog for my collection of daily learnings and discoveries and meant to be shared and just started yesterday. Lemme fix the description above.

[staudenmeir]

There's now joinSub(): laravel/framework#23818

[fico7489]

joinSub() is suitable for raw queries e.g.

DB::table('subtable')->

but there is no solution to join table by relation, this package solves this : https://github.com/fico7489/laravel-eloquent-join