Convert SQL query to Laravel equivalent

I am beginner in Laravel and php. I have mysql tables:

CREATE TABLE `dishes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `enable` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `dish_values` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `dishes_id` bigint(20) UNSIGNED NOT NULL,
  `food_ingredient_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` decimal(9,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `food_ingredients` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `garbage` decimal(9,2) NOT NULL DEFAULT '0.00',
  `energy_value` decimal(9,2) NOT NULL DEFAULT '0.00',
  `protein` decimal(9,2) NOT NULL DEFAULT '0.00',
  `fat` decimal(9,2) NOT NULL DEFAULT '0.00',
  `available_carbohydrates` decimal(9,2) NOT NULL DEFAULT '0.00',
  `roughage` decimal(9,2) NOT NULL DEFAULT '0.00',
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `url_address` varchar(160) COLLATE utf8mb4_unicode_ci NOT NULL,
  `allergen` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `allergen1` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `allergen2` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `allergen3` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `available_in_demo` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `enable` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and query mysql:

select d.id, d.company_id, d.name, d.description, d.enable, d.allergen
from dishes d  
where not exists (
    select 1
    from dish_values v 
    inner join food_ingredients i on i.id = v.food_ingredient_id
    where 
        v.dishes_id = d.id 
        and i.allergen1 = '1'
)  and d.company_id = 1 and d.enable = 1

This queries work fine. I want convert this query to Laravel equivalent.

I try write it:

Dish::whereHas('foodIngredient', function($q) use($query, $companyId)
            {
                $q->whereHas('dish_values', function($q) use($query, $companyId){
//                        $q->where([['allergen1', 1], ['dishes.company_id', 1]])
//                            ->orWhere([['allergen1', 1], ['dishes.company_id', $companyId]]);
                    });
            })->where([
                ['dishes.name', 'LIKE', '%' . $query . '%'],
                ['dishes.enable', '=', 1],
//                ['food_ingredients.allergen1', '=', 1],
            ])->paginate(8)

But it's not working. My Model:

class Dish extends Model
{
    protected $quarded = ['id'];
    protected $fillable = ['company_id', 'name', 'description',  'enable', 'allergen'];
    public $timestamps = false;

    public function components()
    {
        return $this->hasManyThrough('App\DishValues', 'App\Dish', 'id', 'dishes_id');
    }

    public function foodIngredient()
    {
        return $this->hasManyThrough('App\FoodIngredient', 'App\DishValues', 'dishes_id', 'id');
    }

    public function foodIngredient2()
    {
        return $this->hasManyThrough('App\FoodIngredient', 'App\DishValues', 'id', 'food_ingredient_id');
    }

}

but this is not working :(

How can I make it? I'm trying many options, but I don't know haw convert my query to Laravel equivalent

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2SuCHki
via IFTTT