Implement SqlWalker::walkSingleValuedAssociationPathExpression()

Please show an example of such a DQL query, the SQL you would expect to be generated and what query results you would expect. Thanks!

This situation falls into this:

SELECT u FROM \MyProject\Entities\User u WHERE u.Group = 1;

User <=> Group is a manyToOne relation.

I already inspected source and there's no implementation for that yet.
We should just decide rather how we'll support the composite PK situation.

For the given example, it should throw an exception telling that Group is a composite PK.
But in this case:

SELECT u FROM \MyProject\Entities\User u WHERE u.Group = ?1

$q->setParameter(1, array(1, 2));

Solution:

WHERE (groups.pk_id1 = 1 AND groups.pk_id2 = 2)

Since we do not allow SingleValuedAssociationPathExpression in the InExpression, that would be enough.
If the user attempts to do:

SELECT ... WHERE u.Group IN ?1

It'll through an expression, because of our supported grammar:

InExpression             ::= StateFieldPathExpression ["NOT"] "IN" "(" (InParameter {"," InParameter}* | Subselect) ")"
StateFieldPathExpression ::= IdentificationVariable "." StateField | SingleValuedAssociationPathExpression "." StateField

I'm not even sure if this functionality should exist, but the problem was that the error came up as a function call to a method that did not exist!

Anyway, the dql would be something like this:

SELECT a FROM Address a WHERE a.User = :user

where user is a user object (ManyToOne)
If this functionality were to exist, I guess it could simply replicate this dql:

SELECT a FROM Address a WHERE a.User.id = :userId

The reason this was a problem for us at the time, was that we did not know that the second option was available, and since it is not possible to define column names AND relations, we couldn't figure out how to run a simple query that included a relation. It turned out that I figured out that option 2 (above) was valid, and that's the route we went down

Of course we had no reason to beleive that another option existed, since the only error presented to us was a PHP fatal error telling us that the function Doctrine\ORM\Query\SqlWalker::walkSingleValuedAssociationPathExpression() did not exist.

Either this could be implemented, or an exception thrown, rather than Doctrine trying to trigger a function that has not been implemented :o)

Edit: I should have used a ManyToOne example since that was the code we used

@Guilherme:

SELECT u FROM \MyProject\Entities\User u WHERE u.Group = 1;

That should never be valid.

SELECT u FROM \MyProject\Entities\User u WHERE u.Group = ?1
$q->setParameter(1, array(1, 2));

That should also never be valid as it makes no sense. You should pass the object, not the ids.

$q->setParameter(1, $group);

But I think we're on the same page. Its just not as easy as it looks. Supporting this has lots of consequences and edge-cases.

@Russ:

I didnt even know this worked:

SELECT a FROM Address a WHERE a.User.id = :userId

Because generally, we decided against supporting nested paths as it leads to additional complexity for parsing and sql construction.
The above is also the same as this:

"SELECT a FROM Address a JOIN a.User u WHERE u.id = :userId

Which I would recommend since it makes the join explicit.

@Roman: My only issue with the join suggestion is that from a performance and sql perspective it seems unnecessary.

If I want to do:

SELECT a FROM Address a WHERE a.user_id = :userId

I can't, because there is no user_id property (and as far as I know there can't be) so we have to refer to the object, so if using User.id avoids the join I'm all for it. However, like you say, if there is some other magic going on behind that then you may be right that it's best avoided.

So if all I need is an Address object (or array) and I want to look it up based on the owning User object, I want my sql to turn out as:

 
SELECT * FROM address a WHERE a.user_id = 1

In this case one extra join to make things "clean" is no biggy, but imagine in more complex cases with several relations when I know all the foreign key IDs involved it may introduce crippling overhead if we have to join all the necessary tables to be able to select based on the foreign ids.

Do you see where I'm coming from?

@Russ: Yes, absolutely and I'm with you.

I will explain how it should be here, so that I dont have to remember later.

DQL: SELECT a FROM Address a WHERE a.user = :user

