How to filter records when using softdelete (withTrashed)

you can refactor this into a single query and then chain on different conditions if your inputs demand it.

Also look into the ->when query parameter

What are you currently getting and what is your expected result?

Also to avoid

if else

you can use

when

function.

Could try something like this. I haven't tried this code so might not work but you get the general idea

return $this->newQuery()
            ->when($withTrashed, function ($q){
                $q->withTrashed();
            })
            ->when($exceptId, function($q) use ($exceptId) {
                $q->where('parent_id', '!=', $exceptId)->where('id', '!=', $exceptId);
            })
            ->when(is_null($exceptId), function ($q) {
                $q->with([
                    'parent' => function ($parentQ) {
                        $parentQ->withTrashed();
                    }
                ]);
            })
            ->get();

@matt_panton There're syntax errors

@hidayat3676 I've 3 records in categories table as

| id | category_name | parent_id | deleted_at          |
|----|---------------|-----------|---------------------|
| 13 | CPU           | NULL      | 2020-03-27 06:36:14 |
| 14 | Motherboard   | 13        | NULL                |
| 15 | RAM           | 13        | NULL                |

And when I call this function by passing arguments as (15, true), it should return records 13 and 14 but it returns 14 and 15

I've updated my initial comment

Can you show calling code from where you call this method also did you use

use SoftDeletes;

in your model?

@hidayat3676 here is a controller method from where I'm calling this function

public function edit($id){
        $data = $this->category->getCategoryById($id, true);

        if ($data['status']){
            $data['categories'] = $this->category->getCategories($id, true);
            return view('categories.edit', $data);
        }

        return redirect()->back();
    }

And yes, I'm using softDelete trait in my model

@matt_panton It gives me the same result

Are you sure id is 15 also remove

get()

and use toSql()

and post the sql query here.

@hidayat3676 Yes, I dd $expectId in method to verify and it was 15. here is toSql() query

"select * from `categories` where `id` != ? and `parent_id` != ?"

directly execute this in phpmyadmin by passing 15 id and check the result

@hidayat3676 it gives me only record 14. I think it's not considering NULL in parent id

did you pass parent_id also ?

Yes Null is not consider you can use query below to get the desired result

SELECT * FROM `categories` WHERE `id` != 15 AND `parent_id` != 15 OR `parent_id` IS NULL;

Hope this solve your issue.

@hidayat3676 Yes, I tried this and it gives me results I was expecting

select * from `categories` where `id` != 15 and (parent_id is null or parent_id != 15)

how can I achieve this in Laravel ((parent_id is null or parent_id != 15) this part?

OK, the actual problem was with NULL and I modify @matt_panton 's solution a little bit and this worked for me

public function getCategories($exceptId = null, $withTrashed = false) {
        $categories = $this->newQuery()
            ->when($withTrashed, function ($query){
                $query->withTrashed();
            })
            ->when($exceptId, function ($query) use ($exceptId){
                $query->where('id', '!=', $exceptId)->where(function ($q) use ($exceptId){
                    $q->where('parent_id', '!=', $exceptId)->orWhere('parent_id', null);
                });
            })
            ->when(is_null($exceptId), function ($query){
                $query->with([
                    'parent' => function ($parentQ) {
                        $parentQ->withTrashed();
                    }
                ]);
            })
            ->get();
        
        return $categories;
    }

Try

	$query = Category::newQuery();

	if($withTrashed) {
		$query->withTrashed();
	}

	if($exceptId) {

		$query->where(function($q) use($exceptId) {

			$q->where('id','!=',$exceptId)
				->orWhere('parent_id','!=',$exceptId);
		});
	}

	return $query->get();

It's not handling NULL which was real problem

Why should it care about null ? You wanted to exclude records where the the id or the parent matched the except value. Was your scope actually different to this?

I want to exclude records where 'id' != $exceptIdAND 'parent_id' != '$exceptId' but I want to keep those records where 'parent_id' = NULL

I also post my categories table above

that does not make sense. you want to exclude where not equal to exceptID ?

and yes, you include sample table, but that table does not include any rows where the parent ID is 15 so you are not testing half of your requirements.

That was a sample and yes I test by putting records against parent_id = 15