laravel-cte with SoftDeletes problem

[marcelhoogantink]

Using CTE's withExpression() method when SoftDeletes are active gives some problems. The temporary table created, does not know the 'real-table'.deleted_at. column

At this moment I use the withTrashed() method as a work-around to overcome this.

How should this be handled?

[staudenmeir]

Please share details about your query and the expected vs. actual result.

[marcelhoogantink]

Hi, here is my Laravel code (simplified part):

$lastPriceChangesTable='last_price_changes';
$lastPriceChanges= EanRecordPriceTimeModel
            ::select([
                         EanRecordPriceTimeModel::EAN_RECORD_ID,
                         EanRecordPriceTimeModel::PRICE,
                         EanRecordPriceTimeModel::DATE_PRICE_CHANGED,
                     ])
            ->selectRaw('DENSE_RANK() OVER (PARTITION BY '.EanRecordPriceTimeModel::EAN_RECORD_ID.
                        ' ORDER BY '.EanRecordPriceTimeModel::DATE_PRICE_CHANGED.' DESC) AS MyRank');

$lastPrices= EanRecordPriceTimeModel
            ::select() //
            //::withTrashed() // used because of withExpression()
            ->from($lastPriceChangesTable)
            ->where('MyRank',1)
            ->withExpression($lastPriceChangesTable,$lastPriceChanges);

$sql=$lastPrices->toSql();

this results in this sql:

with 
    `last_price_changes` 
as (
    select 
        `ean_record_id`, 
        `price`, 
        `date_price_changed`, 
        DENSE_RANK() OVER (PARTITION BY ean_record_id ORDER BY date_price_changed DESC) AS MyRank 
    from 
        `ean_record_price_time` 
    where 
        `ean_record_price_time`.`deleted_at` is null
    ) 
select 
    * 
from 
    `last_price_changes` 
where 
    `MyRank` = 1 
        and 
    `ean_record_price_time`.`deleted_at` is null

This results in an Error:

Error Code: 1054. Unknown column 'ean_record_price_time.deleted_at' in 'where clause'

expected is a normal results like:

'5', '199.00', '2021-04-05 16:28:22', '1'
'7', '199.00', '2021-03-25 16:30:18', '1'
'10', '266.11', '2021-10-17 15:20:16', '1'
'15', '284.05', '2021-10-17 15:20:31', '1'

The problem lies in the last (automaticly inserted) [ean_record_price_time.deleted_at is null] because of the activated Soft-Deletes.

I worked around this using ::withTrashed() in stead of ::selected() (see remarks in PHP-code)

Is there a better way doing this?

[staudenmeir]

The issue is that from() sets the table name on the underlying base query, but the SoftDeletes trait uses the Eloquent model's table name for the deleted_at constraint.

Use this instead:

$lastPrices= (new EanRecordPriceTimeModel)->setTable($lastPriceChangesTable)
            ->select()
            ->where('MyRank', 1)
            ->withExpression($lastPriceChangesTable, $lastPriceChanges);

[marcelhoogantink]

Thanks, that did it!!