I need to select recipes and their ingredients, without repeating the other recipe fields

Question

I have the following scenario:

Revenue Table
---------------------
id
name
method of preparation
category

Ingredient Table
--------------------------
id
name

Table Ingrediente_recipe
--------------------------------------
id
id_recipe
id_ingredient

I want to make a select that brings the recipes with their respective ingredients according to the ingredients I inform.

Ex: select from the recipes that have flour, egg and sugar (I want you to bring recipes that have only these 3 ingredients, if the recipe has any other ingredients, I do not want to appear)

Supposing a carrot cake only has these three ingredients

I don’t want you to bring it like this:

1| Carrot cake | method of preparation: blablabla | Category: Sweets | flour
1| Carrot cake | way of preparation: blablabla | Category: Sweets | egg
1| Carrot cake | method of preparation: blablabla | Category: Sweets | sugar

I want you to bring it like this (All in the same line):

1| Carrot cake | method of preparation: blablabla | Category: Sweets | flour | egg | sugar

Database: Mysql

Can you help me? Thank you.

Answer 1

We’re going to need two parameters, the id’s of the ingredients we’re looking for and the amount of ingredients that are being searched for. Then we’ll do it this way:

Structure of test tables:

CREATE TABLE receita(id int, nome varchar(20), preparo varchar(20), categoria int);
CREATE TABLE ingrediente(id int, nome varchar(50));
CREATE TABLE ingrediente_receita(id int, id_receita int, id_ingrediente int);

INSERT INTO receita VALUES(1,'bolo','teste',1);

INSERT INTO ingrediente values(1, 'farinha'),(2,'açucar'),(3,'ovo');

insert into ingrediente_receita VALUES(1,1,1);
insert into ingrediente_receita VALUES(1,1,3);

did not create relationship because it is only to reference the fields to the field of the query to be clear, so, follows the query:

SELECT
    r.id,
    r.preparo,
    r.categoria,
    GROUP_CONCAT(i.nome SEPARATOR ' | ') AS ingrediente
FROM receita r
INNER JOIN ingrediente_receita ir
ON ir.id_receita = r.id
INNER JOIN ingrediente i
ON i.id = ir.id_ingrediente
WHERE r.id IN(
SELECT
    r.id
FROM receita r
INNER JOIN ingrediente_receita ir
ON ir.id_receita = r.id
INNER JOIN ingrediente i
ON i.id = ir.id_ingrediente
WHERE ir.id_ingrediente IN(1,3)[id_ingredientes]
HAVING (SELECT COUNT(*) FROM ingrediente_receita WHERE id_receita = r.id) = 2[qtd_ingredientes])

in the parameter [id_ingredients] we will pass the id’s separated by ',', and in the parameter [qtd_ingredients] we will pass the amount of ingredients.

the return of the query with the above parameters will be this:

Then for future tests you can change the id’s and quantities, example IN(1,2) for more that it finds the flour will not return because we are looking for some recipe that has 2 ingredients[qtd_ingredients] and in this return it would bring only 1 line representing the flour.

Answer 2

Let’s assume you’ve already done your search and fetch an array on it, getting the result:

1| Carrot cake | method of preparation: blablabla | Category: Sweets | flour

1| Carrot cake | way of preparation: blablabla | Category: Sweets | egg

1| Carrot cake | method of preparation: blablabla | Category: Sweets | sugar

$rs = [
   0 => [
      'id' => '1',
      'receita' => 'bolo de cenoura',
      'modo' => 'bla bla bla',
      'categoria' => 'doces',
      'ingrediente' => 'farinha'
   ],
   1 => [
      'id' => '2',
      'receita' => 'bolo de cenoura',
      'modo' => 'bla bla bla',
      'categoria' => 'doces',
      'ingrediente' => 'açucar'
   ],
   2 => [
      'id' => '3',
      'receita' => 'bolo de cenoura',
      'modo' => 'bla bla bla',
      'categoria' => 'doces',
      'ingrediente' => 'ovo'
   ]
]

What you will do is select a column, in case 'ingredientes', and implode the data from that array in a string:

$ingredientes = array_column($rs, 'ingrediente');
$lista_ingredientes = implode(',', $ingredientes);
echo $lista_ingredientes; //farinha,açucar,ovo

Functional example in Ideone