How to union when using matching

Need Help
1

// users belongsToMany users
// getting friends post

$friendsPosts= $this->Posts->find('all')
        ->contain(['Users', 'Languages', 'PostStates'])
        ->matching('Users.Dusers', function ($q) {
            return $q->where(['Dusers.id' => $this->Auth->user('id')]);
        });

// getting public post

$posts= $this->Posts->find('all')
        ->where(['Posts.post_state_id' => 3])
        ->contain(['Users', 'Languages', 'PostStates']);

$posts->union($friendsPosts);
dd($posts->toArray());

//error message: The used SELECT statements have a different number of columns

2

The problem is, as you can see it from the error message, that the 2 queries has fifferent number of columns.

Your contain calls are similar, so you can be sure matching is the problemtaic part. Matching adds extra fields to the query, so that is why the first query has more fileds selected.

If you change matching to innerJoinWith you will get the same records, but no fileds wil be added to the select.

1 Like
3

Thanks for your reply
I tried as you mentioned and result same.

  $friendsPosts= $this->Posts->find('all')
                ->contain(['Users', 'Languages', 'PostStates'])
                ->innerJoinWith('Users.Dusers', function ($q) {
                    return $q->where(['Dusers.id' => $this->Auth->user('id')]);
                });

        $posts= $this->Posts->find('all')
                ->where(['Posts.post_state_id' => 3])
                ->contain(['Users', 'Languages', 'PostStates']);

    `$posts->union($friendsPosts);`

I checked

$result = array_diff_key($friendsPosts->toArray(), $posts->toArray());
print_r($result);
die;

Result: Array()

4

Do you get the same error message?

5

Yes.I didn’t see any differences between two arrays. But still getting the same error message “The used SELECT statements have a different number of columns.”

6

Hm.

Check this

$friendsPosts= $this->Posts->find('all')
    ->contain(['Users', 'Languages', 'PostStates'])
    ->innerJoinWith('Users.Dusers', function ($q) {
             return $q->where(['Dusers.id' => $this->Auth->user('id')]);
    });
debug($friendsPosts->sql());

$posts= $this->Posts->find('all')
    ->where(['Posts.post_state_id' => 3])
    ->contain(['Users', 'Languages', 'PostStates']);
debug($posts->sql());

It may help you to find out why you have different numbers of fileds in the two selects.

1 Like
7

Here is full action code. I was changed little for readability. I use CakeDC users plugin. Union works without containing Users.

$this->Translations->Users->addAssociations([
            'belongsToMany' => [
                'Dusers' => ['className' => 'CakeDC/Users.Users']
            ]
]);
$friendsTranslations = $this->Translations->find('all')
        ->where(['Translations.post_state_id' => 2])
        ->contain(['Users'=>['Dusers'],'Posts' => ['Languages'], 'Languages', 'PostStates'])
        ->innerJoinWith('Users.Dusers', function ($q) {
    return $q->where(['Dusers.id' => $this->Auth->user('id')]);
});

debug($friendsTranslations->sql());
$translations = $this->Translations->find('all')
        ->where(['Translations.post_state_id' => 3])
        ->contain(['Users' => ['Dusers'], 'Posts' => ['Languages'], 'Languages', 'PostStates']);

// $translations->union($friendsTranslations);

dd($translations->sql());

Here is friends translations sql code

SELECT translations.id            AS `Translations__id`, 
       translations.post_id       AS `Translations__post_id`, 
       translations.vote_count    AS `Translations__vote_count`, 
       translations.language_id   AS `Translations__language_id`, 
       translations.post_state_id AS `Translations__post_state_id`, 
       translations.hits          AS `Translations__hits`, 
       translations.progress      AS `Translations__progress`, 
       translations.user_id       AS `Translations__user_id`, 
       translations.created       AS `Translations__created`, 
       translations.modified      AS `Translations__modified`, 
       posts.id                   AS `Posts__id`, 
       posts.language_id          AS `Posts__language_id`, 
       posts.title                AS `Posts__title`, 
       posts.text                 AS `Posts__text`, 
       posts.post_state_id        AS `Posts__post_state_id`, 
       posts.hits                 AS `Posts__hits`, 
       posts.vote_count           AS `Posts__vote_count`, 
       posts.user_id              AS `Posts__user_id`, 
       posts.created              AS `Posts__created`, 
       posts.modified             AS `Posts__modified`, 
       languages.id               AS `Languages__id`, 
       languages.name             AS `Languages__name`, 
       languages.code             AS `Languages__code`, 
       PostStates.id              AS `PostStates__id`, 
       PostStates.name            AS `PostStates__name`, 
       PostStates.icon            AS `PostStates__icon` 
