I want to merge two tables with similar columns and display them in pagination.

Need Help
1

Hello.
I now have an AdminUsersTable and a UsersTable.
AdminUsers has columns for “id, name, mail, login-count, created, modified”.
Users has the same columns of “id, name, mail, login-count, created, modified”.
How can I merge these completely separate tables together and display them in a pagination table?

Also, I’d like to see the combined items sorted by “created”.

Help me!

2 
$this->loadModel('Users');
$this->loadModel('Admins');

This two tables have same column

        $users = $this->Users->find('all')->toArray();
        $admins = $this->Admins->find('all')->toArray();
        $all_data = array_merge($users,$admins);

Get data as array and merge both array to get combined data

 usort($all_data , function($a, $b) {
        return $a['created_on'] <=> $b['created_on'];
    });  //need php7+ for work

using this to sort data on created_on

Merge Data..

[
    {
        "id": 1,
        "name": "xxx",
        "email": "xxx@xxx.xx",
        "created_on": "2020-10-06T00:00:00"
    },
    {
        "id": 2,
        "name": "yyy",
        "email": "yyy@yyy.yyy",
        "created_on": "2020-10-07T00:00:00"
    },
    {
        "id": 3,
        "name": "xxx1",
        "email": "xxx1@xxx.xx",
        "created_on": "2020-10-06T00:00:00"
    },
    {
        "id": 4,
        "name": "yyy1",
        "email": "yyy1@yyy.yyy",
        "created_on": "2020-10-08T00:00:00"
    },
    {
        "id": 1,
        "name": "zzz",
        "email": "zzz@zzz.zzz",
        "created_on": "2020-10-06T00:00:00"
    },
    {
        "id": 2,
        "name": "aaa",
        "email": "aaa@aaa.aaa",
        "created_on": "2020-10-15T00:00:00"
    }
]

Merge Data - After sort

[
    {
        "id": 1,
        "name": "xxx",
        "email": "xxx@xxx.xx",
        "created_on": "2020-10-06T00:00:00"
    },
    {
        "id": 3,
        "name": "xxx1",
        "email": "xxx1@xxx.xx",
        "created_on": "2020-10-06T00:00:00"
    },
    {
        "id": 1,
        "name": "zzz",
        "email": "zzz@zzz.zzz",
        "created_on": "2020-10-06T00:00:00"
    },
    {
        "id": 2,
        "name": "yyy",
        "email": "yyy@yyy.yyy",
        "created_on": "2020-10-07T00:00:00"
    },
    {
        "id": 4,
        "name": "yyy1",
        "email": "yyy1@yyy.yyy",
        "created_on": "2020-10-08T00:00:00"
    },
    {
        "id": 2,
        "name": "aaa",
        "email": "aaa@aaa.aaa",
        "created_on": "2020-10-15T00:00:00"
    }
]
1 Like
3

Suggested solution doesn’t support pagination well; if there are millions of records, it’s not going to work at all. I don’t have a better solution (apart from actually merging the two tables entirely and add user groups to handle the difference between admins and users), just pointing out the problem with this solution.

1 Like
4

I hope this code will works for you

$this->loadModel("AdminUsers");
$this->loadModel("Users");

$admin_users = $this->AdminUsers
                    ->find()
                    ->select(["id", "name", "mail", "login-count", "created", "modified"]);

$users = $this->Users
              ->find()
              ->select(["id", "name", "mail", "login-count", "created", "modified"]);

$data=$admin_users->unionAll($users)->epilog('order by created');

$paging_data=$this->paginate($data);
2 Likes
5

Thank you debendra986, Zuluru and Hareshpatel. I will try each of them tomorrow. It has been a great learning experience.