{"action":"opened","issue":{"url":"https://api.github.com/repos/doctrine/phpcr-odm/issues/658","labels_url":"https://api.github.com/repos/doctrine/phpcr-odm/issues/658/labels{/name}","comments_url":"https://api.github.com/repos/doctrine/phpcr-odm/issues/658/comments","events_url":"https://api.github.com/repos/doctrine/phpcr-odm/issues/658/events","html_url":"https://github.com/doctrine/phpcr-odm/issues/658","id":105076745,"number":658,"title":"Query builder seems unable to produce a fully working left join","user":{"login":"sarcher","id":157295,"avatar_url":"https://avatars.githubusercontent.com/u/157295?v=3","gravatar_id":"","url":"https://api.github.com/users/sarcher","html_url":"https://github.com/sarcher","followers_url":"https://api.github.com/users/sarcher/followers","following_url":"https://api.github.com/users/sarcher/following{/other_user}","gists_url":"https://api.github.com/users/sarcher/gists{/gist_id}","starred_url":"https://api.github.com/users/sarcher/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sarcher/subscriptions","organizations_url":"https://api.github.com/users/sarcher/orgs","repos_url":"https://api.github.com/users/sarcher/repos","events_url":"https://api.github.com/users/sarcher/events{/privacy}","received_events_url":"https://api.github.com/users/sarcher/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-09-06T07:24:57Z","updated_at":"2015-09-06T07:24:57Z","closed_at":null,"body":"I am attempting to use a `LEFT JOIN` behavior on child documents, but it seems to be impossible based on what I've seen.\r\n\r\nI have the following situation:\r\n\r\n* Tree of documents of the class `Product`\r\n* Documents _may_ have one or more children of the class `ProductDetail`\r\n* Attempting to design a search query which includes data from these details\r\n\r\nTake the following query builder as an example:\r\n\r\n```php\r\n$searchTerm = '12345';\r\n\r\n$query = $queryBuilder\r\n    ->fromDocument('Path\\To\\Product', 'p')\r\n    ->addJoinLeftOuter()\r\n        ->right()->document('Path\\To\\ProductDetail', 'pd')->end()\r\n        ->condition()->child('pd', 'p')->end()\r\n    ->end();\r\n    ->andWhere()\r\n        ->orX()\r\n            ->like()->localName('p')->literal('%' . $searchTerm . '%')->end();\r\n            ->andX()\r\n                ->eq()->field('pd.type')->literal('title')->end()\r\n                ->fullTextSearch('pd.content', $searchTerm)\r\n            ->end()\r\n        ->end()\r\n    ->end()\r\n    ->getQuery();\r\n```\r\n\r\nNow, PHPCR-ODM produces a JCR-SQL2 query like:\r\n\r\n```sql\r\nSELECT * \r\nFROM [nt:unstructured] AS p \r\nLEFT OUTER JOIN [nt:unstructured] AS pd ON ISCHILDNODE(pd, p) \r\nWHERE (((LOCALNAME(p) LIKE '%12345%' OR (pd.type = 'title' AND CONTAINS(pd.content, '12345'))) \r\n  AND (p.[phpcr:class] = 'Path\\To\\Product' OR p.[phpcr:classparents] = 'Path\\To\\Product'))\r\n  AND (pd.[phpcr:class] = 'Path\\To\\ProductDetail' OR pd.[phpcr:classparents] = 'Path\\To\\ProductDetail'))\r\n```\r\n\r\nThis all works as expected, _if and only if_ the `Product` nodes contain at least one `ProductDetail` child -- in other words, it seems to behave as an `INNER JOIN`. Any `Product` node that has no such child will not be included in the results of the query, because of two things:\r\n\r\n1. The query is specifying `SELECT *` rather than `SELECT p.*`, which is requiring a result for the `pd` alias\r\n2. The query is appending an `AND` clause at the end, which will only match if the alias `pd` is present and of the appropriate class\r\n\r\nThe query that I actually want is this:\r\n\r\n```sql\r\nSELECT p.* \r\nFROM [nt:unstructured] AS p \r\nLEFT OUTER JOIN [nt:unstructured] AS pd ON ISCHILDNODE(pd, p) \r\nWHERE (((LOCALNAME(p) LIKE '%12345%' OR (pd.type = 'title' AND CONTAINS(pd.content, '12345'))) \r\n  AND (p.[phpcr:class] = 'Path\\To\\Product' OR p.[phpcr:classparents] = 'Path\\To\\Product')))\r\n```\r\n\r\nThis works as one would expect a `LEFT JOIN` to, delivering results whose node names match \"12345\" regardless of whether or not they contain one or more `ProductDetail` children.\r\n\r\nWhat I cannot figure out is if it is possible to get to my desired query; is it? This _seems_ to be a bug, but perhaps I am misunderstanding the intent here. \r\n\r\nIf this is intended, is there a workaround to achieve what I am trying to do? I realize I could just pass the JCR-SQL2 query to the session (my environment uses Jackrabbit) and then manually invoke hydration, but that seems a bit ugly. :)"}}