Bring multiple values from a Join in mysql

Question

Guys, I have a question about a mysql query using the Codeigniter Framework. I have the following tables:

Table Products

|--------|-----------------|
|  id    |   nome_produto  |
|--------|-----------------|
|   1    |   Camisa Tal    |
|--------|-----------------|
|   2    |   Bermuda Tal   |
|--------|-----------------|

Products Table - Grid

|--------|-----------------|-----------------|-----------------|
|  id    |     tamanho     |   valor_item    |   produto_id    |
|--------|-----------------|-----------------|-----------------|
|   1    |        P        |      35,00      |        1        |
|--------|-----------------|-----------------|-----------------|
|   2    |        M        |      45,00      |        1        |
|--------|-----------------|-----------------|-----------------|
|   3    |        P        |      22,00      |        2        |
|--------|-----------------|-----------------|-----------------|

I need that in the query, when viewing the product, have a way to display all the values of the product size. Example:

I selected product 1, in this same product we have two size variations but with different prices, I need to bring these prices different to the result of the product visualization.

My appointment is like this:

public function detalhe($id)
    {
        $this->db->select("prod.*");
        $this->db->select("gr.valor_item");
        $this->db->where('prod.slug', $id);
        $this->db->join('ga845_produtos_grades gr', 'prod.id = gr.produtos_id', "inner");
        $this->db->limit(1);
        $query = $this->db->get('ga845_view_produtos prod');
        return $query->result();
    }

So current, instead of the consultation bring all the values, for example the product Shirt, it brings only the first value that would be R $ 35,00 ignoring the 45,00. How do I fix it?

Thank you and I hope I explained the difficulty well.

Answer 1

Following example:

CREATE TEMPORARY TABLE produtos_temp ( id int, descricao varchar(100) );
INSERT INTO produtos_temp VALUES( 1, "Camisa Tal" );
INSERT INTO produtos_temp VALUES( 2, "Bermuda Tal" );
CREATE TEMPORARY TABLE grade_temp ( id int, tamanho char(1), valor double(10,2), produtos_temp_id int);
INSERT INTO grade_temp VALUES( 1, "P", 35.77, 1 );
INSERT INTO grade_temp VALUES( 2, "M", 25.50, 1 );
INSERT INTO grade_temp VALUES( 3, "G", 49.90, 1 );
INSERT INTO grade_temp VALUES( 4, "P", 25.00, 2 );
INSERT INTO grade_temp VALUES( 5, "M", 35.50, 2 );
INSERT INTO grade_temp VALUES( 6, "G", 59.90, 2 );

SELECT 
    pt.id as ID,
    pt.descricao as Descricao,
    SUM(TamanhoP) AS P,
    SUM(TamanhoM) AS M,
    SUM(TamanhoG) AS G
FROM
    produtos_temp AS pt
        INNER JOIN
    (SELECT 
        IF(tamanho = 'P', valor, 0) AS TamanhoP,
        IF(tamanho = 'M', valor, 0) AS TamanhoM,
        IF(tamanho = 'G', valor, 0) AS TamanhoG,
        produtos_temp_id as ID
    FROM
        grade_temp
    WHERE
        produtos_temp_id = :produtos_temp_id
    GROUP BY tamanho WITH ROLLUP) AS gt ON pt.id = gt.ID;

In the Doctrine, for being a slightly more complex query, you can use the Resultsetmapping, this way to capture the return:

$sqlFile = new \SplFileObject('caminho para o aarquivo .sql');
$sql =  $sqlFile->openFile()->fread($sqlFile->getSize());

$rsm = new ResultSetMapping();
$rsm->addScalarResult('ID', 'ID');
$rsm->addScalarResult('Descricao', 'Descricao');
$rsm->addScalarResult('P', 'P');
$rsm->addScalarResult('M', 'M');
$rsm->addScalarResult('G', 'G');

$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(':produtos_temp_id', $data['produtos_temp_id']);

return $query->getResult();