php - 外部服务器上的关系表上的 Laravel where() 和 orderBy()

Question

我需要使用 Laravel where() 和 orderBy() 方法。问题是我需要过滤和排序的数据来自外部服务器/另一个主机数据库 (PostgreSQL) 上的关系。

我尝试过的:

public function index(Request $request)
    {
        $productforms = $this->productform
            ->select('product_forms.*')
            ->with('product')
            ->join('types', 'product_forms.type_id', '=', 'types.id')
            ->where('product.description', 'ILIKE', '%sugar%');
            // ...
    }

也试过了:

$rawquery = DB::table('product_forms')
    ->join('testing.base.products as db2','product_forms.product_id','=','db2.id')
    ->select(DB::raw('form_title as title'))
    ->first();

我搜索了很多主题，但没有找到完全相似的内容。

以上第二次尝试的 Laravel 错误消息:

"""
SQLSTATE[0A000]: Feature not supported: 7 ERROR:  cross-database references are not implemented: "testing.base.product"
LINE 1: ...m_title as titulo from "product_forms" inner join "test...
                                                             ^ (SQL: select form_title as titulo from "product_forms" inner join "testing"."base"."product ▶
"""

上述第一次尝试的 Laravel 错误消息:

"""
SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table "products"
LINE 1: ...on "product_forms"."type_id" = "types"."id" where "products"...
                                                             ^ (SQL: select "product_forms".* from "product_forms" inner join "types" on "product_forms"."type_id" = "types"."id" where "products"."description"::text ILIKE %sugar%) ◀
"""

本地服务器模型productForm:

class ProductForm extends Model implements Auditable
{

    protected $table='product_forms';

    protected $connection = 'localserver';

    protected $fillable = [
        'id', 
        'product_id', 
        'type_id'
        // ...
    ];

    public function product(){
        return $this->belongsTo(Product::class);
    }

}

外部服务器模型产品:

class Product extends Model
{

    protected $connection='externalserver';

    protected $table='base.products';

    protected $fillable = [
       'id',
       'description',
       'attributes'
       // ...
    ];
    // ...

}

示例环境结构:

DB_CONNECTION=localserver
DB_HOST_PG=127.0.0.1
DB_PORT_PG=5432

DB_CONNECTIONK=externalserver
DB_HOST_PGK=externaldomain.com
DB_PORT_PGK=5432

Conf/database.php 连接:

'connections' => [

    'localserver' => [
        'read' => [
            'host' => env('DB_HOST_PG', '127.0.0.1'),
        ],
        'write' => [
            'host' => env('DB_HOST_PG_WRITE', '127.0.0.1'),
        ],
        'driver' => 'pgsql',
        'port' => env('DB_PORT_PG', '5432'),
        // ...
    ],

    'externalserver' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST_PGK', 'externaldomain.com'),
        'port' => env('DB_PORT_PGK', '5432'),
        // ...
    ]

]

谢谢!任何提示都会帮助我

Answer 1

我认为这不是更好的解决方案，但暂时我找到了解决方法。我的目标是使用 where() 为列表创建搜索过滤器。我所做的解决方法是:

在模型上，使用条件局部作用域:

public function scopeSearchField($query, $search) {

        // retrieving the external cross-database data using where() on a closure inside eager loading/with() method

        if (!empty($search['product']) ) {
            $query->with(
                    [
                        'product' =>
                            function ($query) use($search){
                                $query->whereRaw(" public.unaccent('description')::text ILIKE public.unaccent('%{$search["product"]}%')::text");
                            }
                    ]);
        }

    }

在 Controller 上:

$productforms = $this->productform
    ->select('product_forms.*')
    ->searchField($request->all());

在 View 上:

@forelse($productforms as $productform)
                @if($productform->product!= null)

                <tr>
                    <td>$productforms->title</td>
                    <td>$productforms->product->description</td>
                    <td>$productforms->...</td>
                    <td>$productforms->...</td>
                </tr>

                @endif
@endforelse