2

It seems there are lot of posts about this error, but before everything, I must say that I have use SoftDeletes; because I use it and I need it to manage trashed models.

Column 'deleted_at' also exists.

Solution like this saying to remove it does not suit in my case.

I am looking for help to keep use SoftDeletes; but making my request working properly.

Here is my query:

public function scopeWithRowNumber($query)
{
    $sub = Model::selectRaw('*, @row:=@row+1 as row')
        ->orderBy('some_date', 'desc')->toSql();

    $query->from(DB::raw("({$sub}) as sub"));
}

The sql exit gives me :

select *, @row:=@row+1 as row from `model_table` 
where `model_table`.`deleted_at` is null order by `entry_date` desc

How to fix it?

Cutis
  • 949
  • 1
  • 13
  • 32
  • 3
    I don't know what to tell you... Your error says that `deleted_at` does not exist. Show your migration(s) for this Model; you should have `$table->softDeletes()` somewhere, make sure you've actually run the migration(s) (i.e. `SELECT * FROM migrations` and ensure the migration(s) are shown there), then finally run `DESCRIBE model_table` and ensure `deleted_at` is displayed in the list of columns. – Tim Lewis Dec 21 '22 at 20:26
  • Firstly, do you really need such complex queries? What are you trying to achieve? Second, what is your `$query`? – Ramūnas Pabrėža Dec 21 '22 at 20:36
  • @RamūnasPabrėža yes I need this complex query. This legacy project is using mysql 5.7... so I cannot use row_number() (mysql 8 function), which would be more easy. So you guess what I want to do when I say row_number . – Cutis Dec 21 '22 at 20:43
  • Where you will use that `row`? Perhaps you are iterating results and use PHP variables for this `++$key`? And you didn't answer a question, what is in `$query` variable? Is it a model object? – Ramūnas Pabrėža Dec 21 '22 at 20:47
  • @TimLewis Thanks for your answer. I check and I cannot find ->softDeletes() in this table migrations. I don't know why yet. It is a legacy project... But when I run command to describe the table, I can see column deleted_at. – Cutis Dec 21 '22 at 20:47
  • @RamūnasPabrėža it is a scope definition in laravel. I have updated my post. I use this scope in another simple function to get the row which is the ranking number. nothing else. – Cutis Dec 21 '22 at 20:51
  • 1
    Hmm, interesting. It might have been added manually, like `$table->datetime('deleted_at')` (or similar, `->timestamp('deleted_at')`, etc.). `->softDeletes()` is just a helper method that does that. Or, it's possible it predates migrations; I have a project that cannot be fully rolled back due to some of the tables being created without migrations. I'm curious now, if you run `SELECT * FROM model_table WHERE deleted_at IS NOT NULL` (or similar) directly in your SQL editor, do you get the same error? It's possible whatever you're doing with this Scope is messing with the table/columns. – Tim Lewis Dec 21 '22 at 20:56
  • So you have to look into your final query where you are using `ModelX::withRowNumber()->get()`. – Ramūnas Pabrėža Dec 21 '22 at 20:56
  • 1
    @TimLewis, in mysql, everything is ok. I can even use count(*) on "deleted_at is null" condition. no issue with that. – Cutis Dec 21 '22 at 20:59
  • 1
    Seems it is an issue : https://github.com/lazychaser/laravel-nestedset/issues/115. I see in laravel 5. I am using laravel 8 right now, so maybe is the bug still there? I have no clue how to deal with it – Cutis Dec 21 '22 at 21:13

1 Answers1

0

I finally use this:

withoutGlobalScope('Illuminate\Database\Eloquent\SoftDeletingScope')

on my queries to avoid checking deleted_at column.

The issue is relative to nested queries and seems "normal". In my case, this helps me to fix/avoid error:

> Unknown column 'deleted_at' or column not found.

Maybe it will help someone else.

Also for ranking, I was inspired by this post. But I have to edit my query a little bit because of more conditions with dynamic parameters I added. But this is another subject.

Cutis
  • 949
  • 1
  • 13
  • 32