How to use LTree with SQLModel #766
Replies: 1 comment 2 replies
-
|
I have been very divided by using |
Beta Was this translation helpful? Give feedback.
-
|
UPDATE: I was able to send direct from sqlalchemy import text
select(Item)
.where(text(f"item.path @> '{path_str}'")So please document this in PS: Array is still an issue I could not find a workaround. |
Beta Was this translation helpful? Give feedback.
-
|
I have declared data as str in sqlmodel and add the ltree support directly in postgresql: -- Update the map table to use ltree for path representation
ALTER TABLE HeroCategory
ALTER COLUMN path
TYPE ltree
USING path::ltree;
-- Enable ltree extension
CREATE EXTENSION IF NOT EXISTS ltree;
-- Add index for ltree path operations (for better performance)
CREATE INDEX IF NOT EXISTS path_idx ON "map" USING GIST (path);
CREATE INDEX IF NOT EXISTS path_btree_idx ON "map" USING BTREE (path);
-- Add constraint to ensure valid ltree paths
ALTER TABLE "HeroCategory"
ADD CONSTRAINT check_valid_ltree_path
CHECK (path IS NULL OR path ~ '^[A-Za-z0-9_]+(\.[A-Za-z0-9_]+)*$');It'd be great if sqlmodel add support for it |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
First Check
Commit to Help
Example Code
Description
⚙️ Challenge: The 'path' column in this model is intended to store hierarchical data, and I plan to use PostgreSQL's ltree data type for efficient querying. However, I'm uncertain about the correct data type to use in the SQLModel for the 'path' column that aligns with PostgreSQL's ltree.
📚 Reference: PostgreSQL ltree Documentation
🎯 Question:
Looking forward to insights and suggestions from the community!
Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.8
Python Version
3.10
Additional Context
No response
Beta Was this translation helpful? Give feedback.
All reactions