Join Ordering in Postgresql with QueryBuilder

[madflow]

This has been discussed here #2003 but since this only partially resolved and I do not want to comment on old issues:

I am migrating "raw" sql to the QueryBuilder Syntax. Here is a fiddle (quick and dirty and just the core parts of the sql to be migrated - just for demonstrating purposes):

http://sqlfiddle.com/#!15/b6231/7

Here is the QueryBuilder configuration:

$qb
    ->addSelect('COUNT(*)')

    ->from('nodes', 'nodes')

    ->join('nodes', 'structures', 'structures', 'nodes.structure_id=structures.structure_id')

    ->leftJoin('structures', 'project_users', 'project_users', 'structures.project_id=project_users.project_id')
    ->leftJoin('nodes', 'roles_project_users_nodes', 'roles_project_users_nodes',
        'nodes.node_id=roles_project_users_nodes.node_id AND roles_project_users_nodes.project_user_id=project_users.project_user_id');

In the first left join I am defining the relation "project_users". The second join uses this relation.

But the QueryBuilder does this:

SELECT Count(*)
FROM   nodes nodes
       INNER JOIN structures structures
               ON nodes.structure_id = structures.structure_id
       LEFT JOIN roles_project_users_nodes roles_project_users_nodes
              ON nodes.node_id = roles_project_users_nodes.node_id
                 AND roles_project_users_nodes.project_user_id =
                     project_users.project_user_id
       LEFT JOIN project_users project_users
              ON structures.project_id = project_users.project_id  

And the query will fail with: ERROR: missing FROM-clause entry for table "project_users"

• Can this be considered a bug and you will accept pull requests?
• Is there anyway I can change the way the joins are ordered?

[Ocramius]

What would the correct SQL look like?

On 8 May 2017 4:14 p.m., "madflow" [email protected] wrote:

This has been discussed here #2003 https://github.com/doctrine/dbal/issues/2003 but since this only partially resolved and I do not want to comment on old issues:

I am migrating "raw" sql to the QueryBuilder Syntax. Here is a fiddle (quick and dirty and just the core parts of the sql to be migrated - just for demonstrating purposes):

http://sqlfiddle.com/#!15/b6231/7

Here is the QueryBuilder configuration:

$qb ->addSelect('COUNT(*)') ->from('nodes', 'nodes') ->join('nodes', 'structures', 'structures', 'nodes.structure_id=structures.structure_id') ->leftJoin('structures', 'project_users', 'project_users', 'structures.project_id=project_users.project_id') ->leftJoin('nodes', 'roles_project_users_nodes', 'roles_project_users_nodes', 'nodes.node_id=roles_project_users_nodes.node_id AND roles_project_users_nodes.project_user_id=project_users.project_user_id');

In the first left join I am defining the relation "project_users". The second join uses this relation.

But the QueryBuilder does this:

SELECT Count(*) FROM nodes nodes INNER JOIN structures structures ON nodes.structure_id = structures.structure_id LEFT JOIN roles_project_users_nodes roles_project_users_nodes ON nodes.node_id = roles_project_users_nodes.node_id AND roles_project_users_nodes.project_user_id = project_users.project_user_id LEFT JOIN project_users project_users ON structures.project_id = project_users.project_id

And the query will fail with: ERROR: missing FROM-clause entry for table "project_users"

• Can this be considered a bug and you will accept pull requests?
• Is there anyway I can change the way the joins are ordered?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/doctrine/dbal/issues/2719, or mute the thread https://github.com/notifications/unsubscribe-auth/AAJakLhc51jd5uv5MyGIZ0jrQVxpUxEnks5r3yNfgaJpZM4NUAiK .

[madflow]

Sorry - I tried to document this in the sql fiddle:

When I used the QueryBuilder, I assumed that the joins would be in the order how I defined them - but the QueryBuilder does some ordering of its own.

The correct query (when I could decide how the ordering takes place) is:

SELECT Count(*)
FROM   nodes nodes
       INNER JOIN structures structures
               ON nodes.structure_id = structures.structure_id
       LEFT JOIN project_users project_users
              ON structures.project_id = project_users.project_id 
       LEFT JOIN roles_project_users_nodes roles_project_users_nodes
              ON nodes.node_id = roles_project_users_nodes.node_id
                 AND roles_project_users_nodes.project_user_id =
                     project_users.project_user_id

The two LEFT JOIN's are just swapped.

This has been discussed here: https://github.com/doctrine/dbal/pull/679#issuecomment-55940754 - but it seemed the discussion "staled" at some point.

[madflow]

Closing - since we will find an "inhouse" solution for this.

[Ocramius]

Reopened: this is still valid for the doctrine user base.

[chrisguitarguy]

Looks like this happens because QueryBuilder::getSQLForJoins works based on the $fromAlias -- the first parameter passed to the various join methods.

The process gets kicked off with the alias with the alias from the select clause.

In the example, since nodes is in from, the joins that reference nodes in the frm alias are rendered first. If you need to depend on aliases created by other joins, then it seems using those as the join alias should solve the problem.

$qb
  // ...
  // use `project_users` as the fromAlias so its put after `project_users` is defined!
  ->leftJoin('project_users', 'roles_project_users_nodes', 'roles_project_users_nodes',
        'nodes.node_id=roles_project_users_nodes.node_id AND roles_project_users_nodes.project_user_id=project_users.project_user_id');

Seems like this is a question of whether or not DBAL should support multiple from aliases in joins/query builders.

[simPod]

Just stumbled upon this as well. The behaviour is a bit confusing and might unexpectedly generate invalid SQL if not tested 🤔 I suppose common user would expect result SQL to have the same order of joins as was the order of called called join functions on querybuilder.

[Nemoden]

Just stumbled upon this. Highly confusing, joins should really follow the order they are defined in.