Doctrine Native SQL, different result

I need help. I have a raw SQL query (MySql) that calculate a rank of my products based on weight of manufacturer. The two table can be resumed as:

Product

ID	PartnerId
11	1
12	3
13	2
14	3
15	1

Partner

ID	Position
1	2
2	3
3	1

My query is this:

INSERT INTO product_rank (product_id,partner_id,weight,`rank`)
SELECT g.product_id, g.partner_id, g.weight, g.rank FROM (SELECT (@rank:=@rank +1) AS rank, m.* FROM 
(SELECT x.id AS product_id, x.partner_id, x.weight FROM 
    (
        SELECT
            t.id,
            t.partner_id,
            p.position,
            CASE
                WHEN @partner_id != t.partner_id THEN @rownum := 0
                WHEN @partner_id = t.partner_id THEN @rownum := @rownum + 1
                ELSE @rownum
            END AS weight,
            @partner_id := t.partner_id 
        FROM product t
        JOIN partner p ON t.partner_id = p.id
    ) x
ORDER BY x.weight, x.position)m )g, (SELECT @rank := 0) r;

If I run query directly from MySQL console, it works: I have a result as

ID	product_id	partner_id	weight	rank
1	12	3	0	1
2	11	1	0	2
3	13	2	0	3
4	14	3	1	4
5	15	1	1	5

But if I use Doctrine with $this->getEntityManager()->getConnection()->executeStatement

weight and rank are always set to zero.

Where am I doing wrong?

Solution

The issue you're facing could be related to how Doctrine handles prepared statements and the execution of SQL queries. In a prepared statement, the SQL query is parsed and prepared once, and then you bind parameters and execute it multiple times with different values. However, in your case, you're using session variables (@rank, @partner_id) that can have stateful behavior between different executions of the query.

Doctrine might not handle session variables and their statefulness in the same way as executing raw SQL directly in MySQL console does. To work around this, you can try a couple of things:

1. Using NativeQuery with Single Statement Execution:

Instead of using executeStatement, try using Doctrine's createNativeQuery and execute the entire SQL query as a single statement. This can sometimes ensure that the session variables maintain the intended behavior.

$sql = "INSERT INTO product_rank (product_id, partner_id, weight, `rank`)
        SELECT g.product_id, g.partner_id, g.weight, g.rank FROM (...your SQL query...)";

$nativeQuery = $this->getEntityManager()->createNativeQuery($sql);
$nativeQuery->getResult();

2. Resetting Session Variables Manually:

Since session variables might behave differently between raw SQL execution and Doctrine, you could consider manually resetting the session variables before executing the query.

$this->getEntityManager()->getConnection()->executeStatement("SET @rank := 0;");
// Now execute your main query
$this->getEntityManager()->getConnection()->executeStatement("...your main SQL query...");

Try these approaches and see if they resolve the issue. Remember that Doctrine might have some differences in how it handles SQL execution compared to direct SQL console execution, and these workarounds are aimed at addressing those differences.