Defining a relationship for a polymorphic pivot table

Question

I have these models:

• Offer
• OfferReport
• Vendor1Report
• Vendor2Report
• Vendor3Report

Offer table definition:

Schema::create('offers', function (Blueprint $table) {
    $table->id();
    $table->string('slug')->unique();
    $table->string('name')->nullable()->default(null);
    $table->timestamps();
});

OfferReport table definition:

Schema::create('offer_reports', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Offer::class)->constrained();
    $table->unsignedInteger('visitors_count')->default(0);
    $table->unsignedInteger('customers_count')->default(0);
    $table->unsignedInteger('sales_count')->default(0);
    $table->unsignedInteger('sales_amount')->default(0);
    $table->timestamp('starts_at')->nullable();
    $table->timestamp('ends_at')->nullable();
    $table->timestamps();
    $table->unique(['offer_id', 'starts_at', 'ends_at'], 'offer_report_range_unique');
});

Each of the Vendor*Report tables have the following general structure which varies depending the vendor:

Schema::create('vendor1_reports', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(Offer::class)->constrained();

    // Column names are variable depending on vendor, but have some correlate on the OfferReport model.

    $table->timestamp('starts_at')->nullable();
    $table->timestamp('ends_at')->nullable();
    $table->timestamps();
    $table->unique(['offer_id', 'starts_at', 'ends_at'], 'offer_report_range_unique');
});

This is the OfferReportSource pivot:

class OfferReportSource extends MorphPivot
{
    use HasFactory;

    protected $table = 'offer_report_sources';

    public function getMorphClass(): string
    {
        return 'offer_report_sources';
    }

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

    public function source(): MorphTo
    {
        return $this->morphTo();
    }
}

This is the migration for that pivot:

Schema::create('offer_report_sources', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(OfferReport::class)->constrained();
    $table->morphs('source'); // Vendor1Report, Vendor2Report, etc.
    $table->timestamps();
});

I tried creating this relationship on the OfferReport model:

public function sources(): MorphToMany
{
    return $this->morphToMany(
        OfferReportSource::class,
        'source',
        'offer_report_sources',
        'offer_report_id',
        'source_id'
    )->using(OfferReportSource::class);
}

When I try to aggregate, I am using this query to check if a particular vendor report is already associated with the combined OfferReport for the particular date range:

OfferReport::where('offer_id', $vendorReport->offer_id)
    ->where('starts_at', '>=', $vendorReport->starts_at->startOfDay())
    ->where('ends_at', '<=', $vendorReport->ends_at->endOfDay())
    ->whereHas('sources', function (Builder $query) use ($vendorReport) {
        $query->where('source_type', $vendorReport->getMorphClass())
            ->where('source_id', $vendorReport->id);
    })
    ->firstOrNew();

This always causes the following error:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'offer_report_sources' (Connection: mysql, SQL: select * from `offer_reports` where `offer_id` = 31 and `starts_at` >= 2023-03-31 00:00:00 and `ends_at` <= 2023-03-31 23:59:59 and exists (select * from `offer_report_sources` inner join `offer_report_sources` on `offer_report_sources`.`id` = `offer_report_sources`.`source_id` where `offer_reports`.`id` = `offer_report_sources`.`offer_report_id` and `offer_report_sources`.`source_type` = offer_reports and `source_type` = vendor1_reports and `source_id` = 1) limit 1)

If this is a new record, I compile all the data in the specific way to each vendor, save the entry, and then I try to attach the vendor report to the new OfferReport:

$offerReport->sources()->attach($vendorReport);

If I try to do the attachment above (assuming I just skipped the broken whereHas part of the firstOrNew check then I get this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`webseeds`.`offer_report_sources`, CONSTRAINT `offer_report_sources_offer_report_id_foreign` FOREIGN KEY (`offer_report_id`) REFERENCES `offer_reports` (`id`)) (Connection: mysql, SQL: insert into `offer_report_sources` (`offer_report_id`, `source_id`, `source_type`) values (2, 10, offer_report_sources))

Obviously offer_report_sources is showing up in the wrong places according to the query errors, but I can't seem to figure out how to structure my polymorphic pivot relationship method source() to handle these variable table names that could be referenced as part of the polymorph.

Answer 1

You can define hasMany relationship offerReportSources and then get source from it

As your table structure, you can't define eloquent relationship sources in the model OfferReport.

In the OfferReport, you can use morphedByMany for vendor1Report, Vendor2Report, Vendor3Report, such as:

/**
  * Get all of the vendor1Report that are assigned this tag.
  */
 public function vendor1Report(): MorphToMany
 {
    return $this->morphedByMany(vendor1Report::class, 'source');
 }

Reference: https://laravel.com/docs/10.x/eloquent-relationships#many-to-many-polymorphic-relations