FROM   translations Translations 
       INNER JOIN users Users 
               ON users.id = ( translations.user_id ) 
       INNER JOIN users_users UsersUsers 
               ON users.id = ( UsersUsers.user_id ) 
       INNER JOIN users Dusers 
               ON ( Dusers.id = :c0 
                    AND Dusers.id = ( UsersUsers.duser_id ) ) 
       INNER JOIN posts Posts 
               ON posts.id = ( translations.post_id ) 
       LEFT JOIN languages Languages 
              ON languages.id = ( translations.language_id ) 
       LEFT JOIN post_states PostStates 
              ON PostStates.id = ( translations.post_state_id ) 
WHERE  translations.post_state_id = :c1

Here is translations sql

SELECT translations.id            AS `Translations__id`, 
       translations.post_id       AS `Translations__post_id`, 
       translations.vote_count    AS `Translations__vote_count`, 
       translations.language_id   AS `Translations__language_id`, 
       translations.post_state_id AS `Translations__post_state_id`, 
       translations.hits          AS `Translations__hits`, 
       translations.progress      AS `Translations__progress`, 
       translations.user_id       AS `Translations__user_id`, 
       translations.created       AS `Translations__created`, 
       translations.modified      AS `Translations__modified`, 
       users.id                   AS `Users__id`, 
       users.username             AS `Users__username`, 
       users.email                AS `Users__email`, 
       users.friends              AS `Users__friends`, 
       users.bio                  AS `Users__bio`, 
       users.text                 AS `Users__text`, 
       users.photo                AS `Users__photo`, 
       users.password             AS `Users__password`, 
       users.star_count           AS `Users__star_count`, 
       users.first_name           AS `Users__first_name`, 
       users.last_name            AS `Users__last_name`, 
       users.phone                AS `Users__phone`, 
       users.token                AS `Users__token`, 
       users.token_expires        AS `Users__token_expires`, 
       users.api_token            AS `Users__api_token`, 
       users.activation_date      AS `Users__activation_date`, 
       users.tos_date             AS `Users__tos_date`, 
       users.active               AS `Users__active`, 
       users.is_superuser         AS `Users__is_superuser`, 
       users.role                 AS `Users__role`, 
       users.created              AS `Users__created`, 
       users.modified             AS `Users__modified`, 
       posts.id                   AS `Posts__id`, 
       posts.language_id          AS `Posts__language_id`, 
       posts.title                AS `Posts__title`, 
       posts.text                 AS `Posts__text`, 
       posts.post_state_id        AS `Posts__post_state_id`, 
       posts.hits                 AS `Posts__hits`, 
       posts.vote_count           AS `Posts__vote_count`, 
       posts.user_id              AS `Posts__user_id`, 
       posts.created              AS `Posts__created`, 
       posts.modified             AS `Posts__modified`, 
       languages.id               AS `Languages__id`, 
       languages.name             AS `Languages__name`, 
       languages.code             AS `Languages__code`, 
       PostStates.id              AS `PostStates__id`, 
       PostStates.name            AS `PostStates__name`, 
       PostStates.icon            AS `PostStates__icon` 
FROM   translations Translations 
       LEFT JOIN users Users 
              ON users.id = ( translations.user_id ) 
       INNER JOIN posts Posts 
               ON posts.id = ( translations.post_id ) 
       LEFT JOIN languages Languages 
              ON languages.id = ( translations.language_id ) 
       LEFT JOIN post_states PostStates 
              ON PostStates.id = ( translations.post_state_id ) 
WHERE  translations.post_state_id = :c0
8

My final goal is list friends post with public and own posts like Facebook wall.
> Detailed post waiting review.

9

As you can see your first sql dost not select users. It is strange, as it is there in the contain call. It may be filtered out by innerjoinwith call. So in this case your original matching call supposed to run without an error. Try to get the debug the sql in that case also.

10

Now it looks dusers, users_users added.
matching results

