CODEWITHSUNDEEP
phpsqllaravelpostgresqleloquent
JeremyA
JeremyA

Reputation: 39

How to convert left join lateral SQL query to Laravel Eloquent query?

I would like to convert this following Postgre SQL query in an Eloquent query:

SELECT ncts_info.nct_id, A.agency_model, ncts_info.indexation_id
FROM ncts_info
LEFT JOIN (
    drug_centers LEFT JOIN LATERAL json_to_recordset(nct_numbers) AS tbl(nct_number text) ON true
) A ON A.nct_number = ncts_info.nct_id

How can I convert this into an Eloquent query?

Upvotes: 1

Views: 3030

Answers (5)

Pablo
Pablo

Reputation: 3

I know this is an old question, but you can use this way.

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Expression;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     */
    public function register(): void
    {
        //
    }

    /**
     * Bootstrap any application services.
     */
    public function boot(): void
    {
        Builder::macro('joinLateral', function ($query, $as, $type = 'inner') {
            if (!in_array($type, ['inner', 'left', 'right', 'outer', 'cross'])) {
                throw new \InvalidArgumentException("Invalid type (". $type .") use: inner | left | right | outer | cross");
            }

            if (!(($query instanceof \Closure) || ($query instanceof \Illuminate\Database\Query\Builder))) {
                throw new \InvalidArgumentException('$query need to be an instance of \Closure OR \Illuminate\Database\Query\Builder');
            }

            [$subQuery, $bindings] = $this->createSub($query);

            $expression = "lateral ($subQuery) as {$this->grammar->wrapTable($as)} on true";

            $join = $this->newJoinClause($this, $type, new Expression($expression));

            $this->joins[] = $join;

            $this->addBinding($bindings, 'join');

            return $this;
        });

        Builder::macro('innerJoinLateral', function ($query, $as) {
            return $this->joinLateral($query, $as, 'inner');
        });

        Builder::macro('leftJoinLateral', function ($query, $as) {
            return $this->joinLateral($query, $as, 'left');
        });

        Builder::macro('rightJoinLateral', function ($query, $as) {
            return $this->joinLateral($query, $as, 'right');
        });
        ....
    }
}

this way:

$db = DB::table($this->table, 'com'); 
$db->select($fields);
$db->joinLateral($subAddr, 'addr', 'left');

or this way:

$db = DB::table($this->table, 'com');
$db->select($fields);
$db->leftJoinLateral($subAddr, 'addr');

and others

Upvotes: 0

BARNZ
BARNZ

Reputation: 1329

This library adds a bunch of postgres-specific features to laravel including the ability to perform a lateral join:

https://github.com/tpetry/laravel-postgresql-enhanced#lateral-subquery-joins

$latestPostsBuilder = Post::select('id', 'created_at')
    ->whereColumn('user_id', '=', 'users.id') // This would not work without LATERAL join
    ->orderByRaw('created_at DESC NULLS LAST') // Put an index on this!
    ->limit(1);

User::lateralLeftJoin($latestPostsBuilder, 'latest_post')
    ->orderBy('latest_post.created_at') // Ordering users by their latest post!
    ->get([
        'users.*',
        'latest_post.*'
    ]);

Normally you would just eagerload the latest post for each user via a relationship. But in cases where you want to be able to order users by specific fields of the latest post in SQL this is a good way to do it.

A regular leftJoinSub also works for most cases, but if your join subquery is a very large table its faster to use the above lateral join approach

Upvotes: 0

Kressaty
Kressaty

Reputation: 41

In your second response here, you mention that you need to reference the join as A - you don't need to. You can change A to drug_centers in Laravel as it will be aliased as the table name.

FWIW, I solved the original question by creating a custom Macro for the Builder class that will let you do a join prefixed with the LATERAL keyword:

Builder::macro('joinSubLateral', function ($query, $as, $first, $operator = null, $second = null, $type = 'inner', $where = false) {
    [$query, $bindings] = $this->createSub($query);

    $expression = 'LATERAL ('.$query.') as '.$this->grammar->wrapTable($as);

    $this->addBinding($bindings, 'join');

    return $this->join(new Expression($expression), $first, $operator, $second, $type, $where);
});

Put this in the register method of a Service Provider and you're all set!

Keep in mind it doesn't check for database compatibility.

Upvotes: 2

MandyF
MandyF

Reputation: 27

The best way I've found to do this is to define a subquery first. From the laravel docs:

Posts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
               ->where('is_published', true)
               ->groupBy('user_id');

$users = DB::table('users')
    ->joinLeftSub($latestPosts, 'latest_posts', function ($join) {
        $join->on('users.id', '=', 'latest_posts.user_id');
    })->get();

Upvotes: 0

Raja
Raja

Reputation: 1

You can use DB like that in laravel

$data = DB::table('ncts_info')
->leftJoin('drug_centers', 'drug_centers.foreign_key', 'ncts_info.id')
->select('ncts_info.nct_id','A.agency_model','ncts_info.indexation_id')
->get();

This would produce the following query:

SELECT "ncts_info"."nct_id", "A"."agency_model", "ncts_info"."indexation_id"
FROM "ncts_info"
LEFT JOIN "drug_centers" ON "drug_centers"."foreign_key" = "ncts_info"."id";

Upvotes: -1

Related Questions