Best Way to Query This

I'm going to try to simplify this process as much as possible to get straight to the point. If you have any additional questions then let me know by all means.

I have 2 tables as follows:

item_centers:

| id | center | identifier | description        |
|----|--------|------------|--------------------|
| 1  | 1A     |       0011 | Description for 1A |
| 2  | 1B     |       0022 | Description for 1B |
| 3  | 1C     |       0033 | Description for 1C |

An item center can have many items in a way. The "identifier" column in the table, represents the identifier column in the "items" table below. So center 1A can have many "items" with the identifier of 0011.

items:

| id | identifier | description        | quantity |
|----|------------|--------------------|----------|
| 1  |       0011 | Description for 1A |      250 |
| 2  |       0022 | Description for 1B |      500 |
| 3  |       0033 | Description for 1C |      750 |

I have an item center dropdown that lists all the item centers by the center from the "item_centers" table. (Ex: 1A). Alongside that dropdown, I have an item dropdown that lists all the unique descriptions containing a key word from the "items" table. Under these 2 drop-downs, I have a text box that allows the user to enter a quantity of the amount that they are trying to subtract from the selected "item".

When a user selects the item_center, the item description , and clicks submit - I have a process that does this:

Get all items from the "items" table, with the same "identifier" as the one selected from the item center dropdown.
Sum up the quantity of all the retrieved items in step one.
Subtract the amount that the user entered from the item list, starting with the oldest first (created_at column).

So, to my problem...

We have many item centers that contain items with a quantity of 0. I want to be able to remove all of the item centers from the list that have a 0 quantity, so that the user doesn't have to sort through 100's of item centers to find one that has a quantity above 0.

Here is a quick example of what I mocked up. It's obviously a horrible approach, because it's running tons of queries - and will time out. But it may work better as a mockup for what I'm trying to achieve here.

public function index()
{
        $itemCenters = ItemCenter::select(['id', 'center', 'identifier', 'description'])
                                    ->orderBy('center', 'asc')
                                    ->orderBy('description', 'asc')
                                    ->get();

        $itemDescriptions = Item::select('description')
                        ->where('description', 'LIKE', '% .$keyword. %')
                        ->orWhere('description', 'LIKE', '.$keyword. %')
                        ->orWhere('description', 'LIKE', '% $.$keyword.')
                        ->distinct('description')
                        ->orderBy('description', 'asc')
                        ->get();

        foreach ($itemCenters as $itemCenter)
        {
            foreach ($itemDescriptions as $itemDescription)
            {
                $currentQty = Item::where('identifier', $itemCenter->identifier)
                                 ->where('description', $itemDescription->description)
                                 ->sum('quantity');
                

                if ($currentQty <= 0)
                {
                    $itemCenter->forget($itemCenter->id);
                }
            }
        }

        return view('pages.ItemRemoval', compact('itemCenters'), compact('itemDescriptions'));
}

Like I said before, this is really simplifying the process - and things could've been left out. So let me know if there is any confusion.

jabain

7 years ago

Level 4

Can you total your item quantity up into itemCenters when items are being maintained? That way you do not have to retrieve unneccesary records while building your drop down. In many cases, that will save time consuming IO calculating the quantity by scanning across the file.

chunterb

7 years ago

Level 6

@JABAIN - That's a good idea - so that I won't have to recalculate on every request. But, I still need to initially do it so that I can store that data... how can that be done without running so many queries it causes a timeout?

zhxscript

7 years ago

Level 2

This might be a way I would go about this. Define some relationships in your models, and adjust your query. Adjust to your exact needs.

In your "ItemCenters" Model define a relationship such as

public function items(){
    return $this->hasMany(Item::class,'identifier', 'identifier');
}

In your Items class define the inverse relationship:

public function center(){
    return $this->belongsTo(ItemCenter::class,'identifier', 'identifier');
}

Your New Query Example:


$itemCenters = ItemCenter::select(['id', 'center', 'identifier', 'description'])
         ->orderBy('center', 'asc')
         ->orderBy('description', 'asc')
->with([items=> function ($query) {
    $query->where('quantity', '>', '0');
    }])->get();

This should return all your Item Centers and load thier ITEMS that have quantity more then 0.

I have not "tested" this code, but I think I get what you want to do and this should work. Sorry for code errors, still working on my first coffee :D

1 like

Please or to participate in this conversation.