1

I have two collections:

USERS:

{
  {"_id": 1, "orders": [1,2]},
  {"_id": 2, "orders": [3]},
  {"_id": 3, "orders": [4,5]}
}

ORDERS:

{
  {"_id": 1, "paid": true},
  {"_id": 2, "paid": false},
  {"_id": 3, "paid": true},
  {"_id": 4, "paid": false},
  {"_id": 5, "paid": false}
}

How can I use MongoDB\Driver in php select all entries from the USERS collection containing at least one paid order? At the moment, I am pulling both collections incide php, and when using the comparison of arrays just filter the collection of users.

My code at this moment:

$users = _mongo_::collection('USERS')->find([]);
$paid_orders = _mongo_::collection('ORDERS')->find(['paid' => true]);
$paid_orders_ids = array_column($paid_orders, '_id');

$filtered_users = array_filter($users, function ($user) use ($paid_orders_ids) {
    return (boolean) array_intersect($user['orders'], $paid_orders_ids);
});

The question is, can I get the filtered list of users right away with just one MongoDB\Driver query?

Вадим Гангнус
  • 13
  • 4
  • 1
    Show the code that you've tried, the desired result, and the actual/current result. – Patrick Q Dec 12 '19 at 13:53
  • I have no good code, but in the result I want get USERS records with "_id" in (1,2) in this case (because USER with "_id" === 3 have no paid ORDERS). Right now I'm doing somthing like: "$users = $userCollection->find([]); $orders = $orderCollection->([]);", and just compared array records – Вадим Гангнус Dec 12 '19 at 14:07
  • 1
    Do not put your code in comments. Instead, edit/update your question. It doesn't matter if you have "good code" or not. You should show what you do have. You said "I am pulling both collections incide php, and when using the comparison of arrays just filter the collection of users." Show _that_ code. – Patrick Q Dec 12 '19 at 14:09
  • I've added the code – Вадим Гангнус Dec 12 '19 at 14:31

1 Answers1

0

Your current code isn't checking the paid status of the orders at all. It is only checking that one of the orders for the user exists in the orders list (which of course they all will). Instead, you want to see if any of the users' orders are marked as paid.

To do this, we first re-arrange the orders array so that the id is the index and the paid status is the value.

$ordersManipulated = array();

foreach($paid_orders as $order)
{
    $ordersManipulated[$order["_id"]] = $order["paid"];
}

Then we change the array_filter() call back function to check the order row that corresponds to each order for each user, stopping when we find a paid order for the current user (since we only require that there is one paid order).

$filtered_users = array_filter($users, function ($user) use ($ordersManipulated) {
    foreach($user["orders"] as $order)
    {
        if($ordersManipulated[$order] === true)
        {
            return TRUE;
        }
    }
    return FALSE;
});

DEMO

Patrick Q
  • 6,373
  • 2
  • 25
  • 34
  • The result of my code is exactly the same. $paid_orders already contain all paid orders selected from MongoDB (see "->find(['paid' => true])"). But in both cases, first the php sends two queries to each collection in database, and after that php compared the results. May be it's possible to send one request insted, and get already filtered USERS array (same as SQL queries allows)? – Вадим Гангнус Dec 12 '19 at 16:49
  • @ВадимГангнус It seems that you have edited your question in a rather fundamental way by adding that last sentence. If you only want to use one query, then you'll need to use whatever JOIN implementation that MongoDB has. I suggest you checkout [this question](https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) and the answer and see where that gets you. – Patrick Q Dec 12 '19 at 16:57