title: Best Way to Query This in Laravel
body: <p>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.</p>
  
  <p>I have 2 tables as follows:</p>
  
  <p>item_centers:</p>
  
  <pre><code>| id | center | identifier | description        |
  |----|--------|------------|--------------------|
  | 1  | 1A     |       0011 | Description for 1A |
  | 2  | 1B     |       0022 | Description for 1B |
  | 3  | 1C     |       0033 | Description for 1C |
  </code></pre>
  
  <p>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.</p>
  
  <p>items:</p>
  
  <pre><code>| id | identifier | description        | quantity |
  |----|------------|--------------------|----------|
  | 1  |       0011 | Description for 1A |      250 |
  | 2  |       0022 | Description for 1B |      500 |
  | 3  |       0033 | Description for 1C |      750 |
  </code></pre>
  
  <p>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".</p>
  
  <p>When a user selects the item_center, the item description , and clicks submit - I have a process that does this:
  1. Get all items from the "items" table, with the same "identifier" as the one selected from the item center dropdown.
  2. Sum up the quantity of all the retrieved items in step one.
  3. Subtract the amount that the user entered from the item list, starting with the oldest first (created_at column).</p>
  
  <p>So, to my problem...</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre class="lang-php prettyprint-override"><code>public function index()
  {
          $itemCenters = ItemCenter::select(['id', 'center', 'identifier', 'description'])
                                      -&gt;orderBy('center', 'asc')
                                      -&gt;orderBy('description', 'asc')
                                      -&gt;get();
  
          $itemDescriptions = Item::select('description')
                          -&gt;where('description', 'LIKE', '% .$keyword. %')
                          -&gt;orWhere('description', 'LIKE', '.$keyword. %')
                          -&gt;orWhere('description', 'LIKE', '% $.$keyword.')
                          -&gt;distinct('description')
                          -&gt;orderBy('description', 'asc')
                          -&gt;get();
  
          foreach ($itemCenters as $itemCenter)
          {
              foreach ($itemDescriptions as $itemDescription)
              {
                  $currentQty = Item::where('identifier', $itemCenter-&gt;identifier)
                                   -&gt;where('description', $itemDescription-&gt;description)
                                   -&gt;sum('quantity');
  
  
                  if ($currentQty &lt;= 0)
                  {
                      $itemCenter-&gt;forget($itemCenter-&gt;id);
                  }
              }
          }
  
          return view('pages.ItemRemoval', compact('itemCenters'), compact('itemDescriptions'));
  }
  </code></pre>
  
  <p>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.</p>