如何动态构建此查询-Laravel/Lumen [英] How to dynamically build this query - Laravel/Lumen

问题描述

我从用户那里得到以下输入:

I have the following input from the user:

array (
  'id_coretable' => 1,
  'Internal_key' => 'UPDATED1',
  'extensiontable_itc' => 
  array (
    'description_itc' => 'UPDATED1',
  ),
  'extensiontable_sysops' => 
  array (
    'description_sysops' => 'UPDATED1',
  ),
)  

及其内容应更新以下模型:

and its contents shall update the following model:

array (
  'id_coretable' => 1,
  'Internal_key' => 'TESTKEY_1',
  'extensiontable_itc' => 
  array (
    'description_itc' => 'EXTENSION_ITC_1',
  ),
  'extensiontable_sysops' => 
  array (
    'description_sysops' => 'EXTENSION_SYSOPS_1',
  ),
)  

此模型是使用以下代码创建的:

This model was created with this code:

$joinAsArray = coretable::with($permittedTables)->find(1);

$permittedTables是表名数组，用于确定要与coretable联接的表.

Where $permittedTables is an array of tablenames which determine the tables to be joined with coretable.

现在，我花了很多时间思考如何正确地遍历一个模型，而且如果不将模型序列化为一个普通数组或类似对象，这简直是不可能的. 但是，这并没有真正的帮助，因为我想在这里更新模型，并且如果我只是将其转换为数组，那么我将失去与数据库/模型的连接.

Now, I've spent multiple hours on pondering how to properly loop over a model, and it simply isnt possible without serializing the model to a plain array or the like. This doesnt really help though, since I want to UPDATE the model here, and if I just convert it to an array, then I lose this connection to the DB/Model.

所以我现在使用另一种方法. 我正在循环用户输入，该用户输入将始终具有与模型相同的结构和索引. 然后，我使用来自可很好循环的userinputarray的键和数组形式的旧模型的副本，以确定应使用相应输入数据更新的模型的属性. 这是我的代码当前的样子:

So I'm now using a different approach. I'm looping the userinput, which will ALWAYS have the same structure and indexes as the model. And then I'm using the keys from the very well loopable userinputarray and a copy of the old model in form of an array, to determine the attributes of the model which shall be updated with the respective input data. This is what my code currently looks like:

foreach($input as $key => $value){
  foreach($modelAsArray as $keyOld => $valueOld){ 

  //$keyOld is ALWAYS the same key as the "new" one. 
  //$keyOld was only chosen to both distinguish from the outer loops $key and to 
  //maintain its relationship to $valueOld, which is indeed the old value! ;)

    coretable::with($permittedTables)->where($key, $valueOld)->update([$key => $value]);
  }
}

现在，此代码可很好地用于外部数组.我确实可以通过这种方式更新"Internal_key"字段.但是，一旦我们达到了以嵌套数组"形式表示联接表的地步，事情就往南走了.我收到以下错误消息:

Now, this code works pretty neatly for the outer array. I can indeed update the "Internal_key" field this way. However, once we hit the point where the joined table is represented in form of the "nested array", things go south. I get the following error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'extensiontable_itc' in 'where clause' (SQL: update `coretable` set `extensiontable_itc` = {"description_itc":"UPDATED1"}, `coretable`.`updated_at` = 2020-02-06 16:07:06 where `extensiontable_itc` = UPDATED1)

我可以看出这是从哪里来的.它试图在coretable上找到列extensiontable_itc，因为它是它自己的关系，因此显然不在那儿，仅通过FK连接到coretable.

I can kind of see where this comes from. Its trying to find a column extensiontable_itc on coretable which obviously isnt there since it is its own relation, only connected to the coretable via FK.

我想到的第一件事是通过$ key动态插入其名称来调用另一个模型. 然后，我将不得不遍历嵌套数组的键和值，基本上执行与外部数组相同的操作.

The first thing that comes to my mind to deal with this problem is calling another model by dynamically inserting its name via the $key. Then I would have to loop over the nested arrays keys and values, basically doing the same thing as I did with the outer array.

这可能会消耗相对可怕的资源，但是此软件仅用于内部目的，我们的数据库服务器可能能够处理此负载.我认为它也很骇人^^

This probably will consume a comparatively horrible amount of ressources, but this software is for internal purposes only and our DB-server probably will be able to handle this load. Its also quite hacky, I think^^

那么，还有谁能给我比我更多的解决方案呢?

So, can anyone else give me another, more elegant, less ressource hungry and less hacky solution than mine?

根据注释中的请求，这是我的迁移:

As per request in the comments, here are my migrations:

核心表

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCoretable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('coretable', function (Blueprint $table) {
            $table->bigIncrements('id_coretable');
            $table->string('Internal_key')->nullable(false)->unique();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('coretable');
    }
}

extensiontable_itc

