Nova adding duplicate aliased tables to query

I'm generating a query from a selection in Nova which is used with Laravel-Nova-Excel in an action to generate a CSV export. I'm not sure whether this problem is coming from Nova itself or that package, but Nova has had this bug in the past which is almost identical.

My query method, which is based on a Scan resource:

public function query()
    {
        return $this->query
            ->join('locations', 'locations.id', '=', 'scans.location_id')
            ->leftJoin('customers', 'locations.customer_id', '=', 'customers.id')
            ->join('users', 'users.id', '=', 'scans.user_id')
            ->leftJoin('product_scan', 'scans.id', '=', 'product_scan.scan_id')
            ->leftJoin('products', 'products.id', '=', 'product_scan.product_id')
            ->leftJoin('skus', 'products.sku_id', '=', 'skus.id')
            ->leftJoin('borrowings', 'borrowings.scan_id', '=', 'scans.id')
            ->select(
                [
                    'scans.uuid',
                    'scans.status',
                    'scans.created_at',
                    'scans.external_id',
                    'users.email as user_email',
                    'customers.name as customer_name',
                    'locations.name as location_name',
                    'locations.uuid as location_uuid',
                    'skus.brand as sku_brand',
                    'skus.name as sku_name',
                    'skus.type as sku_type',
                    'products.serial as product_serial',
                    'borrowings.status as borrowing_status',
                    'borrowings.sku_type as borrowing_sku_type',
                ]
            );
    }

This generates an error:

Syntax error or access violation: 1066 Not unique table/alias: 'locations' (
Connection: mysql, SQL: select `scans`.`uuid`, `scans`.`status`, `scans`.`created_at`, `scans`.`external_id`, `users`.`email` as
 `user_email`, `customers`.`name` as `customer_name`, `locations`.`name` as `location_name`, `locations`.`uuid` as `location_uui
d`, `skus`.`brand` as `sku_brand`, `skus`.`name` as `sku_name`, `skus`.`type` as `sku_type`, `products`.`serial` as `product_ser
ial`, `borrowings`.`status` as `borrowing_status`, `borrowings`.`sku_type` as `borrowing_sku_type` from `scans` inner join `loca
tions` on `locations`.`id` = `scans`.`location_id` left join `customers` on `locations`.`customer_id` = `customers`.`id` inner j
oin `users` on `users`.`id` = `scans`.`user_id` left join `product_scan` on `scans`.`id` = `product_scan`.`scan_id` left join `p
roducts` on `products`.`id` = `product_scan`.`product_id` left join `skus` on `products`.`sku_id` = `skus`.`id` left join `borro
wings` on `borrowings`.`scan_id` = `scans`.`id` inner join `locations` on `locations`.`id` = `scans`.`location_id` left join `cu
stomers` on `locations`.`customer_id` = `customers`.`id` inner join `users` on `users`.`id` = `scans`.`user_id` left join `produ
ct_scan` on `scans`.`id` = `product_scan`.`scan_id` left join `products` on `products`.`id` = `product_scan`.`product_id` left j
oin `skus` on `products`.`sku_id` = `skus`.`id` left join `borrowings` on `borrowings`.`scan_id` = `scans`.`id` where `scans`.`i
d` in...

Cleaned up, the full set of joins is:

from `scans`
         inner join `locations` on `locations`.`id` = `scans`.`location_id`
         left join `customers` on `locations`.`customer_id` = `customers`.`id`
         inner join `users` on `users`.`id` = `scans`.`user_id`
         left join `product_scan` on `scans`.`id` = `product_scan`.`scan_id`
         left join `products` on `products`.`id` = `product_scan`.`product_id`
         left join `skus` on `products`.`sku_id` = `skus`.`id`
         left join `borrowings` on `borrowings`.`scan_id` = `scans`.`id`
         inner join `locations` on `locations`.`id` = `scans`.`location_id`
         left join `customers` on `locations`.`customer_id` = `customers`.`id`
         inner join `users` on `users`.`id` = `scans`.`user_id`
         left join `product_scan` on `scans`.`id` = `product_scan`.`scan_id`
         left join `products` on `products`.`id` = `product_scan`.`product_id`
         left join `skus` on `products`.`sku_id` = `skus`.`id`
         left join `borrowings` on `borrowings`.`scan_id` = `scans`.`id`

So while you can see there are no duplicate tables in my query builder code, there are duplicate tables in the generated query. If I replace any table name with an alias, for example:

->join('locations as locations2', 'locations2.id', '=', 'scans.location_id')

I get the same error on the new name.

This looks like a bug to me – I don't know how I would provoke this behaviour intentionally. What do you think?

LaryAI

2 years ago

Level 58

It seems like the issue you're encountering is related to the query being duplicated by the Laravel-Nova-Excel package when it's preparing the query for export. This could be a bug in the package or an unintended side effect of how the package processes the query.

To work around this issue, you can try to ensure that your query is only being built once. Here's a potential solution:

Make sure you're not calling the query() method multiple times inadvertently.
If you're using any scopes or additional query modifications in your Nova resource, ensure they are not causing the duplication.
You can try to override the actions() method in your Nova resource to ensure that the query is only being modified once.

Here's an example of how you might override the actions() method:

/**
 * Get the actions available for the resource.
 *
 * @param  \Illuminate\Http\Request  $request
 * @return array
 */
public function actions(Request $request)
{
    return [
        (new Actions\ExportToExcel)
            ->withHeadings()
            ->withFilename('export.csv')
            ->withWriterType(\Maatwebsite\Excel\Excel::CSV)
            ->onlyOnIndex()
            ->canSee(function ($request) {
                return true;
            })
            ->canRun(function ($request, $user) {
                return true;
            }),
    ];
}

In the above code, ExportToExcel is a hypothetical action that you would have created for exporting your data. You would need to ensure that this action uses the query without duplicating the joins.

If the issue persists, you might want to open an issue on the Laravel-Nova-Excel repository with a detailed explanation of the problem and the steps to reproduce it. The maintainers of the package may be able to provide a more specific solution or fix the bug if it's on their end.

Lastly, ensure that you're using the latest versions of Laravel Nova and Laravel-Nova-Excel, as the issue might have been fixed in a more recent release.

Synchro

1 year ago

Level 2

I discovered the cause and a workaround for this problem.

It's really a design bug in Laravel-Excel, but I managed to work around it by adding a check to see whether query() had been called before, thus avoiding the duplicate joins. More info in here, but the short version is to add this at the top of your query method:

static $done = false;
if ($done) {
    return $this->query;
}
$done = true;
//Now carry on making your modifications to $query

Please or to participate in this conversation.