CODEWITHSUNDEEP
mysqlsymfonydoctrine-orm
Torben
Torben

Reputation: 5494

Doctrine - SQL Query Builder - update and join?

I use Doctrine's SQL Query Builder (http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html)

and want to do an update case with a join. I understand that a join is not supported here. So I do this: 

$query = $dbalQueryBuilder->update('s_articles_attributes', 'aa')
                    ->set('aa.fp_pos_days', ':days')
                    ->where('aa.articledetailsID IN (SELECT a.id FROM s_articles a WHERE a.supplierID IN (:supplierIds))')
                    ->setParameter('days', $days_string)
                    ->setParameter('supplierIds', $supplierIds)
                    ->execute();

$supplierIds is string(39) "3,4,26,28,31,36,37,38,48,49,55,56,64,82"

When I run this query, nothing happens. When I do this:

$query = $dbalQueryBuilder->update('s_articles_attributes', 'aa')
                        ->set('aa.fp_pos_days', ':days')
                        ->where('aa.articledetailsID IN (SELECT a.id FROM s_articles a WHERE a.supplierID IN (3,4,26,28,31,36,37,38,48,49,55,56,64,82))')
                        ->setParameter('days', $days_string)
                        ->execute();

It works fine. Does anybody know why? And that do I do wrong? I need to use that variable...

Upvotes: 2

Views: 920

Answers (2)

Torben
Torben

Reputation: 5494

I did solve it:

I do pass the values as an array now and critical was to declare the type as well:

->setParameter('days', $days_string)
->setParameter('supplierIds', $supplierIds, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY)

Upvotes: 2

Mocrates
Mocrates

Reputation: 209

try to pass an array and not a string to set parameter doctrine know how to map an "in" where clause.

explode your string

Upvotes: 0

Related Questions