JOIN inaccuracies

[Wintereise]

Test code:

            $permissionName = 'resource.create';
             $target = '2'; //dummy values

            $init = new PermissionAssociationCollection();

            $init->join(new Permission(), 'INNER', 'p')
                ->on('m.permission_id', [ 'p.id' ]);

            $init->join(new Role(), 'INNER', 'r')
                ->on('m.role_id', [ 'r.id' ]);

            $init->join(new RoleAssociation(), 'INNER', 'ra')
                ->on('r.id', [ 'ra.role_id' ]);

            $init->where()
                ->equal('ra.user_id', $this->userId) // 3
                ->equal('r.tenant_id', $this->tenantId) // 1
                ->equal('p.name', $permissionName);

             $init->where()->in('m.target', [ $target, '*' ]);

Since I'm specifically telling it what to join on, I expect those to be followed, however...

It generates this (which doesn't work as intended):

SELECT m.`id`, m.`permission_id`, m.`role_id`, m.`user_id`, m.`target`, m.`created_at`, m.`updated_at`, p.id AS p_id, p.tenant_id AS p_tenant_id, p.name AS p_name, p.created_at AS p_created_at, p.updated_at AS p_updated_at, r.id AS r_id, r.tenant_id AS r_tenant_id, r.config AS r_config, r.name AS r_name, r.created_at AS r_created_at, r.updated_at AS r_updated_at, ra.id AS ra_id, ra.role_id AS ra_role_id, ra.user_id AS ra_user_id, ra.created_at AS ra_created_at, ra.updated_at AS ra_updated_at FROM permission_associations AS m INNER JOIN permissions AS p ON (m.permission_id = p.id AND m.permission_id) INNER JOIN roles AS r ON (m.role_id) INNER JOIN role_associations AS ra ON (r.id) WHERE ra.user_id = 3 AND r.tenant_id = 1 AND p.name = 'resource.create' AND m.target IN ('2','*')

when the expected output is (which does work as expected - note the join constraints added back on):

SELECT m.`id`, m.`permission_id`, m.`role_id`, m.`user_id`, m.`target`, m.`created_at`, m.`updated_at`, p.id AS p_id, p.tenant_id AS p_tenant_id, p.name AS p_name, p.created_at AS p_created_at, p.updated_at AS p_updated_at, r.id AS r_id, r.tenant_id AS r_tenant_id, r.config AS r_config, r.name AS r_name, r.created_at AS r_created_at, r.updated_at AS r_updated_at, ra.id AS ra_id, ra.role_id AS ra_role_id, ra.user_id AS ra_user_id, ra.created_at AS ra_created_at, ra.updated_at AS ra_updated_at FROM permission_associations AS m INNER JOIN permissions AS p ON (m.permission_id = p.id AND m.permission_id) INNER JOIN roles AS r ON (m.role_id = r.id) INNER JOIN role_associations AS ra ON (ra.role_id = r.id) WHERE ra.user_id = 3 AND r.tenant_id = 1 AND p.name = 'resource.create' AND m.target IN ('2','*')

What am I doing wrong here? I can PM you the Schema files or a dump of the SQL schema if you need it.

[c9s]

Hi @Wintereise

sorry for the late reply. I was too busy in the last week

[c9s]

You need to join with the equal syntax:

$init->join(new Role(), 'INNER', 'r')
                ->on()->equal('r.id',  ['r.id']);

[c9s]

When you use on() expression, it needs to be a SQL clause:

examples/books/Model/AuthorBase.php
251:        $collection->joinTable('author_books', 'j', 'INNER')
252-           ->on("j.book_id = {$collection->getAlias()}.id");

Or you can ignore the expression for on(), and use:

examples/books/Tests/AuthorBookTest.php
347:        $books->join('author_books')
348-            ->as('ab')
349-                ->on()
350-                    ->equal('ab.book_id', array('m.id'));

[c9s]

@Wintereise does this solve your problem?

[Wintereise]

Hi @c9s,

I haven't had a moment to try this yet, I'll do that and get back to you.

Many thanks for getting back to me.