Laravel，将两个查询合并为一个 [英] Laravel, merge two queries in one

问题描述

与该代码相比，我怎么能做得更好:

How could I do a better code than this one:

$data1 = UploadsPois::where('estado_carga', Util::UPLOAD_POIS_CARGA_INGRESADA)
    ->where('schema_country', $schema_country)
    ->orderBy('id', 'asc')
    ->get();

foreach ($data1 as $carga) {
    $carga->UserResponsable = User::findOrFail($carga->responsable);
    $carga->Pois            = Pois::where('upload_pois_id', $carga->id)->where('pois_validate', Util::POIS_INGRESADO)->orderBy('id', 'asc')->get();
    $carga->Log = LogsPois::where('upload_pois_id', $carga->id)
        ->where('schema_country', $schema_country)
        ->whereNull('address_id')
        ->orderBy('id', 'desc')
        ->first();
}
$tareas['data1'] = $data1;

// All this bucle takes ~13000 miliseconds

$data2 = UploadsPois::where('estado_carga', Util::UPLOAD_POIS_CARGA_DEVUELTA_REVISION)
    ->where('schema_country', $schema_country)
    ->where('revisado_por', \Auth::user()->id)
    ->orderBy('id', 'asc')
    ->get();

foreach ($data2 as $carga) {
    $carga->UserResponsable = User::findOrFail($carga->responsable);
    $carga->UserValidador   = User::findOrFail($carga->validado_por);
    $carga->Pois            = Pois::where('upload_pois_id', $carga->id)->where('pois_validate', Util::POIS_INGRESADO)->orderBy('id', 'asc')->get();
    $carga->Log             = LogsPois::where('upload_pois_id', $carga->id)
        ->where('schema_country', $schema_country)
        ->whereNull('address_id')
        ->orderBy('id', 'desc')
        ->first();
}
$tareas['data2'] = $data2;

// And this one takes ~ 20 or 50 miliseconds

那些气泡非常相似，如何合并一个单独的foreach和一个单独的UploadsPois模型调用? 我不确定如何在同一过程中设置$tareas['data1']和$tareas['data2'].

Those bucles are pretty the same, how could I merge in one single foreach and 1 single call to UploadsPois model? I'm not sure how could I set $tareas['data1'] and $tareas['data2'] in the same process.

推荐答案

看看这段代码，我可以知道有4个重要模型:UploadsPois，User，Pois，LogPois.

Looking at this code, I can tell there are 4 important models: UploadsPois, User, Pois, LogPois.

您可以设置关系以加载所有这些数据而不必循环.

You can set up relationships to load all this data without having to loop.

请参见雄辩的关系，您还可以如下定义关系的倒数.

You can also define the inverse of the relationships as follows.

# User model
namespace App;

// Usually User model extends this instead of base model.
use Illuminate\Foundation\Auth\User as Authenticatable;
use UploadsPois;

class User extends Authenticatable
{
    public function responsable_uploads_pois()
    {
        return $this->hasMany(UploadsPois::class, 'responsable');
    }

    public function validador_uploads_pois()
    {
        return $this->hasMany(UploadsPois::class, 'validado_por');
    }
}

# Pois model
namespace App;

use Illuminate\Database\Eloquent\Model;
use UploadsPois;

class Pois extends Model
{
    public function uploads_pois()
    {
        return $this->belongsTo(UploadsPois::class, 'upload_pois_id');
    }
}

# LogPois model
namespace App;

use Illuminate\Database\Eloquent\Model;
use UploadsPois;

class LogPois extends Model
{
    public function uploads_pois()
    {
        return $this->belongsTo(UploadsPois::class, 'upload_pois_id');
    }
}

现在我们已经定义了所有关系，可以按以下方式获取您的$data1变量:

Now that we have all relationships defined, your $data1 variable can be obtained as follows:

UploadsPois::where([
    ['estado_carga', Util::UPLOAD_POIS_CARGA_INGRESADA],
    ['schema_country', $schema_country]
])
->with([
    'user_responsable',
    'pois' => function ($pois) {
        $pois->where('pois_validate', Util::POIS_INGRESADO);
    },
    'log' => function ($log) use ($schema_country) {
        $log->where('schema_country', $schema_country)
        ->whereNull('address_id')
        ->orderBy('id', 'desc');
    }
])
->orderBy('id', 'asc')
->get();

与$data2一样:

UploadsPois::where([
    ['estado_carga', Util::UPLOAD_POIS_CARGA_DEVUELTA_REVISION],
    ['schema_country', $schema_country],
    ['revisado_por', auth()->id()] //Same as \Auth::id(), same as \Auth::user()->id
])
->with([
    'user_responsable',
    'user_validador',
    'pois' => function ($pois) {
        $pois->where('pois_validate', Util::POIS_INGRESADO)
    },
    'log' => function ($log) use ($schema_country) {
        $log->where('schema_country', $schema_country)
        ->whereNull('address_id')
        ->orderBy('id', 'desc');
    }
])
->orderBy('id', 'asc')
->get();

Laravel命名约定规定您的关系方法必须在snake_case中.

Laravel naming conventions dictate your relationship methods must be in snake_case.

关于合并这些查询.我看到的唯一区别如下:

About combining those queries. The only differences I see are the following:

• $data1的estado_carga等于Util::UPLOAD_POIS_CARGA_INGRESADA，而$data2的estado_carga等于Util::UPLOAD_POIS_CARGA_DEVUELTA_REVISION
• $data2具有一个附加过滤器(validado_por等于已验证用户的id)
• $data2已加载其他关系(user_validador)

• $data1 has estado_carga equal to Util::UPLOAD_POIS_CARGA_INGRESADA whereas $data2 has estado_carga equal to Util::UPLOAD_POIS_CARGA_DEVUELTA_REVISION
• $data2 has an additional filter (validado_por equal to authenticated user's id)
• $data2 has an additional relationship loaded (user_validador)

如果您真的想合并查询，则最初不能仅按这两个条件进行过滤.

If you want really want to combine the queries, you could just not filter by those 2 conditions initially.

$data = UploadsPois::where('schema_country', $schema_country)
->with([
    'user_responsable',
    'user_validador',
    'pois' => function ($pois) {
        $pois->where('pois_validate', Util::POIS_INGRESADO)
    },
    'log' => function ($log) use ($schema_country) {
        $log->where('schema_country', $schema_country)
        ->whereNull('address_id')
        ->orderBy('id', 'desc');
    }
])
->orderBy('id', 'asc')
->get();

这将返回一个集合，然后您可以使用多种方法(where，firstWhere，filter，reject等)进行过滤

This returns a collection, which you can then filter using a variety of methods (where, firstWhere, filter, reject, etc)

# data1
$data->where('estado_carga', Util::UPLOAD_POIS_CARGA_INGRESADA);

# data2
$data->where('estado_carga', Util::UPLOAD_POIS_CARGA_DEVUELTA_REVISION)->where('validado_por', auth()->id());

请参见集合:可用方法

这篇关于Laravel，将两个查询合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！