Query (find many) documents from multiple collections with the same document structure (MongoDB & PHP)

Question

I use MongoDB PHP v1.3 and in my MongoDB I have multiple collections:

// COLLECTION NAMES:
- user_1_list_1
- user_1_list_2
- user_1_list_3
...
- user_1_list_55

All these collections have the same document-structure:

{
   first_name
   last_name
   phone
}

How can I query the documents from all of these collections at the same time? In the documentation, it is explained how to query (find many) documents from one collection: https://docs.mongodb.com/php-library/v1.3/tutorial/crud/#find-many-documents.

For example, in my case, it would look something like this:

$collection_name = "user_1_list_1";
$collection = $this->db->{$collection_name};

$query = []; 

$cursor = $collection->find(
    $query,
    [
        'limit' => 10,
        'skip'  => 0,
        'sort'  => ['first_name' => 1],
    ]
);

... but this will find documents only from one collection (in this case, only from the collection with name "user_1_list_1").

How to find documents from all of these collections (user_1_list_1, user_1_list_2, user_1_list_3 ... ) (that have the same structure), not just from one specific? Is this possible at all? If yes, how would you do that?

Chloramphenicol

There is no facility to do a find() over many collections at once. This operation may make sense in your use case, but it doesn't really make sense in the majority of cases, since a collection typically stands alone and have little relation to other collections. Why not loop over the list of collection names and do the find in each collection? – Dekko

@KevinAdistambha Why not loop over the list of collection names and do the find in each collection? - Because I need to return this data to a frontend table with pagination (pagination number links), that is sorted out and shows, for example, 10 rows per page, and has a search functionality ... (it will look and work similarly to jQuery Datatables). So, if I loop over each and then find in each collection - I wont be able to properly populate that table, it will never be properly sorted out, pagination would never work properly etc. etc. – Chloramphenicol

If pagination is the goal, I suggest to avoid using skip/limit: #49195042 – Dekko

@Dekko Not just pagination, but sorting as well. Anyway, as you wrote - there is no facility to do a find() over many collections at once... so, this is probably impossible to do, it seems that the "design" (having bunch of collections with the same document-structure instead of having one collection) is wrong and bad. – Chloramphenicol

What are you trying to do is "JOIN" multiple collections and this would defeat the purpose of using MongoDB, i.e. non relational database. You could alter your schema and use embedded documents or maybe use a relational database for this. – Trenttrento

try using $lookup and $unwind – Viscoid

Answer 1

MongoDB is not a relation database and there is no good solution for your case.

1. You can get your collections and loop over it (but it’s a not good solution).
2. You can change your database structure and use one collection with embedded data

Sublet