SELECT translations.id            AS `Translations__id`, 
       translations.post_id       AS `Translations__post_id`, 
       translations.vote_count    AS `Translations__vote_count`, 
       translations.language_id   AS `Translations__language_id`, 
       translations.post_state_id AS `Translations__post_state_id`, 
       translations.hits          AS `Translations__hits`, 
       translations.progress      AS `Translations__progress`, 
       translations.user_id       AS `Translations__user_id`, 
       translations.created       AS `Translations__created`, 
       translations.modified      AS `Translations__modified`, 
       users.id                   AS `Users__id`, 
       users.username             AS `Users__username`, 
       users.email                AS `Users__email`, 
       users.friends              AS `Users__friends`, 
       users.bio                  AS `Users__bio`, 
       users.text                 AS `Users__text`, 
       users.photo                AS `Users__photo`, 
       users.password             AS `Users__password`, 
       users.star_count           AS `Users__star_count`, 
       users.first_name           AS `Users__first_name`, 
       users.last_name            AS `Users__last_name`, 
       users.phone                AS `Users__phone`, 
       users.token                AS `Users__token`, 
       users.token_expires        AS `Users__token_expires`, 
       users.api_token            AS `Users__api_token`, 
       users.activation_date      AS `Users__activation_date`, 
       users.tos_date             AS `Users__tos_date`, 
       users.active               AS `Users__active`, 
       users.is_superuser         AS `Users__is_superuser`, 
       users.role                 AS `Users__role`, 
       users.created              AS `Users__created`, 
       users.modified             AS `Users__modified`, 
       UsersUsers.user_id         AS `UsersUsers__user_id`, 
       UsersUsers.duser_id        AS `UsersUsers__duser_id`, 
       UsersUsers.created         AS `UsersUsers__created`, 
       UsersUsers.status          AS `UsersUsers__status`, 
       Dusers.id                  AS `Dusers__id`, 
       Dusers.username            AS `Dusers__username`, 
       Dusers.email               AS `Dusers__email`, 
       Dusers.friends             AS `Dusers__friends`, 
       Dusers.bio                 AS `Dusers__bio`, 
       Dusers.text                AS `Dusers__text`, 
       Dusers.photo               AS `Dusers__photo`, 
       Dusers.password            AS `Dusers__password`, 
       Dusers.star_count          AS `Dusers__star_count`, 
       Dusers.first_name          AS `Dusers__first_name`, 
       Dusers.last_name           AS `Dusers__last_name`, 
       Dusers.phone               AS `Dusers__phone`, 
       Dusers.token               AS `Dusers__token`, 
       Dusers.token_expires       AS `Dusers__token_expires`, 
       Dusers.api_token           AS `Dusers__api_token`, 
       Dusers.activation_date     AS `Dusers__activation_date`, 
       Dusers.tos_date            AS `Dusers__tos_date`, 
       Dusers.active              AS `Dusers__active`, 
       Dusers.is_superuser        AS `Dusers__is_superuser`, 
       Dusers.role                AS `Dusers__role`, 
       Dusers.created             AS `Dusers__created`, 
       Dusers.modified            AS `Dusers__modified`, 
       posts.id                   AS `Posts__id`, 
       posts.language_id          AS `Posts__language_id`, 
       posts.title                AS `Posts__title`, 
       posts.text                 AS `Posts__text`, 
       posts.post_state_id        AS `Posts__post_state_id`, 
       posts.hits                 AS `Posts__hits`, 
       posts.vote_count           AS `Posts__vote_count`, 
       posts.user_id              AS `Posts__user_id`, 
       posts.created              AS `Posts__created`, 
       posts.modified             AS `Posts__modified`, 
       languages.id               AS `Languages__id`, 
       languages.name             AS `Languages__name`, 
       languages.code             AS `Languages__code`, 
       PostStates.id              AS `PostStates__id`, 
       PostStates.name            AS `PostStates__name`, 
       PostStates.icon            AS `PostStates__icon` 
