How can I optimize my Model/DB Query relationship?

I have a Recipe Eloquent model. With so many relationships it was too inefficient (read "slow") to use for a RESTful API response. I get much better performance using the DB query shown below, and then the cache on top of that takes what took up to 30 seconds before and delivers it in less than a second. I understand that maybe a better method would be to have a single flat table for API request such as the one shown below, but that isn't immediately possible.

So...

1. I'd like to have all of my recipe methods continue to live in one place, whether it be an Eloquent model or elsewhere. Should the query below be a method within my model?
2. Can my DB query be done in Eloquent without sacrificing performance?
3. If the query below cannot live within my model, is there any literature on where a DB query should live so that it is easier to manage the object and keep it scalable?

Details:

$recipes = Cache::remember("query-recipe-all-{$this->getLocation()}-{$this->getItemsPerPage()}-{$request->input("start")}-{$request->input("end")}", $cacheExpireAfterMinute, function () {
    return DB::connection('remoteRecipes')->table('Recipe')
       ->join('HowServed', 'Recipe.serveid', '=', 'HowServed.serveid')
       ->join('Colors as Color', 'Recipe.colorid', '=', 'Color.colorid')
       ->join('Glass as GlassType', 'Recipe.glassid', '=', 'GlassType.glassid')
       ->join('BeverageType', 'Recipe.bevtypeid', '=', 'BeverageType.bevtypeid')
       ->join('UOM as UnitOfMeasure', 'Recipe.servingsizeuomid', '=', 'UnitOfMeasure.uomid')
       ->join('Equipment', 'Recipe.equipmentid', '=', 'Equipment.equipmentid', 'full outer')
       ->join('Methods as Method', 'Recipe.methodid', '=', 'Method.methodid')
       ->join('RecipeImages as Images', 'Recipe.recGUID', '=', 'Images.RecipeID')
       ->select(
          'Recipe.recGUID as masterRecipeId',
          'Recipe.MoninDotCom_SKU as alt_id',
          'Recipe.credat as date_created',
          'Recipe.updat as date_updated',
          'Recipe.locationid as locationId',
          'Recipe.location as locationTextId',
          'Recipe.publishedname as titlePreferred',
          'Recipe.description as title',
          'HowServed.serveid as servedHowId',
          'HowServed.served as servedHowLabel',
          'HowServed.location as servedHowLocation',
          'Color.colorid as colorId',
          'Color.color as colorLabel',
          'Color.location as colorLocation',
          'GlassType.glassid as glassTypeId',
          'GlassType.glass as glassTypeLabel',
          'GlassType.location as glassTypeLocation',
          'Recipe.LevelOfDifficulty as levelOfDifficultyId',
          'Recipe.alcohol as hasAlcohol',
          'BeverageType.bevtypeid as beverageTypeId',
          'BeverageType.bevtype as beverageTypeLabel',
          'BeverageType.location as beverageTypeLocation',
          'Recipe.servingsize as servingSize',
          'UnitOfMeasure.uomid as uniteOfMeasureId',
          'UnitOfMeasure.uom as uniteOfMeasureLabel',
          'UnitOfMeasure.location as uniteOfMeasureLocation',
          'Equipment.equipmentid as equipmentId',
          'Equipment.equipment as equipmentLabel',
          'Equipment.location as equipmentLocation',
          'Recipe.Rating as recipeTagRatingInternal',
          'Recipe.isCoffee as recipeTagIsCoffee',
          'Recipe.isFood as recipeTagIsCulinary',
          'Recipe.isHome as recipeTagIsForHome',
          'Recipe.isBar as recipeTagIsForBar',
          'Method.methodid as methodId',
          'Method.methodName as methodLabel',
          'Method.location as methodLocation',
          'Recipe.Instructions as instructions',
          'Recipe.recGUID as IngredientsLookupId',
          'Recipe.recGUID as GetProductsUsedInIngredients',
          'Recipe.recGUID as Garnish.LookupId',
          'Recipe.recGUID as Seasons.LookupId',
          'Images.*'
       )
       ->where('Recipe.location', '=', $this->getLocation())
       ->where('Recipe.web', '=', 1)
       ->where('Images.apptype', '=', "monin.com US")
       ->whereBetween('Recipe.updat', [$this->getListFromDate(), $this->getListToDate()])
       ->orderBy('Recipe.updat', 'DESC')
       ->paginate($this->getItemsPerPage());
  });

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/24p0AYg
via IFTTT