extensiontable_itc

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateExtensiontableItc extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('extensiontable_itc', function (Blueprint $table) {
            $table->bigIncrements('id_extensiontable_itc');
            $table->bigInteger('coretable_id')->unsigned()->unique()->nullable(false);
            $table->foreign('coretable_id', 'fk_extensiontable_itc_coretable')->references('id_coretable')->on('coretable');
            $table->string('description_itc')->nullable(false);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('extensiontable_itc');
    }
}

extensiontable_sysops

extensiontable_sysops

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class ExtensiontableSysops extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('extensiontable_sysops', function (Blueprint $table) {
            $table->bigIncrements('id_extensiontable_sysops');
            $table->bigInteger('coretable_id')->unsigned()->nullable(false)->unique();
            $table->foreign('coretable_id', 'fk_extensiontable_sysops_coretable')->references('id_coretable')->on('coretable');
            $table->string('description_sysops')->nullable(false);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('extensiontable_sysops');
    }
}

推荐答案

SQLSTATE [42S22]:找不到列:1054'where子句'中的未知列'extensiontable_itc'(SQL:update coretable set extensiontable_itc = {"description_itc":"UPDATED1"}，coretable. c5> = 2020-02-06 16:07:06其中extensiontable_itc = UPDATED1)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'extensiontable_itc' in 'where clause' (SQL: update coretable set extensiontable_itc = {"description_itc":"UPDATED1"}, coretable.updated_at = 2020-02-06 16:07:06 where extensiontable_itc = UPDATED1)

您无法通过with('relation')->update()更新关联数据.

You can't update relation data via with('relation')->update().

我将根据您的迁移文件为实现此示例.

I will make an example to implement this, based on your migration files.

核心

namespace App;

use App\ExtensiontableItc;
use App\ExtensiontableSysops;
use Illuminate\Database\Eloquent\Model;

class Core extends Model
{
    protected $table      = 'coretable';
    protected $primaryKey = 'id_coretable';
    protected $fillable   = [
        'id_coretable',
        'Internal_key',
    ];

    public function extensiontable_itc()
    {
        return $this->hasOne(ExtensiontableItc::class, 'coretable_id', 'id_coretable');
    }

    public function extensiontable_sysops()
    {
        return $this->hasOne(ExtensiontableSysops::class, 'coretable_id', 'id_coretable');
    }
}

ExtensiontableItc

namespace App;

use Illuminate\Database\Eloquent\Model;

class ExtensiontableItc extends Model
{
    protected $table      = 'extensiontable_itc';
    protected $primaryKey = 'id_extensiontable_itc';
    protected $fillable   = [
        'coretable_id',
        'description_itc',
    ];
}

ExtensiontableSysops

namespace App;

use Illuminate\Database\Eloquent\Model;

class ExtensiontableSysops extends Model
{
    protected $table      = 'extensiontable_sysops';
    protected $primaryKey = 'id_extensiontable_sysops';
    protected $fillable   = [
        'coretable_id',
        'description_sysops',
    ];
}

---

用法

$permittedTables = ['extensiontable_itc', 'extensiontable_sysops'];
$core            = Core::with($permittedTables)->find(1);

您将获得(基于您的示例)

You will get (based on your example)

array:6 [
  "id_coretable" => 1
  "Internal_key" => "TESTKEY_1"
  "created_at" => "2020-02-07 18:05:50"
  "updated_at" => "2020-02-07 18:05:50"
  "extensiontable_itc" => array:5 [
    "id_extensiontable_itc" => 1
    "coretable_id" => 1
    "description_itc" => "UPDATED1"
    "created_at" => "2020-02-07 18:17:08"
    "updated_at" => "2020-02-07 11:32:44"
  ]
  "extensiontable_sysops" => array:5 [
    "id_extensiontable_sysops" => 1
    "coretable_id" => 1
    "description_sysops" => "UPDATED1"
    "created_at" => "2020-02-07 18:17:21"
    "updated_at" => "2020-02-07 11:32:44"
  ]
]

要更新您的关系，您需要通过Core模型调用extensiontable_itc和extensiontable_sysops急切加载.

To update your relation, you need to call extensiontable_itc and extensiontable_sysops eager load via Core model.

$input = [
    'id_coretable'          => 1,
    'Internal_key'          => 'TESTKEY_1',
    'extensiontable_itc'    => [
        'description_itc' => 'EXTENSION_ITC_1',
    ],
    'extensiontable_sysops' => [
        'description_sysops' => 'EXTENSION_SYSOPS_1',
    ],
];

$permittedTables = ['extensiontable_itc', 'extensiontable_sysops'];
$core            = Core::with($permittedTables)->find(1);

// Update extensiontable_itc
$core->extensiontable_itc->update($input['extensiontable_itc']);

// Update extensiontable_sysops
$core->extensiontable_sysops->update($input['extensiontable_sysops']);

这篇关于如何动态构建此查询-Laravel/Lumen的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！