markss
1 month ago
307
0
Nova

Eloquent / Nova - Change Default orderBy

Posted 1 month ago by markss

Hey

I have this relation method, which applies a union to a relation, which is again required for a localization feature. My question is not about the union itself as it works fine, but about SQL errors caused by default sorting.

/**
 * Get all payment methods for this operator
 */
function paymentMethods() {

//... some logic filling the $fields with null values for the union
//... some logic finding $res existing relations

// Morph To Payment Method
return $this->morphToMany('App\PaymentMethod', 'payable')
            ->withPivot(Payables::PIVOT_FIELDS)
            ->using(Payables::class)
            ->where(function ($q) {
                if (isset($this->paymentMethods_checkboxes)) {
	            // remove payment methods that are defined absent for this localization
                    $q->whereIn('payment_methods.id', $this->paymentMethods_en)
            })
	    // append payment methods that are defined present for this localization
            ->union(function ($q) use ($fields, $res) {
                $q->select($fields)
                    ->from('payment_methods')
                    // append additional ids
                    ->whereIn('payment_methods.id', $this->paymentMethods_en ?? array())
		    // but avoid appending existing relations as duplicates
                    ->whereNotIn('payment_methods.id', $res);
            });
}

Due to the union I get the following error when viewing the models detail view: Syntax error or access violation: 1250 Table 'payment_methods' from one of the SELECTs cannot be used in ORDER clause [... long sql statement...] ) order by `payment_methods`.`id` desc

According to SQL docs on https://dev.mysql.com/doc/refman/8.0/en/union.html

This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY. (Alternatively, refer to the column in the ORDER BY using its column position. However, use of column positions is deprecated.)

So it is not allowed to set the payment_methods. prefix on a union.

When changing the orderBy in the relation method like ->select(payment_methods.id as sort_id)->orderBy(sort_id) the order by clause only gets appended as additional clause.

When changing it like this: ->select(payment_methods.id as sort_id)->orderBy(payment_methods.sort) the default orderBy gets overwritten, but due to the table-prefix the statement is invalid again.

Any suggestions how to remove the default order clause?!

Appreciate your help!

Please sign in or create an account to participate in this conversation.