Multiple Table Joins
As a relational database, TiDB lets you store diverse data in tables with different structures (for example, chunks, documents, users, chats) in a single database. You can also use joins to combine data from multiple tables and perform complex queries.
Basic Usage
Step 1. Create tables and insert sample data
Assuming you have already connected to TiDB using TiDBClient:
Create a documents table and insert some sample data:
from pytidb import Session
from pytidb.schema import TableModel, Field
from pytidb.sql import select
class Document(TableModel):
__tablename__ = "documents"
id: int = Field(primary_key=True)
title: str = Field(max_length=255)
client.create_table(schema=Document, if_exists="overwrite")
client.table("documents").truncate()
client.table("documents").bulk_insert([
Document(id=1, title="The Power of Positive Thinking"),
Document(id=2, title="The Happiness Advantage"),
Document(id=3, title="The Art of Happiness"),
])
Create a chunks table and insert some sample data:
class Chunk(TableModel):
__tablename__ = "chunks"
id: int = Field(primary_key=True)
text: str = Field(max_length=255)
document_id: int = Field(foreign_key="documents.id")
client.create_table(schema=Chunk, if_exists="overwrite")
client.table("chunks").truncate()
client.table("chunks").bulk_insert([
Chunk(id=1, text="Positive thinking can change your life", document_id=1),
Chunk(id=2, text="Happiness leads to success", document_id=2),
Chunk(id=3, text="Finding joy in everyday moments", document_id=3),
])
Create a documents table and insert some sample data:
CREATE TABLE documents (
id INT PRIMARY KEY,
title VARCHAR(255) NOT NULL
);
INSERT INTO documents (id, title) VALUES
(1, 'The Power of Positive Thinking'),
(2, 'The Happiness Advantage'),
(3, 'The Art of Happiness');
Create a chunks table and insert some sample data:
CREATE TABLE chunks (
id INT PRIMARY KEY,
text VARCHAR(255) NOT NULL,
document_id INT NOT NULL,
FOREIGN KEY (document_id) REFERENCES documents(id)
);
INSERT INTO chunks (id, text, document_id) VALUES
(1, 'Positive thinking can change your life', 1),
(2, 'Happiness leads to success', 2),
(3, 'Finding joy in everyday moments', 3);
Step 2. Perform a join query
with Session(client.db_engine) as db_session:
query = (
select(Chunk)
.join(Document, Chunk.document_id == Document.id)
.where(Document.title == "The Power of Positive Thinking")
)
chunks = db_session.exec(query).all()
[(c.id, c.text, c.document_id) for c in chunks]
Perform a join query to combine data from the chunks and documents tables:
SELECT c.id, c.text, c.document_id
FROM chunks c
JOIN documents d ON c.document_id = d.id
WHERE d.title = 'The Power of Positive Thinking';