Harmonisation of MorphToMany relationship query building

[stephaneisenbach]

I had trouble with the union of two MorphToMany queries.
I need to set up a method (MorphToMany relation) that should return the related models in a bidirectional way, that is, the method should return the MorphToMany relations as well as the MorphedByMany relations, which are actually just inverse MorphToMany relations.
This should be possible in my case because the related models are of the same class.
Table Structure

contacts
	id (uuid)
	props
	
contactables
	id
	contact_id
	contactable_type
	contactable_id
	type (type of relation (string/enum) e.g. "family", "work", "test")

I have set up a method Contact->relatedTestContacts() in order to get a collection of contacts in a relationship of the given type 'test' (bidirectional). The contactables.type attribute is used as a pivot field to distinguish between different types of relationship.
This method is used in a Laravel Nova installation ("laravel/framework": "^10.10", "laravel/nova": "^4.0") for a Laravel\Nova\Fields\MorphToMany Field as described here: https://nova.laravel.com/docs/resources/relationships.html#morphtomany.

The method looked like this:

// App\Models\Contact
public function relatedTestContacts(): MorphToMany
{
    $first = $this->morphToMany(Contact::class, 'contactable')
        ->withPivot('type')
        ->wherePivot('type', 'test');

    $second = $this->morphedByMany(Contact::class, 'contactable')
        ->withPivot('type')
        ->wherePivot('type', 'test');

    return $first->union($second)->beforeQuery(function (\Illuminate\Database\Query\Builder $builder) {
        // Avoid order by qualified column name in union select
        // ERROR: SQLSTATE[42000]: Syntax error or access violation: 1250 Table 'contacts' from one of the SELECTs cannot be used in ORDER clause
        $builder->reorder('id');
    });
}

This has resulted in the following error.

ERROR: SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns

(select `contacts`.*,
        `contactables`.`contactable_id`   as `pivot_contactable_id`,
        `contactables`.`contact_id`       as `pivot_contact_id`,
        `contactables`.`contactable_type` as `pivot_contactable_type`,
        `contactables`.`type`             as `pivot_type`
 from `contacts`
          inner join `contactables` on `contacts`.`id` = `contactables`.`contact_id`
 where `contactables`.`contactable_id` = '06bb979f-94fd-4f83-b47b-375160bcd085'
   and `contactables`.`contactable_type` = App\Models\Contact and `contactables`.`type` = test)
union
(select *
 from `contacts`
          inner join `contactables` on `contacts`.`id` = `contactables`.`contactable_id`
 where `contactables`.`contact_id` = '06bb979f-94fd-4f83-b47b-375160bcd085'
   and `contactables`.`contactable_type` = App\Models\Contact and `contactables`.`type` = test)
order by `id` asc
limit 6 offset 0

The selected columns of the two MorphToMany relations differ, which (in my opinion) should be identical. The second select uses only '*' for all columns.
My dirty workaround:

// App\Models\Contact
public function relatedTestContacts(): MorphToMany
{
    $first = $this->morphToMany(Contact::class, 'contactable')
        ->withPivot('type')
        ->wherePivot('type', 'test');

    $select = [
        $this->getTable() . '.*',
        // Pivot
        "contactables.contactable_id as pivot.contactable_id",
        "contactables.contact_id as pivot.contact_id",
        "contactables.contactables as pivot.contactables",
        // Additional Pivot Attributes
        "contactables.type as pivot.type",
    ];

    $second = $this->morphedByMany(Contact::class, 'contactable')
        ->withPivot('type')
        ->wherePivot('type', 'test')
        ->select($select);

    return $first->union($second)->beforeQuery(function (\Illuminate\Database\Query\Builder $builder) {
        // Avoid order by qualified column name in union select
        // SQLSTATE[42000]: Syntax error or access violation: 1250 Table 'contacts' from one of the SELECTs cannot be used in ORDER clause
        $builder->reorder('id');
    });
}

Idea: Harmonise the building of select queries at Builder level so that both select the same columns. This would make it possible to union selects of the same class.