CODEWITHSUNDEEP
phplaravellaravel-10
MMDM
MMDM

Reputation: 465

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!

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);
    }

    ...
}

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.

Upvotes: 1

Views: 1118

Answers (2)

JUBA 420
JUBA 420

Reputation: 41

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 want from 2 table like this

$post[0]->title //title of post table
$post[0]->user->name //name of user table

Upvotes: -1

Abdulla Nilam
Abdulla Nilam

Reputation: 38652

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.

Upvotes: 1

Related Questions