Now, the SQL depends on where the "owning side" (from doctrine's point fo view) is, in other words which side holds the FK.
If the address table holds the FK (user_id) the SQL should be like you have shown:

SQL: SELECT a FROM addresses a WHERE a.user_id = ?

However, if the foreign key is on the other side, theres no way around the join and the SQL should be:

SQL: SELECT a FROM addresses a INNER JOIN users u ON a.id = u.address_id WHERE u.id = ?

Of course, this should only be possible for X-to-one associations.

You are right about that there should not be a user_id property or other properties for foreign keys in your objects. I mean, you could do that, it would be treated as a normal field, but if you do that, you leak a part of the relational schema into your object model, because your objects have associations, not foreign keys. Foreign keys are how the relational database handles associations, not your objects.

I hope that makes sense! I will certainly try to work on this.

@Russ: Meanwhile you could use a NativeQuery to achieve the same.

Take a look at: http://www.doctrine-project.org/documentation/manual/2_0/en/native-sql

This concept is extremely powerful as you can use any SQL you want. It is important to understand that what comes out at the end when DQL is processed is = SQL + ResultSetMapping!

In your specific case, it could look like this:

// in some Repository/DAO class
public function findAddressForUser($userId) {
    $rsm = new ResultSetMapping;
    $rsm->addEntityResult('My\Domain\Address', 'a');
    $rsm->addFieldResult('a', 'id', 'id');
    $rsm->addFieldResult('a', 'city', 'city');
    // ... more fields if there are any

    $query = $this->_em->createNativeQuery('SELECT id, city FROM addresses WHERE user_id = ?', $rsm);
    $query->setParameter(1, $userId);

    return $query->getSingleResult();
}

It can be a bit tricky at times to get the ResultSetMapping right, but once you got the hang of it you can use the fanciest database-specific SQL and let Doctrine turn it into objects (or arrays or scalars) for you.

Of course NativeQuery can help for us that are developing on Alpha version.

Please, would you be so kind and add more examples, especially for one to one and one to many references and how to add these objects to EM in scope of work as UNCHANGED objects.

@Roman:

DQL: SELECT a FROM Address a WHERE a.user IS NULL
DQL: SELECT a FROM Address a WHERE a.user IS NOT NULL

should also be supported, as an equivalent of checking "collection IS [NOT] EMPTY"

The problem is that SqlWalker::walkPathExpression() has to return a single column sql string otherwise it breaks behaviour on related walk functions like:

• walkNullComparisonExpression
• walkInExpression
• walkAggregateExpression
• walkGroupByItem
• walkOrderByItem

This restricts the behaviour to be only allowed if the single valued association has a single column primary key, otherwise an exception should be thrown.

If this is agreed behaviour, a DQL statement like Guilherme mentioned, might become feasible under the assumptions:

SELECT u FROM \MyProject\Entities\User u WHERE u.Group = 1;

No, a query like this should not be allowed. WHERE u.Group = 1 is just wrong. See my earlier comments.

This should work now.

I.e.

select u from User u where u.address = ?1
...
->setParameter(1, $em->getReference('Address', $addressId))
...
SQL: SELECT .... FROM users WHERE user_id = ?

Not that for simplicity this only works if User is the owning side (has the FK in the database)

Otherwise you need a join anyway and then its better if you do that join explicitly:

select a from Address a join a.user u where u.id = ?1
...
->setParameter(1, $userId)
...

The SQL in my first example should of course be:

SQL: SELECT .... FROM users WHERE address_id = ?

Similarly, the examples from Benjamin for null checking should now work as well, provided in that example Address is the owning side.

DQL: SELECT a FROM Address a WHERE a.user IS NULL
=> SQL: SELECT ... FROM addresses a WHERE user_id IS NULL

DQL: SELECT a FROM Address a WHERE a.user IS NOT NULL
=> SQL: SELECT ... FROM addresses WHERE user_id IS NOT NULL