Inner join when it should be left join in JPA-CodePudding

I'm using JpaRepository and I'm creating a @Query in my repository:

this is my query:

@Query( "SELECT SUM(p.prima) as prima, p.producto as producto, p.tipoProducto as tipoProducto, p.compania as compania, p.cliente as cliente, p.vendedor as vendedor "  
        "FROM Poliza p "  
        "JOIN Producto pr       ON p.producto=pr "  
        "JOIN TipoProducto tp   ON p.tipoProducto=tp "  
        "JOIN Compania c        ON p.compania=c "  
        "JOIN Cliente cl        ON p.cliente=cl "  
        "LEFT JOIN Vendedor v   ON p.vendedor=v "  
        "WHERE p.comienzo >=?1 "  
        "AND p.comienzo <= ?2 "  
        "GROUP BY p.producto")

and I realize that I only get the rows where "Vendedor" is present. I used the spring.jpa.show-sql=true property to check what was going on and I realize that the query is creating an inner join for each property in the SELECT STATEMENT

inner join producto producto1_ on (poliza0_.producto=producto1_.id) 
inner join tipo_producto tipoproduc2_ on (poliza0_.tipo_producto=tipoproduc2_.id)
inner join compania compania3_ on (poliza0_.compania=compania3_.id) 
inner join cliente cliente4_ on (poliza0_.cliente=cliente4_.id) 
inner join vendedor vendedor5_ on (poliza0_.vendedor=vendedor5_.id) 

join producto producto6_ on poliza0_.producto=producto6_.id 
join tipo_producto tipoproduc7_ on poliza0_.tipo_producto=tipoproduc7_.id 
join compania compania8_ on poliza0_.compania=compania8_.id 
join cliente cliente9_ on poliza0_.cliente=cliente9_.id 
left join vendedor vendedor10_ on poliza0_.vendedor=vendedor10_.id

As you can see in the first part I have an inner join from Vendedor which makes the query wrong.

How should I create my query to get the expected result?

CodePudding user response：

if understand clearly

@Query( "SELECT SUM(p.prima) as prima, p.producto as producto, p.tipoProducto as tipoProducto, p.compania as compania, p.cliente as cliente, p.vendedor as vendedor "  
        "FROM Poliza p "  
        "JOIN Producto pr       ON p.producto=pr "  
        "JOIN TipoProducto tp   ON p.tipoProducto=tp "  
        "JOIN Compania c        ON p.compania=c "  
        "JOIN Cliente cl        ON p.cliente=cl "  
        "LEFT JOIN Vendedor v   ON p.vendedor=v "  
        "WHERE p.comienzo >=?1 "  
        "AND p.comienzo <= ?2 "  
        "GROUP BY p.producto")

i think you can using v instead p.vendedor as vendedor in select field.

CodePudding user response：

If I read correctly, you are defining your own query. So, you can edit the JOIN in your query and write LEFT JOIN instead. Am I right on your intentions?