6

This is my first post in here, so please forgive any mistakes :)

I'm currently working on the project of stock management application (Laravel). I came to the point where anything I do doesn't work, so now I beg for help with it.

I have a table with products, of which some are in the relationship with the others. Everything happens in one table. If the product has a child, the child overrides the parent.

products table view

Then, all the queries I run on them use the following logic:

  • If the item doesn't have any child, use it.
  • If the item has children, use the latest child (highest id)

Now I have the relationships created in model file:

public function childItems(){
    return $this->hasMany('\App\OrderItem','parent_id');
}

public function parentItem(){
    return $this->belongsTo('\App\OrderItem','parent_id');
}

public function latestChild(){
    return $this->hasOne('\App\OrderItem','parent_id')->orderBy('id','desc')->limit(1);
}

The problem with latestChild() relationship is, that when you run this query:

\App\OrderItem::find(7)->latestChild()->get()

It works fine and returns only one (latest)(id 6) record in relationship - to do it I had to add orderBy and limit to hasOne().

But when I want to use this relationship in scopes, so in whereHas method, it doesn't work properly, as takes any of the children instead of the latest one.

public function scopeDue($query){
    return $query->where(function($q){
                            $q->has('childItems','==',0)->has('parentItem','==',0)->whereDate('due_date','=', Carbon::today()->toDateString())->whereNull('return_date');
                        })->orWhere(function($q2){
                            $q2->has('childItems')->has('parentItem','==',0)->whereHas('childItems',function($q3) use($q2){
                                $q3->whereDate('due_date','=', Carbon::today()->toDateString())->whereNull('return_date');
                            });
                        })->with('latestChild');

}

However, with() at the end returns the right record.

I think, the reason it works so is because my relationship latestChild() returns all the children (despite hasOne()) and when i use it in whereHas it ignores the filtering functions I applied.

I know it's a little bit complex from what I describe, but to explain it better I will use an example. Executing the following in tinker

\App\OrderItem::due()->get();

Should return only record id 2, as the number seven has children, where of course id 5 is due, but the latest child is id 6 which is not due.

I hope I've explained it enough to let you help me, as I'm already going crazy with it. If you have any ideas on how I could achieve what I need by changing exisiting one or changing the whole logic of it, please help!

Thanks, Darek

Darek Bujak
  • 61
  • 1
  • 4
  • Hello! I'm having the same problem, cant filter a model by **ONLY** the last related one... Could you solve this issue? Thx. – Zalo Oct 02 '19 at 18:32
  • @Zalo There is a slightly different approach that uses a sub-select column on the parent model to select the id of the latest child, which can then be used to join the corresponding child. I did answer a very similar question [here](https://stackoverflow.com/questions/16035333/how-to-delete-and-recreate-from-scratch-an-existing-ef-code-first-database) before. – Namoshek Oct 02 '19 at 18:39

2 Answers2

0

Try this one:

->with(
    [
        'latestChild' => function (HasOne $query) {
            return $query->latest('id')->limit(1);
        }
    ]
);
SpinyMan
  • 436
  • 5
  • 9
-1

I think the problem is in your latestChild() method where you do a limit(1). Why don't you try the last() method instead?

So:

public function latestChild(){
    return $this->hasOne('\App\OrderItem','parent_id')->last();
}

EDIT:

What about returning the value like this:

public function latestChild(){
    $item = App\OrderItem::all()->last();
    return $item;
}
Norgul
  • 4,613
  • 13
  • 61
  • 144