Laravel Nova & Corcel - many-to-many relation

Question

I have a hard time to acquire proper relation betwen fair_lead and product(corcel), nova try to search for pivot table in wordpress database instead of laravel database.

Laravel: 10.43.0 Nova: 4.23.0 PHP: 8.2.13 Corcel: jgrossi/corcel @ v7.0.0 -> https://github.com/corcel/corcel

My fair lead model:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class FairLead extends Model
{

    public function product(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)->using(FairLeadProduct::class)->withPivot('amount');
    }
}

product model

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Corcel\WooCommerce\Model\Product as Corcel;

class Product extends Corcel
{
    //use HasFactory;

    protected $connection = 'corcel';

    public function fair_lead(): BelongsToMany
    {

        return $this->belongsToMany(FairLead::class)->using(FairLeadProduct::class)->withPivot('amount');
    }


}

my fair_lead_product model:

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class FairLeadProduct extends Pivot
{
    protected $connection = 'mysql';

    protected $table = 'fair_lead_product'; // nova seems to ignore this

}

fields in nova Product resource:

public function fields(NovaRequest $request)
    {
        return [
            ID::make(__('ID'), 'ID')->hideFromIndex(),
            BelongsToMany::make(__('Fair Leads'), 'fair_lead', FairLead::class),
        ];
    }

error i get:

"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'WP_DATABASE_fair_lead_product' doesn't exist (Connection: corcel, SQL: select `WP_DATABASE_posts`.*, 

I need to nova to search for pivot in laravel database not in corcel(wordpress) database for fair_lead_product table.

Displaying Products works well in nova, but I can't get relation work as wanted.

Answer 1

After few hours of digging I tried to apply solutions from: belongsToMany relationship in Laravel across multiple databases now my code looks like: FairLead.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class FairLead extends Model
{
    use HasFactory;

    //protected $table = 'mysql.fair_lead';

    public function fair(): BelongsTo
    {
        return $this->belongsTo(Fair::class);
    }

    public function client(): BelongsTo
    {
        return $this->belongsTo(Client::class);
    }

    public function product(): BelongsToMany
    {
        $database = $this->getConnection()->getDatabaseName();

        if (is_file($database)) {
            $connection = app('B')->getConnection()->getName();
            $name = $this->getConnection()->getName();
            \Illuminate\Support\Facades\DB::connection($connection)->statement("ATTACH DATABASE '$database' AS $name");
            $database = $name;
        }
        return $this->belongsToMany(Product::class, "$database.fair_lead_products", 'fair_lead_id', 'product_ID')->using(FairLeadProduct::class)->withPivot('amount');
    }
}

my product.php

<?php

namespace App\Models;

use Corcel\WooCommerce\Model\Product as Corcel;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Product extends Corcel
{

    protected $connection = 'corcel';

    public function fair_lead(): BelongsToMany
    {
        $database = $this->getConnection()->getDatabaseName();
        if (is_file($database)) {
            $connection = app('B')->getConnection()->getName();
            $name = $this->getConnection()->getName();
            \Illuminate\Support\Facades\DB::connection($connection)->statement("ATTACH DATABASE '$database' AS $name");
            $database = $name;
        }
        return $this->belongsToMany(FairLead::class, "$database.fair_lead_products", 'product_ID', 'fair_lead_id')->using(FairLeadProduct::class)->withPivot('amount');
    }
}

Now it seems well pick databases but WP_DB_PREFIX (from corcel) seems to still apply and even twice, how to null it inside public function or bypass ?

 "message": "SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'WP_USER'@'localhost' for table `WP_DB_PREFIX_WP_DB_PREFIX_LARAVEL_DB`.`fair_lead_products` (Connection: corcel, SQL: select * from `WP_DB_PREFIX_posts` where `post_type` = product and not exists (select `product_ID` from `WP_DB_PREFIX_WP_DB_PREFIX_LARAVEL_DB`.`fair_lead_products` where `WP_DB_PREFIX_LARAVEL_DB`.`fair_lead_products`.`fair_lead_id` = 2 and `WP_DB_PREFIX_posts`.`ID` = `WP_DB_PREFIX_LARAVEL_DB`.`fair_lead_products`.`product_ID`) order by `WP_DB_PREFIX_posts`.`ID` desc)",