good morning. I need to create a query that joins me 4 tables: recipes, food_recipes, prices_food AND users
Currently what I do is:
search for recipes together with their user inside I look for information on their food and inside, in each iteration, I look for the information on the price of the food that the user entered If the user did not enter any price, I look for the average of all the prices of people in the same country on certain dates If the number of people who entered that price does not exceed the minimum, then the total price of the prescription will be 0 and it works very well. But I need everything to be in the same query to be able to filter for the price.
Try to create the query, with inner join and left join, but either way I don't get the result I need. Could you give me some guidance on what the correct syntax would be like? please
$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);
}
but I still can't:
- know if the quantity of prices entered for that food is greater than or equal to that required (min. 50). Try "where COUNT (price)> 50" but it says "Invalid use of group function"
- If the quantity of prices IS LESS than required, stop last in the results (or do not show)
- If the quantity of prices is greater than required .... Order by price. Adding "order by COUNT (price) desc" reduces the results obtaining only the first one.
