Joining without data on the right table

[waldner]

I have the following schemas:

class PiccoloPieces(Table, tablename="pieces"):

    id = Serial(primary_key=True, null=False)
    type = Text(null=False, required=True)
    description = Text(null=False, required=True)
    start_date = Date(null=False, required=True)
    timezone = Text(null=False, required=True)
    internal_id = Text(null=False, required=True)

class PiccoloParts(Table, tablename="parts"):

    id = Serial(primary_key=True, null=False)
    piece_id = ForeignKey(references=PiccoloPieces, target_column='id', null=True, required=True)
    description = Text(null=False, required=True)
    start_date = Date(null=False, required=True)
    internal_id = Text(null=False, required=True)

So there's a one-to many relationship between pieces.id and parts. Now to get a piece with all its parts, I'm currently forced to use a query like this:

    piece_with_parts = await PiccoloParts.select(PiccoloParts.piece_id.all_columns(), PiccoloParts.all_columns()).where(
        (PiccoloParts.piece_id.id == 10)
    )

This looks backwards to me, first because I'm interested in pieces yet I have to specify everyting in function of PiccoloParts in the query to traverse the foreign key, second because if I have a piece with no associated parts, I'd still like to get that as a result, while the above query returns an empty list. So in plain SQL terms I'd do:

SELECT * FROM
    pieces LEFT JOIN parts ON (pieces.id = parts.piece_id)
WHERE pieces.id = 10;

But I'm unable to obtain the same result with piccolo. Am I missing something here?

The ultimate goal is to build a pydantic object PieceWithParts with the following structure:

class Part(BaseModel):
    ....

class Piece(BaseModel):
    ....

class PieceWithParts(Piece):
    parts: list[Part] = []
    model_config = ConfigDict(extra='forbid')

where the parts list might be empty if no associated parts are found.

Would querying pieces alone for the piece first, then (if the piece is found) querying parts for all parts matching the piece id be a better (although slightly less efficient and/or idiomatic) way to accomplish the task?

[sinisaos]

@waldner I think you need a reverse foreign key lookup in that case. For now you need to make two queries.
Reverse lookup is not implemented yet, but there is a PR for it. You can try patch your local Piccolo installation with that PR. After that you simply add a reverse lookup attribute to the PiccoloPieces table like this

from piccolo.columns.reference import LazyTableReference
from piccolo.columns.reverse_lookup import ReverseLookup

class PiccoloPieces(Table, tablename="pieces"):
    # unchanged columns
    parts = ReverseLookup(
        LazyTableReference("PiccoloParts", module_path=__name__), reverse_fk="piece_id"
    )

After that you can query the PiccoloPieces table using single DB query, like this (similar to an M2M select query)

piece_with_parts = await PiccoloPieces.select(
    PiccoloPieces.all_columns(),
    PiccoloPieces.parts(), # reverse lookup
).where(PiccoloPieces.id == 1)

The result is this and you get all the parts for PiccoloPieces.id

[
    {
        "id": 1,
        "type": "Type A",
        "description": "Desc Type A",
        "start_date": datetime.date(2025, 4, 12),
        "timezone": "UTC",
        "internal_id": "1",
        "parts": [
            {
                "id": 1,
                "piece_id": 1,
                "description": "Piccolo Part 1",
                "start_date": datetime.date(2025, 4, 12),
                "internal_id": "100",
            }
        ],
    }
]

[waldner]

Thanks, in the meantime I've also been experimenting with join_on and seem to be getting a somewhat more sensible result by doing:

    piece_with_parts = await PiccoloPieces.select(PiccoloPieces.id.join_on(PiccoloParts.piece_id).all_columns(), PiccoloPieces.all_columns()).where(
        (PiccoloPieces.id == 10) 
    )

which at least lets me reason in terms of pieces instead of parts, and also correctly returns pieces without parts. Using nested=True I can also get, for each record, the part data inside its own dictionary, so it can be easily extracted. Building my PieceWithParts object still needs some postprocessing of the piccolo result, but I think it can be good enough for now.

The perfect outcome would be just as the one you show, with parts already inside a list within the piece, but I don't think it's possible to get that without using the code in the PR and I didn't want to stray too much from vanilla.

I'll go either with the join_on code above, or will experiment with running two queries (after all, with asyncio they can even be run in parallel). Thanks!

Let's wait some more to see if more ideas are suggested, then I think we can close the discussion.

[waldner]

As an extra info, if using the join_on code shown above with nested=True, piccolo puts the part data inside a dictionary called id and that name cannot be changed (or at least, I haven't found a way to change it), so during postprocessing I have to do eg

piece = None

for record in piece_with_parts:
    # extract part 
    part_dict = record["id"]     # this is where the part data ends up
    # restore correct value for piece id
    record["id"] = part_dict["piece_id"]
    if piece is None:
        piece = PieceWithParts(**record)
    piece.parts.append(Part(**part_dict))
...

to restore the correct structure of the piece schema.
I've also tried

 piece_with_parts = await PiccoloPieces.select(PiccoloPieces.id.as_alias('part').join_on(PiccoloParts.piece_id).all_columns(), PiccoloPieces.all_columns()).where(
        (PiccoloPieces.id == 10) 
    )

which produces no error but doesn't rename the part dictionary either.

This makes the kind of postprocessing described above more complicated than it should be, because in the case of a piece with no parts, record["id"]["piece_id"] will be None and so you will have to fill in record["id"] with the user-supplied piece id used for the query, otherwise it will be lost, whereas if the part dictionary had a name other than id, you could just extract it, delete it, and be confident that what's left is already a valid piece.

[sinisaos]

@waldner If you go with two DB queries, you can go with something like this (old gist)

import itertools as it

pieces = await PiccoloPieces.select()
parts = await PiccoloParts.select()

parts_map = {i[0]: list(i[1]) for i in it.groupby(parts, lambda x: x["piece_id"])}
piece_with_parts = []

for piece in pieces:
    try:
        parts_or_none = parts_map[piece["id"]]
    except KeyError:
        parts_or_none = []
    piece_with_parts.append(
        {
            "piece": piece,
            "parts": parts_or_none,
        }
    )
print(piece_with_parts)

and you can get a similar result as with reverse lookup queries.

[
    {
        "piece": {
            "id": 1,
            "type": "Type A",
            "description": "Desc Type A",
            "start_date": datetime.date(2025, 4, 12),
            "timezone": "UTC",
            "internal_id": "1",
        },
        "parts": [
            {
                "id": 1,
                "piece_id": 1,
                "description": "Piccolo Part 1",
                "start_date": datetime.date(2025, 4, 12),
                "internal_id": "100",
            }
        ],
    },
    {
        "piece": {
            "id": 2,
            "type": "Type B",
            "description": "Desc Type B",
            "start_date": datetime.date(2025, 4, 12),
            "timezone": "UTC",
            "internal_id": "2",
        },
        "parts": [], <- empty list if there are no parts for the piece
    },
]