Question 220330 in PHP Versions Question posted in
You can find a list of all versions here.

Php versions – laravel set deleted_at has problem with subqueries

first things first I have repositories and services in my laravel app.

Main Problem

My main problem is laravel is not compatible with sub-queries and not support them well!

  • I use below versions:

    • laravel v10.16.1
    • php 8.1

  • I have deleted_at, updated_at and created_at columns

Problem with deleted_at

If I use soft delete trait and want deleted_at to handle automatically, when I use sub queries like below, it’ll show me error:

class UserRepository {
    ...

    public function getWhere(array $columns, Builder $where)
    {
        $query = $this->model
            ->newQuery()
            ->when($where, function (Builder $query) use ($where) {
                $query->fromSub($where, 'sub');
            });

        return $query->get($columns);
    }

    ...
}

ERROR: `'deleted_at' column does not exists`

Problematic query Example

SELECT * FROM (SELECT * FROM `users` WHERE `name`='john') AS `sub` WHERE `users`.`deleted_at` IS NULL

In above query, deleted_at is not exist but it exists in database and this is because of sub-query alias.

I search about it and it seems to be laravel issue some how and finally I removed automatic soft deletes and do it manually like below:

class UserRepository {
    ...
    
    public function getWhere(array $columns, Builder $where)
    {
        $prefix = $this->model->getTable();
        $query = $this->model
            ->newQuery()
            ->when($where, function (Builder $query) use ($where, &$prefix) {
                $prefix = 'sub';
                $query->fromSub($where, $prefix);
            });

        $query->whereNull($prefix . '.deleted_at');

        return $query->get($columns);
    }

    ...
}

What Should I do?

(I hope my explanation is understandable)

Thanks in advanced.

EDIT

I changed my example because I was doing wrong for update operation and updated_at is not problematic in my case but sub-query and set deleted_at has problem.

Tags:

2

Answers


  1. 0

    I don’t quite understand what you are trying to do, but I think I can help you

    you can just try this

    $query = yourModelName::where('name' , 'john')->get();

    to make this

    SELECT * FROM (SELECT * FROM `users` WHERE `name`='john') AS `sub` WHERE `users`.`deleted_at` IS NULL

    If you are trying to bind a foreign key to a primary in laravel try this

    add this to "users" model

    public function post(){
    return $this->hasMany(Post::class);
}

    and add this to other model "posts"

    public function user(){
    return $this->belongsTo(User::class);
}

    then you can get any things you whant from 2 table like this

    $post[0]->title //title of post table
$post[0]->user->name //name of user table
    Login or Signup to reply.
  2. 0

    Okay, usually when you create a Schema with $table->softDeletes(); it will automatically create the deleted_at column in the table. So I’m not sure why you get deleted_at doesn’t exist.

    An example of a schema is like this.

    public function up()
{
    Schema::create('able_name', function (Blueprint $table) {
        $table->id();
        $table->softDeletes();  // This adds the deleted_at column
    });
}

    After this, you have to run the migration. php artisan migrate if the first time, to drop all and re-run php artisan migrate:fresh

    As well, when use use use SoftDeletes; it will automatically add global scope to all queries, like where deleted_at is null.

    If you use this in the given order, this will work like a piece of cake. Nothing goes wrong and simple as that

    To fix your issue in an alternative way, you can add withoutTrashed() to your query.

    public function getWhere(array $columns, Builder $where)
{
    $query = $this->model
        ->newQuery()
        ->withoutTrashed()
        ->when($where, function (Builder $query) use ($where) {
            $query->fromSub($where, 'sub');
        });

    return $query->get($columns);
}

    But consider my first suggestion, Because it’s the proper way to handle it and you don’t have to put extra effort into this.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search