Windows 7, Apache 2 (xampp 1.8.1), PHP 5.4.7, Symfony 2.2.1

Usage of

columnDefinition

in annotations or generally metadata mappings is all about vendor specific syntax. `columnDefinition` is designed to allow overriding the default ORM column generated DDL to build vendor specific syntax/types, therefore the issue is invalid

Can this be somehow worked around? If not, it is really serious problem...

I couldn't find any workaround.
Trying to force the 'type' condition in the join clause resulted useless as Doctrine would add the 'where' condition regardless.

Easier way to workaround right now, is to declare a OneToMany from class A to class B on a protected field (no need of getter or setter). That way you can do classic join via relationship transversing and then the condition will be placed in the ON part of the query.

I could access with below codes. You should use RAW SQL it is easy solution I guess. good luck.

$qb = $em->createQueryBuilder();

$qb->select('a, b')
->from('YourEntity1', 'a')
->leftJoin('YourEntity2', 'b', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.id = b.relationId');

$raw_sql = $qb->where( 
	$qb->expr()->in('a.relationId', $ids)
)
->orderBy('a.updatedAt', 'DESC')
->setMaxResults(10)
->getQuery()->getSQL();

$conn = $em->getConnection();
$stmt = $conn->query($raw_sql);

/* $stmt->fetchAll(); // access as Array */

Assigned to Alexander

Duplicate of DDC-1940

Benjamin: this bug doesn't seem to be a dupe of DDC-1940. Actually that issue doesn't seem to be a bug at all.

As a reminder, the problem in this issue is that when performing arbitrary left joins on entities that are part of a class hierarchy, the discriminator condition is placed in the where clause instead of the join clause. This means that rows that could not be joined will have null values in the discriminator column and thus will not be returned because of the where condition (which will contain something like " where x.discriminator in (1,2,3) ").

Has this issue been resolved elsewhere? From reading over DDC-1940 it doesn't seem to be a duplicate at all. I'm experiencing the same problem as Jordi and can't seem to find a solution. Is there any particular reason the `IN ()` predicate is not a part of the join, but instead placed in the main `WHERE` clause?

I've been looking into the root cause of this bug (or feature..) to try and understand why it's happening, and after trying various possible fixes (a little hard without full understanding of the Doctrine/Query internals) I've ended up with a fix that seems to work well for my use case, at least. I've not run any of the unit tests (I plan to, and may adjust my fix based on that) but the top revision is my change... https://gist.github.com/tarnfeld/a6bb50ec707c7af1c5dc/revisions

Would love some feedback.

Pull request here: https://github.com/doctrine/doctrine2/pull/656

Tom's fix moves the condition of the discriminator column to the LEFT JOIN, which is exactly what was needed.

Alexander: could you please give it a look?

Tom's proposal seems to fix the issue.

Marked as minor improvement - thank you for the patch!

Osx

What does `$this->_identifier` contain in your failing case?

It contains for example

array(
[id] =>
);

Because on db you find an empty string and not a null value.

If I get it correctly, you are using empty strings to emulate NULL references, which is invalid in SQL ( I've explained it extensively at http://stackoverflow.com/questions/15502408/doctrine-2-use-default-0-values-instead-of-null-for-relation/15511715#15511715 )

Is this what you are doing? Because for any identifier that is not NULL an attempt to load it should be run, regardless of its content.

That's not me

It's the system by which i'm sharing the db.
It uses MyIsam engine with no fk or index, so i've found this way to skip load when on db i've empty strings.

Are there other (maybe cleaner) ways?

Thanks,
Gabriele

No, the only correct way to handle this is to set NULL values for the association meta-columns. Marking as invalid

I can't find doc about it: https://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html?highlight=column#annref-column

Can you give an example?

`$this->someAssociation = NULL;`. That's basically what I mean.

Mmm, didn't understand.

Anyway, we've tried to fix the old Delphi Monster witch store empty strings instead of nulls... with no results.

So, here is my workaround, maybe this can be useful for other developers who will be in troubles in the future..

In "UnitOfWork" class, public method "createEntity".

[...]

 
// TODO: Is this even computed right in all cases of composite keys?
                    foreach ($assoc['targetToSourceKeyColumns'] as $targetColumn => $srcColumn) {
                        $joinColumnValue = isset($data[$srcColumn]) ? $data[$srcColumn] : null;

                        // START-EDIT

                        // GT: our moas store empty string instead of null in fk columns.
                        // So, let's check and handle it as null!

                        if (empty($joinColumnValue))
                            $joinColumnValue = null;

                        // END-EDIT

                        if ($joinColumnValue !== null) {
                            if ($targetClass->containsForeignIdentifier) {
                                $associatedId[$targetClass->getFieldForColumn($targetColumn)] = $joinColumnValue;
                            } else {
                                $associatedId[$targetClass->fieldNames[$targetColumn]] = $joinColumnValue;
                            }
                        }
                    }

[...]

Regards,
Gabriele

Gabriele Tondi the ORM does not deal with such architectures (nor with generally invalid usage of RDBMS systems). The only acceptable solution in ORM is with correct NULL values, as it should be, so this patch is invalid.

I've added two more cases where the parsing fails. Do you want a separate tickets for that?

Don't worry, I'll spend some time over this...
But I'm not sure about the last one.

The 3rd case seems work just fine as a part of a HAVING clause.
I haven't tried it but It might be that it fails with something simpler like SELECT COUNT( * ) = :foo FROM ... or SELECT COUNT( * ) = 2 FROM ...

Fabio I have two more...
It doesn't like NULL and subselect after then part

 
->addSelect('CASE
    WHEN po.quantity IS NULL THEN NULL
    ELSE po.quantity -
            COALESCE(0, (
                SELECT COUNT(rd.product) FROM xxxx rd
                    WHERE (rd.startDate <= :end) AND (rd.endDate >= :start) AND
                        rd.product = c.product)))
    END
    AS po.quantity
')

:edit replaced with real query

addon: well the subquery part can be full query with joins ....

After further investigation, JPA 2.0 and 2.1 do not support NULL as part of ScalarExpression.
There are many underlying problems by adding this straight to ScalarExpression, such as the example I showed.
I don't think supporting this will bring benefits, but too many headaches.
As a workaround, create your own function that generates "NULL" as SQL. It would work perfectly here.
Closing the PR as we will not support it.

Not to sound rude but, the answer is far fetched. So what if JPA is not supporting it. Yes I understand that the Doctrine is modeled after JPA but this shouldn't mean that it's not better in some regards.

This is really a low blow especially if there is a need to use a query builder to build the queries. And as I said it's not only the IS NULL but the CASE statement can contain a whole subquery with it's own CASE statements etc...

We could say a duplicate of: DDC-2208

After further investigation, JPA 2.0 and 2.1 do not support NULL as part of ScalarExpression.
There are many underlying problems by adding this straight to ScalarExpression, such as the example I showed.
I don't think supporting this will bring benefits, but too many headaches.
As a workaround, create your own function that generates "NULL" as SQL. It would work perfectly here.
Closing the PR as we will not support it.

A related Github Pull-Request [GH-665] was closed:
https://github.com/doctrine/doctrine2/pull/665

A related Github Pull-Request [GH-676] was closed:
https://github.com/doctrine/doctrine2/pull/676

Related: DDC-1209, DDC-1780