CODEWITHSUNDEEP
phpmysqllaravelquery-builder
Miguel Stevens
Miguel Stevens

Reputation: 9230

Laravel Query Builder, Where based on condition?

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

Upvotes: 3

Views: 2938

Answers (4)

Qirel
Qirel

Reputation: 26490

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

Upvotes: 1

Bibhudatta Sahoo
Bibhudatta Sahoo

Reputation: 4904

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

enter image description here

2nd Scenario enter image description here

It may help you :)

Upvotes: 1

Sagar Gautam
Sagar Gautam

Reputation: 9389

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.

Upvotes: 1

Johhn
Johhn

Reputation: 1049

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

Upvotes: 0

Related Questions