I need to find how to build a query with django ORM I have two tables with a relation one to many.
table A
id = integer autonumeric
data = postgres json
...
table B
id = integer autonumeric
id_A = table A id
name = string
t_name = string like slug
the A.data
have a json with the structure {key (t_name of table B): value (is a string value)}
so that the definition of entities (name and t_name) are in the table B and the values of that entities ere in the json structure in the table A.
eje:
Table A
------------
id | data
1 |{"a_01":"value a","a_02":"value a","b_01":"value b"}
2 |{"a_01":"value a","b_01":"value b"}
Table B
-----------
id | id_A | name | t_name
1 | 1 | A | a_01
2 | 1 | AA | a_02
3 | 1 | B | b_01
4 | 2 | A | a_01
5 | 2 | B | b_01
the id_A
and t_name
are uniques together
I need to get the items from table A with name (B.name) and value (A.data."t_name") from django orm
this query solve my problem but I don't how do in django ORM
SELECT at.id, at.data, bt.name
FROM "A" AS at
JOIN "B" AS bt ON at.id=bt.id_a
WHERE data ->> bt.t_name = 'value a' AND bt.name='AA'
LIMIT 50;
the result is:
1 | {"a_01":"value a","a_02":"value a","b_01":"value b"} | AA