FROM   translations Translations 
       INNER JOIN users Users 
               ON users.id = ( translations.user_id ) 
       INNER JOIN users_users UsersUsers 
               ON users.id = ( UsersUsers.user_id ) 
       INNER JOIN users Dusers 
               ON ( Dusers.id = :c0 
                    AND Dusers.id = ( UsersUsers.duser_id ) ) 
       INNER JOIN posts Posts 
               ON posts.id = ( translations.post_id ) 
       LEFT JOIN languages Languages 
              ON languages.id = ( translations.language_id ) 
       LEFT JOIN post_states PostStates 
              ON PostStates.id = ( translations.post_state_id ) 
WHERE  translations.post_state_id = :c1
11

I removed dusers,usersusers selections in raw SQL and it works but still have a question that how to do CakePHP way.

   SELECT translations.id            AS `Translations__id`, 
       translations.post_id       AS `Translations__post_id`, 
       translations.vote_count    AS `Translations__vote_count`, 
       translations.language_id   AS `Translations__language_id`, 
       translations.post_state_id AS `Translations__post_state_id`, 
       translations.hits          AS `Translations__hits`, 
       translations.progress      AS `Translations__progress`, 
       translations.user_id       AS `Translations__user_id`, 
       translations.created       AS `Translations__created`, 
       translations.modified      AS `Translations__modified`, 
       users.id                   AS `Users__id`, 
       users.username             AS `Users__username`, 
       users.email                AS `Users__email`, 
       users.friends              AS `Users__friends`, 
       users.bio                  AS `Users__bio`, 
       users.text                 AS `Users__text`, 
       users.photo                AS `Users__photo`, 
       users.password             AS `Users__password`, 
       users.star_count           AS `Users__star_count`, 
       users.first_name           AS `Users__first_name`, 
       users.last_name            AS `Users__last_name`, 
       users.phone                AS `Users__phone`, 
       users.token                AS `Users__token`, 
       users.token_expires        AS `Users__token_expires`, 
       users.api_token            AS `Users__api_token`, 
       users.activation_date      AS `Users__activation_date`, 
       users.tos_date             AS `Users__tos_date`, 
       users.active               AS `Users__active`, 
       users.is_superuser         AS `Users__is_superuser`, 
       users.role                 AS `Users__role`, 
       users.created              AS `Users__created`, 
       users.modified             AS `Users__modified`, 
       posts.id                   AS `Posts__id`, 
       posts.language_id          AS `Posts__language_id`, 
       posts.title                AS `Posts__title`, 
       posts.text                 AS `Posts__text`, 
       posts.post_state_id        AS `Posts__post_state_id`, 
       posts.hits                 AS `Posts__hits`, 
       posts.vote_count           AS `Posts__vote_count`, 
       posts.user_id              AS `Posts__user_id`, 
       posts.created              AS `Posts__created`, 
       posts.modified             AS `Posts__modified`, 
       languages.id               AS `Languages__id`, 
       languages.name             AS `Languages__name`, 
       languages.code             AS `Languages__code`, 
       PostStates.id              AS `PostStates__id`, 
       PostStates.name            AS `PostStates__name`, 
       PostStates.icon            AS `PostStates__icon` 
FROM   translations Translations 
       INNER JOIN users Users 
               ON users.id = ( translations.user_id ) 
       INNER JOIN users_users UsersUsers 
               ON users.id = ( UsersUsers.user_id ) 
       INNER JOIN users Dusers 
               ON ( Dusers.id = '97b99809-f80f-4bd9-9cdc-a643038c85be' 
                    AND Dusers.id = ( UsersUsers.duser_id ) ) 
       INNER JOIN posts Posts 
               ON posts.id = ( translations.post_id ) 
       LEFT JOIN languages Languages 
              ON languages.id = ( translations.language_id ) 
       LEFT JOIN post_states PostStates 
              ON PostStates.id = ( translations.post_state_id ) 
