Eloquent Union - SQL error "column 'id' already exists"

Question

I have defined a Relation between several Shop-Types and PaymentMethods representing which shop offers which payment method. This is defined through a morphToMany. So when calling the $shop->paymentMethods() it will return all active payment methods.

Now I also have a Localization-Feature implemented, which works the following way: the current locale you're working in is stored in the session. Then, there is a localizations table, which stores the localized elements (in this case: the modified shop model), as the different countries will again provide different payment methods for the same shop. This localization then also stores an array of attached IDs when the relation is modified while in localization view, so that payment methods can be added and removed while in the sub-view. In this example, they're stored in $shop->paymentMethods_en

Now its easy to exclude relations that exist in the root view by calling the sub-query ->whereIn('payment_methods.id', $this->paymentMethods_en).

What's causing problems is showing additional payment methods that don't exist in root and therefore also don't exist in the relations-table. I tried using a union and then left outer join the relations table, which excludes all elements that are already defined in the root dataset. Otherwise the ID 1 for example would get loaded twice. And then, all remaining payment method ids are appended to the output.

So far, soo good. Actually, the table-view loads as expected (laravel nova), showing the additional payment methods as well, but when nova is trying to count the existing models for the table pagination, I get the error: Column already exists (1060): duplicate column name 'id'

Shop.php

function paymentMethods() {

  $fields = array_map(function ($el) {
      return 'name as pivot_' . $el;
  }, Payables::PIVOT_FIELDS);

  return $this->morphToMany('App\PaymentMethod', 'payable')
    ->withPivot(Payables::PIVOT_FIELDS)
    ->using(Payables::class)
    ->where(function ($q) {
        if (isset($this->paymentMethods_en)) {
            $q->whereIn('payment_methods.id', $this->paymentMethods_en);
        }
    })
    ->unionAll(function ($q) use ($fields) {
        $q->select($fields)
            ->from('payment_methods')->join('payables', 'payment_methods.id', '=', 'payables.id', 'left outer')
            ->where('payables.payable_id', '=', $this->id)
            ->where('payables.payable_type', '=', self::class)
            ->whereIn('payment_methods.id', $this->paymentMethods_en);
    });
}

According to SQL UNION of two queries, duplicate column name error I would have to explicitly name the columns, which is not possible using the eloquent methods. Any ideas how to work around that problem - or a different approach on how to attach the missing relations?

Quite frustrating to see this unanswered. I'm using eloquent query builder with union like you, with a nice addition of [`fromSub`](https://laravel.com/api/7.x/Illuminate/Database/Query/Builder.html#method_fromSub) which further complicates things! — om-ha, Mar 09 '21 at 20:41
As per your question specifics, what do you mean you an't rename the columns? clearly you can like [this](https://stackoverflow.com/q/26958080/10830091) (i.e. alias a column). But then again in your case, you are selecting `$fields` in a programmatic way, not as a String parameter like this: `$q->select("payment_method_id as aliased_id")` — om-ha, Mar 09 '21 at 20:50

Eloquent Union - SQL error "column 'id' already exists"

0 Answers0