"Inner join" like with MongoDB in Flask, Jinja

Question

I'm trying to perform an 'inner join' like with two MongoDB collections using Flask.

I think I should be using $lookup to achieve this, and my route currently is:

@app.route("/perfumes")
def perfumes():
    perfumes = mongo.db.perfumes.find()
    users = mongo.db.perfumes.aggregate(
        [
            {
                "$lookup": {
                    "from": "mongo.db.users",
                    "localField": "author",
                    "foreignField": "username",
                    "as": "creator",
            }
        }
    ]
)
return render_template("perfumes.html", title="Perfumes", perfumes=perfumes, users=users)

In my template I'm iterating through the perfumes in one collection and would like to access all the data in the users collection, as each perfume item has been created by one user. The perfumes collection has an author field which should be used to link the username field in the users collection. A very simplified (stripped down) version of my template looks like this:

% for perfume in perfumes %}
    {{ perfume.name }}
    Created by {{ <HERE I WANT TO HAVE MY ITEM CREATOR> }} on {{ perfume.date_updated.strftime('%d-%m-%Y') }}
{% endfor %}

What am I missing in order to have this 'join' working and be able to access to all data for that item from both collections?

Thank you!!

NOTE: I want to access more than just the creator's username, like profile picture, etc, that's why I need the join.

UPDATE: These are the structures I'm using for both databases:

{
    "perfumes": {
        "author": "<string>",
        "brand": "<string>",
        "name": "<string>",
        "descritpion": "<text field>",
        "date_updated": "<date>",
        "public": "<boolean>",
        "picture": "<string>"
    },
    "users": {
        "username": "<string>",
        "first_name": "<string>",
        "last_name": "<string>",
        "email": "<string>",
        "is_admin": "<boolean>",
        "avatar": "<string>"
    }
}

So the author in perfumes would be linkable with the username in users.

Answer 1

You can do it with a simple aggregation. Try the below code:

cur = mongo.db.perfumes.aggregate(
    [
        {
            '$lookup': {
                'from': 'users', # You can directly specify on which collection you want to lookup
                'localField': 'author',
                'foreignField': 'username',
                'as': 'creator',
            }
        },
        {
            '$unwind' : '$creator'
        },
        {
            '$project': {
                'username' : '$creator.username',
                'perfumeName' : '$name',
                'date_updated' : '$date_updated',
                'profilePicture' : '$creator.avatar',
                ..., # Rest of the items that you want to display
                '_id': 0
            }
        }
    ]
)

Return the results like below:

return render_template("perfumes.html", title="Perfumes", perfumes=cur) # You don't need to pass users

Display in the template like below:

{% for perfume in perfumes %}
    {{ perfume['perfumeName'] }}
    Created by {{ perfume['username'] }} on {{ perfume['date_updated'].strftime('%d-%m-%Y') }}
{% endfor %}

I hope it helps.

Answer 2

You can create a jinja filter in order to join the information from the two cursors, and then iterate over it on jinja as if it was a list o dicts.

Start creating a custom jinja filter, where aggregated cursor is the output from your mongo.db.perfumes.aggregate line: e.g. page_filters.py file

from .. import app

@app.template_filter()
def get_user_information(perfume_cursor):
    """ Creates a generator to get extra info of user """
    # Transform mongo cursor into a list of dicts
    perfume_cursor = list(perfume_cursor)

    for item in perfume_cursor:
        extra_info = mongo.db.users.find_one({"username": item['author']}, {"first_name": 1, "profile_picture": 1})
        # Declare below your desired user info
        item['user_info'] = dict(first_name=extra_info.first_name,profile_picture=extra_info.profile_picture)
        yield item

To make our filter available in the templates, we just need to import it in our top-level init.py

# Make sure app has been initialized first to prevent circular imports.
from .util import page_filters

Now on jinja, just use the filter

{% for perfume in (perfumes | get_user_information) %}
    {{ perfume['name'] }}
    Created by {{ perfume['user_info']['first_name']}} on {{ perfume['date_updated'].strftime('%d-%m-%Y') }}
{% endfor %}

You should make the query more specific getting only the necassary user fields though, otherwise there might be too much info on your memory. The reason we use Generator, is to provide a means of processing data without loading the entire data-source into memory first.

refs: https://uniwebsidad.com/libros/explore-flask/chapter-8/custom-filters https://www.lachlaneagling.com/reducing-memory-consumption-python/