WHERE  translations.post_state_id = 2 
UNION 
SELECT translations.id            AS `Translations__id`, 
        translations.post_id       AS `Translations__post_id`, 
        translations.vote_count    AS `Translations__vote_count`, 
        translations.language_id   AS `Translations__language_id`, 
        translations.post_state_id AS `Translations__post_state_id`, 
        translations.hits          AS `Translations__hits`, 
        translations.progress      AS `Translations__progress`, 
        translations.user_id       AS `Translations__user_id`, 
        translations.created       AS `Translations__created`, 
        translations.modified      AS `Translations__modified`, 
        users.id                   AS `Users__id`, 
        users.username             AS `Users__username`, 
        users.email                AS `Users__email`, 
        users.friends              AS `Users__friends`, 
        users.bio                  AS `Users__bio`, 
        users.text                 AS `Users__text`, 
        users.photo                AS `Users__photo`, 
        users.password             AS `Users__password`, 
        users.star_count           AS `Users__star_count`, 
        users.first_name           AS `Users__first_name`, 
        users.last_name            AS `Users__last_name`, 
        users.phone                AS `Users__phone`, 
        users.token                AS `Users__token`, 
        users.token_expires        AS `Users__token_expires`, 
        users.api_token            AS `Users__api_token`, 
        users.activation_date      AS `Users__activation_date`, 
        users.tos_date             AS `Users__tos_date`, 
        users.active               AS `Users__active`, 
        users.is_superuser         AS `Users__is_superuser`, 
        users.role                 AS `Users__role`, 
        users.created              AS `Users__created`, 
        users.modified             AS `Users__modified`, 
        posts.id                   AS `Posts__id`, 
        posts.language_id          AS `Posts__language_id`, 
        posts.title                AS `Posts__title`, 
        posts.text                 AS `Posts__text`, 
        posts.post_state_id        AS `Posts__post_state_id`, 
        posts.hits                 AS `Posts__hits`, 
        posts.vote_count           AS `Posts__vote_count`, 
        posts.user_id              AS `Posts__user_id`, 
        posts.created              AS `Posts__created`, 
        posts.modified             AS `Posts__modified`, 
        languages.id               AS `Languages__id`, 
        languages.name             AS `Languages__name`, 
        languages.code             AS `Languages__code`, 
        PostStates.id              AS `PostStates__id`, 
        PostStates.name            AS `PostStates__name`, 
        PostStates.icon            AS `PostStates__icon` 
 FROM   translations Translations 
        LEFT JOIN users Users 
               ON users.id = ( translations.user_id ) 
        INNER JOIN posts Posts 
                ON posts.id = ( translations.post_id ) 
        LEFT JOIN languages Languages 
               ON languages.id = ( translations.language_id ) 
        LEFT JOIN post_states PostStates 
               ON PostStates.id = ( translations.post_state_id ) 
 WHERE  translations.post_state_id = 3
12

Try to call the same addAssociations() just right before $translations = $this->Translations->find('all')->...

On the other hand you may consider to use translate behaviour: https://book.cakephp.org/3.0/en/orm/behaviors/translate.html

13

Yes, I did result same.
Here is my solution seem bit poor :stuck_out_tongue:
Maybe contain did not work well with innerJoinWith

 $this->Translations->addAssociations([
        'belongsTo' => [
            'Users' => ['className' => 'CakeDC/Users.Users']
        ]
    ]);
    $this->Translations->Users->addAssociations([
        'belongsToMany' => [
            'Dusers' => ['className' => 'CakeDC/Users.Users',
                'foreignKey' => 'user_id',
                'targetForeignKey' => 'duser_id',]
        ]
    ]);

    $translations = $this->Translations->find()
            ->where(['Translations.post_state_id' => 3])
            ->contain(['Users' , 'Posts' => ['Languages'], 'Languages', 'PostStates']);
    

    $friendsTranslations = $this->Translations->find('all')
            ->where(['Translations.post_state_id' => 2])
            ->contain(['Users', 'Posts' => ['Languages'], 'Languages', 'PostStates'])                
            ->select($this->Translations)
            ->select($this->Translations->Users)
            ->select($this->Translations->Posts)
            ->select($this->Translations->PostStates)
            ->select($this->Translations->Languages)
            ->innerJoinWith('Users.Dusers', function ($q) {
                return $q->where(['Dusers.id' => $this->Auth->user('id')]);
            });

    $translations->union($friendsTranslations);
14

You can use only one select and have an array in the call :slight_smile:

Yes ugly, but working :slight_smile:

1 Like
15

Now, it looks better :slight_smile:

$friendsTranslations = $this->Translations->find('all')
                ->where(['Translations.post_state_id' => 2])
                ->contain(['Posts' => ['Languages'], 'Languages', 'PostStates'])
                ->innerJoinWith('Users.Dusers', function ($q) {
            return $q->where(['Dusers.id' => $this->Auth->user('id')])->select($this->Translations->Users);
        });