[Question] Need help with table joins

[WoolenSweater]

I have following tables.

class Status(Table):
    code = SmallInt()
    name = Varchar()

class Report(Table):
    id = UUID(primary_key=True)
    form = Varchar()
    # columns

class Message(Table):
    id = UUID(primary_key=True)
    report_id = ForeignKey(references=Report)
    status = ForeignKey(references=Status, target_column=Status.code)
    # columns

class Client(Table):
    report_id = ForeignKey(references=Report)
    type = SmallInt()
    # columns

I want to make this query.

SELECT message.id, message.report_id, report.form, status.name, client.type FROM message LEFT JOIN report ON report.id = message.report_id LEFT JOIN status ON status.code = message.status LEFT JOIN client ON client.report_id = message.report_id WHERE message.status != 0

This variant returns a rather obvious error.

result = await Message.select(
    Message.id,
    Message.report_id,
    Message.report_id.form,
    Message.status.name,
    Message.report_id.client.type  # ?? AttributeError: 'ForeignKey' object has no attribute 'client'
).where(
    Message.status.code != 0
)

How can I join a table with client details?

[sinisaos]

@WoolenSweater No one has answered yet so I'll try even though I can't try it. Try passing the client as fk to the Message table

class Client(Table):
    report_id = ForeignKey(references=Report)
    type = SmallInt()
    # columns

class Message(Table):
    id = UUID(primary_key=True)
    report_id = ForeignKey(references=Report)
    status = ForeignKey(references=Status, target_column=Status.code)
    client = ForeignKey(references=Client)
    # columns

and then use like this.

result = await Message.select(
    Message.id,
    Message.report_id,
    Message.report_id.form,
    Message.status.name,
    Message.client.report_id,
    Message.client.type 
).where(
    Message.status.code != 0
)

If that doesn't work, you can always use raw query Message.raw("your sql query").
I'm sorry if that doesn't help.

[WoolenSweater]

@sinisaos Thank you for your response.I thought about this way, but it doesn't work.
Piccolo transforms this join like this:

LEFT JOIN "client" "message$client" ON ("message"."client" = "message$client"."id")

Message does not have a client field, and Client does not have an id field. It also affects migration.

I would not like to resort to raw request. Perhaps I'm missing something else. I looked in the documentation for methods for joining tables, but they are not there. It seems to me that it would be great if such methods were available if there is no way to access the table through a foreign key. Something like this.

result = await Message.select(
    Message.id,
    Message.status.name,
    Client.report_id,
    Client.type,
).left_join(
    Client, on=Client.report_id == Message.report_id
).where(
    Message.status.code != 0
)

or

result = await Message.select(
    Message.id,
    Message.status.name,
    Client.report_id,
    Client.type,
).left_join(
    Client
).on(
    Client.report_id == Message.report_id
).where(
    Message.status.code != 0
)

[sinisaos]

@WoolenSweater Piccolo doesn't expose join syntax (similarly to Django) and there is no methods like left_join or inner_join.
Can you try this. Pass the client as fk to the Report table and then you can access the Client table columns via Message.report_id or Report.client like this

class Status(Table):
    code = SmallInt()
    name = Varchar()


class Client(Table):
    type = SmallInt()
    # columns


class Report(Table):
    id = UUID(primary_key=True)
    form = Varchar()
    client = ForeignKey(references=Client)
    # columns


class Message(Table):
    id = UUID(primary_key=True)
    report_id = ForeignKey(references=Report)
    status = ForeignKey(references=Status)
    # columns

and then you can use your previous query

result = await Message.select(
    Message.id,
    Message.report_id,
    Message.report_id.form,
    Message.status.name,
    Message.report_id.client.type, 
).where(Message.status.code != 0)

If that doesn't work well, I'm out of ideas other than a raw sql query.

[dantownsend]

Yeah, we're currently working on this.

If possible, you could modify your schema, so Report has a foreign key to Client instead:

Other than that, you can splice the data together in Python.

[WoolenSweater]

Pass the client as fk to the Report table and then you can access the Client table columns via Message.report_id or Report.client like this

Passing the client as fk to the Report table will result in much the same result as in the previous case.
It looks like without changing the database schema I won't achieve the desired result, but unfortunately I can't change the schema.

Yeah, we're currently working on this.

@dantownsend Where can I follow these updates?

[dantownsend]

@dantownsend Where can I follow these updates?

This is it - we're going to add a ReverseLookup.

#599

In the meantime, something like this is probably what you need:

clients = await Client.select(Client.type. Client.report_id)
clients_map = {i['report_id']: i for i in clients}

messages = await Message.select(
    Message.status.name,
    Message.status.code,
    Message.report_id,
    Message.report_id.form
)
 
combined = [{**i, **clients_map[i['report_id']]} for i in messages]

[sinisaos]

Passing the client as fk to the Report table will result in much the same result as in the previous case.

@WoolenSweater Sorry, but this is not true (I tried that and there is no migration error or any other error). That works if you change schema (add client fk to Report table). But since you can't change the schema, @dantownsend solution is better because the result is the same without schema changes.

[WoolenSweater]

Yes, by changing the schema, you can make any method work. I should have said right away that I can't do it, sorry.
I also looked at ReverseLookup and it's also not exactly what I need.

Perhaps I will focus on the method that @dantownsend advised.
Thanks to both of you for helping me figure out the issue.