Laravel Query Builder, Where based on condition?

Question

I have a dropdown to select a parent, which is self-referencing to the Page. I want to limit the results of that dropdown, so that it won't allow me to nest a Page more than one level.

If I edit the 'Son' Page, and the 'Son' has a 'Grandson', than I shouldn't be allowed to select 'Dad' as parent for the son, since it would create a nest that is to deep

In the folllowing case, When I'm editing the Son record, I shouldn't be able to select Dad as it's parent, since the son has children.

+----+-----------+----------+
| id | parent_id |  title   |
+----+-----------+----------+
|  1 | NULL      | Dad      |
|  2 | NULL      | Son      |
|  3 | 2         | Grandson |
+----+-----------+----------+

Now in this case, I should be able to select Dad as the parent when I'm editing the Son record, since the Son doesn't have any children

+----+-----------+----------+
| id | parent_id |  title   |
+----+-----------+----------+
|  1 | NULL      | Dad      |
|  2 | NULL      | Son      |
|  3 | NULL      | Grandson |
+----+-----------+----------+

I'm struggling to get my head around this, and on how to wrap this all in the query builder.

What I have so far

The following code works if the Son has a Child of it's own, I won't be able to select Dad, which is good. But it fails when there's no children.

It comes down to this: My parent select should also allow pages where parent_id is null to be shown, but only if the current record (Son) doesn't have any children.

Recap: Only show the record if it doesn't occur in any parent_id, so has no children, if it does however no records are to be shown.. If it doesn't, it should show the records where parent_id is null. Is this possible in one query?

$query->where('id', '<>', $page->id);
$query->where('parent_id', '<>', $page->id);

So, you want to select record only if there is no children of the record and no parent of the record ? — Sagar Gautam
– Sagar Gautam, Commented Jul 18, 2019 at 7:48
Only if the record doesn't occur in any parent_id, so has no children, if it does however no records are to be shown.. If it doesn't, it should show the records where parent_id is null. — Miguel Stevens
– Miguel Stevens, Commented Jul 18, 2019 at 7:49
Quite confusing your explanation :D, can you explain in more — Sagar Gautam
– Sagar Gautam, Commented Jul 18, 2019 at 7:55
Well I have a dropdown to select a parent, which is self-referencing to the Page. I want to limit the results of that dropdown, so that it won't allow me to nest a Page more then one level. If I edit the 'Son' Page, and the 'Son' has a 'Grandson', than I shouldn't be allowed to select 'Dad' as parent for the son, since it would create a nest that is to deep — Miguel Stevens
– Miguel Stevens, Commented Jul 18, 2019 at 7:55

Sagar Gautam · Accepted Answer · 2019-07-18 08:00:47Z

1

You can create parent and children relationship in the Page model.

public function parent()
{
    return $this->belongsTo('Page', 'parent_id');
}

public function children()
{
    return $this->hasMany('Page', 'parent_id');
}

Now, you can check if Page has children or not and the pick up the records with parent id null like this:

$pages = Page::where('parent_id', null)->doesntHave('children')->get();

This will give you records with parent id null and no children. I hope you understand.

answered Jul 18, 2019 at 8:00

Sagar Gautam

9,4099 gold badges62 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Miguel Stevens Over a year ago

I'm afraid I'm using the Query Builder, so I don't have access to ->doesntHave()

Sagar Gautam Over a year ago

@Notflip hmm, Create a model it will ease the process ;)

Miguel Stevens Over a year ago

It's more complex than that, I have a model, but I'm using this method in Nova, in a function called relatableQuery(), that fills are dropdown with relatable models, I can only filter using the query builder.

Sagar Gautam Over a year ago

@Notflip ooh, I am not familiar with nova bro. There may be some way

Friendly Code Over a year ago

doesntHave - That's awesome! Never knew that function existed

|

Bibhudatta Sahoo · Accepted Answer · 2019-07-18 08:35:12Z

1

You can achieve this with query builder sub-query with whereRaw function Check the code

$result = DB::table('parents as p')
    ->whereRaw("parent_id is null and id not in (SELECT parent_id FROM parents WHERE parent_id is not null)")
    ->get();
dd($result);

The o/p looks like this
1st scenario

2nd Scenario

It may help you :)

answered Jul 18, 2019 at 8:35

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

3 Comments

Miguel Stevens Over a year ago

In my first scenario I shouldn't be able to select the Dad case, in your example that is what is returned?

RAUSHAN KUMAR Over a year ago

that is the answer i am looking for.:-)

Bibhudatta Sahoo Over a year ago

Okay great :) then close you question by accepting my answer.

Qirel · Accepted Answer · 2019-07-18 08:42:52Z

1

You need to join the table on itself, as the where-clause is a per-row basis.

Join the table on itself with a left-join, where the ID matches the parent ID. Then select only the rows where the joined table's IDs are null.

$pages = DB::table('pages AS p')
            ->leftJoin('pages AS p1', 'p.id', '=', 'p1.parent_id')
            ->where('p.id', '<>', $page->id)
            ->whereNull('p1.id')
            ->select('p.*')
            ->get();

DB fiddle showing the actual query: https://www.db-fiddle.com/f/pwW1aPmYyVqvWjKgXhisbZ/2

edited Jul 18, 2019 at 8:42

answered Jul 18, 2019 at 7:59

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

7 Comments

Miguel Stevens Over a year ago

When nobody has parent_id's filled in, this doesn't return any results, while all entities should be able to be selected when there's no parents and children

Qirel Over a year ago

Whoops, it's supposed to be a LEFT JOIN. Have a look at the revised answer.

Miguel Stevens Over a year ago

Hmh still not there.. When I edit the "Child", I can select the "Dad" as a parent, although my Child is a parent of Grandchild, so I shouldn't be able to select Dad for that reason. Thanks for your time!

Miguel Stevens Over a year ago

It's maybe even simpler, If the 'Son', is a parent himself, there should be NO query results, since we can't couple the 'Son' to any other record then..

Qirel Over a year ago

So you want that an element that is a parent (of any element) is not included? That's the entire logic?

|

Johhn · Accepted Answer · 2019-07-18 08:00:22Z

Hmmm... I don't think it is possible to break this to just one line because am like you will have to loop the parent_id column checking whether the current id is in the parent_id column or not:

But not to worry: a different approach would be easier: two separate tables and thus different relationship:

Pages model: has one parent
Parents model: belongs to a page

With above relationship you it is easy using eloquent to get what has no parent by just checking where has not the given relationship:

Breaking this down:

Pages model:

public function parents () {
    return $this->hasOne(Parent::class, 'page_id');
}

to parents model:

 Parents model:

 protected $fillable = ['page_id'];

 public function pages () {
     return $this->belongsTo(Page::class, 'page_id');
 }

Collectives™ on Stack Overflow

Laravel Query Builder, Where based on condition?

What I have so far

4 Answers 4

6 Comments

3 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I have so far

4 Answers 4

6 Comments

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related