Bring multiple join values into mysql

Question

Bring multiple join values into mysql

Navigation

#1 by (0 votes)

0

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

Products Table

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

Products Table - Grade

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

In the query, when viewing the product, I need to display all the values of the product size. Example:

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

My query looks 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();
    }

In this current way, instead of the query bring all the values, for example the product Shirt, it brings only the first value that would be $ 35.00 ignoring the $ 45.00. How do I resolve this?

Thanks and I hope I've explained the difficulty well.

php mysql framework mysqli codeigniter

asked by anonymous 12.09.2018 / 03:52

1 answer

Sequential files in shell script php - How to set maximum execution time, using interval in each loop? [duplicate]

score 0 · Answer 1

Here's an 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;

doctrine , because it is a slightly more complex query, you can use ResultSetMapping , in order 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();