php - 如何在 sql 中正确连接 4 个表“多对一”？

Question

早上好。我需要创建一个查询来连接我 4 个表：recipes、food_recipes、price_food 和 users

表

目前我做的是：

与他们的用户一起在里面搜索食谱 我在他们的食物和里面寻找信息，在每次迭代中，我都会查找有关用户输入的食物价格的信息 如果用户没有输入任何价格，我会查找特定日期同一国家所有人的平均价格 如果输入该价格的人数不超过最小值，则处方的总价格将为 0，并且效果很好。但是我需要所有内容都在同一个查询中才能过滤价格。

尝试使用内连接和左连接创建查询，但无论哪种方式我都没有得到我需要的结果。你能给我一些关于正确语法的指导吗？请

$wsqli="SELECT * from recipes 
INNER JOIN  users
on recipes.id_user = users.id_user
where $where 
order by recipes.date desc
limit $limit
OFFSET $offset ";
$result = $linki->query($wsqli);
if($linki->errno) die($linki->error);
while($row = $result->fetch_array()){
    $precio_receta = recipe_price($id_recipe);
}



    function recipe_price($id_recipe)
        {
            $current_date = date('Y/m/d', strtotime('+1 day'));
            $previous_date = date('Y/m/d', strtotime('-8 month'));

            $price_recipe = 0;

            $wsqli="SELECT * from recipes_foods
            where id_recipe='$id_recipe' ";
            $result = $linki->query($wsqli);
            if($linki->errno) die($linki->error);
            while($row = $result->fetch_array()){

                $weight = $row['weight'];
                $fdcId = $row['fdcId'];

                $wsqli="SELECT * from foods_prices
                where fdcId='$fdcId' and id_user='$id_user_session' and (foods_prices.date between '$previous_date' and '$current_date')";
                $result2 = $linki->query($wsqli);
                if($linki->errno) die($linki->error);
                if($row = $result2->fetch_array()){

                    $price_recipe = $price_recipe + (($row['price'] / 1000) * $weight);
                }else{

                    $wsqli="SELECT  
                    COUNT(*) as quantity_prices,
                    AVG(price) as average_price 
                    from foods_prices 
                    inner join users
                    on users.id_user = foods_prices.id_user
                    where fdcId='$fdcId' 
                    and country='$user_country' 
                    and (foods_prices.date between '$previous_date' and '$current_date')";
                    $result2 = $linki->query($wsqli);
                    if($linki->errno) die($linki->error);
                    if($row = $result2->fetch_array()){ 

                        if ($row['quantity_prices'] > 50) {
                            $price_recipe = $price_recipe + (($row['average_price'] / 1000) * $weight);
                        }else{
                            $price_recipe = 0;
                            return $price_recipe;
                            break;

                        }
                    }else{
                        $price_recipe = 0;
                        return $price_recipe;
                        break;
                    }
                }
            }
            return $price_recipe;
        }

I have already managed to gather all the data in a single query.

    $wsqli="SELECT *
    from recipes 
    left JOIN  recipes_foods
        on recipes_foods.id_recipe = recipes.id_recipe 
    left JOIN  foods_prices
        on foods_prices.fdcId = recipes_foods.fdcId  
     left JOIN  users
        on users.id_user = foods_prices.id_user  
    where country='AR' 
    order by recipes.date desc";
    $result = $linki->query($wsqli);
    if($linki->errno) die($linki->error);
    while($row = $result->fetch_array()){

        var_dump($row);
    }

但我仍然不能：

• 知道为该食品输入的价格数量是否大于或等于要求的数量（最少 50）。尝试“其中 COUNT（价格）> 50”，但它显示“无效使用组功能”
• 如果价格数量少于要求，则在结果中最后停止（或不显示）
• 如果价格数量大于要求....按价格订购。添加“order by COUNT (price) desc”会减少仅获得第一个的结果。

标签： phpmysqlsql