SQL LITE + SYMFONY TESTING: SQLSTATE[HY000]: General error: 1 near ON

Question

I'm using symfony+sqllite to test my application and I have issue with ORM. Doctrine generates sql with double ON in JOIN, something like:

SELECT s0_.config AS config_0, s0_.sensor_number AS sensor_number_1 FROM sensors s0_ LEFT JOIN fos_user f1_ LEFT JOIN worker w2_ ON f1_.id = w2_.id LEFT JOIN dealer d3_ ON f1_.id = d3_.id ON (s0_.owner_id = f1_.id) AND (f1_.status != 'd') LEFT JOIN user_groups u4_ ON (f1_.user_group_id = u4_.id) AND (u4_.status != 'd') WHERE (s0_.status = ? AND u4_.id = ? AND ((s0_.device_id IS NULL OR s0_.device_id = ?))) AND ((select user_group_id from fos_user where id = s0_.owner_id) = '1') AND (s0_.status != 'd')

it is about:

ON f1_.id = d3_.id ON (s0_.owner_id = f1_.id)

it cause error:

SQLSTATE[HY000]: General error: 1 near \"ON\": syntax error","class":"Doctrine\DBAL\Exception\SyntaxErrorException"

I checked and I'm aware that SQLite doesn't work with more than one "ON"(postgresSQL works), so why doctrine generates it when run tests.

Symfony form responsible for this:

$builder->add(
            'sensors',
            EntityType::class,
            [
                'class' => Sensors::class,
                'label' => 'form.assigned-sensors',
                'required' => false,
                'multiple' => true,
                'by_reference' => false,
                'query_builder' => function (EntityRepository $er) use ($device, $userGroup) {
                    $qb = $er->createQueryBuilder('s')
                        ->leftJoin(User::class, 'o', 'WITH', 's.owner = o.id')
                        ->leftJoin(UserGroups::class, 'g', 'WITH', 'o.userGroup = g.id')
                        ->where('s.status = :status')
                        ->andWhere('g.id = :userGroupId')
                        ->andWhere('(s.device is null or s.device = :deviceId)')
                        ->setParameter('status', 'a')
                        ->setParameter('deviceId', $device->getId())
                        ->setParameter('userGroupId', $userGroup->getId());

                    return $qb;
                },
            ]
        );

EDIT:

Finally i found the issue as described in my answer below. Thanks for your time.

`LEFT JOIN tab4 d3_ ON f1_.id = d3_.id ON (s0_.o` You have an on without a join — litelite, May 26 '17 at 13:00
LEFT JOIN tab4 d3_ ON f1_.id = d3_.id ON - it works on postgressql — Arkowsky, May 26 '17 at 13:11

Arkowsky · Accepted Answer · 2017-05-31T11:31:41.587

Finally i found the issue in this ticket:

https://stackoverflow.com/a/15088250/6357312

There is no need to do:

->leftJoin(User::class, 'o', 'WITH', 's.owner = o.id')

should be only:

->leftJoin('s.owner', 'o')

Entity has defined on this relation to which table is made relation on selected entity.

Finally this code looks like:

$builder->add(
            'sensors',
            EntityType::class,
            [
                'class' => Sensors::class,
                'label' => 'form.assigned-sensors',
                'required' => false,
                'multiple' => true,
                'by_reference' => false,
                'query_builder' => function (EntityRepository $er) use ($device, $userGroup) {
                    $qb = $er->createQueryBuilder('s')
                        ->leftJoin('s.owner', 'o')
                        ->leftJoin('o.userGroup', 'g')
                        ->andWhere('g.id = :userGroupId')
                        ->andWhere('(s.device is null or s.device = :deviceId)')
                        ->setParameter('userGroupId', $userGroup->getId())
                        ->setParameter('deviceId', $device->getId());

                    return $qb;
                },
            ]
        );

SQL LITE + SYMFONY TESTING: SQLSTATE[HY000]: General error: 1 near ON

1 Answers1