Hi @KenWhitesell ,
Thanks for your support , I am posing another issue from SQL to ORM conversion. Looking for you and other experts suggestion… Thanks in advance.
Hi,
I have following SQL queries which I tried convert to Django ORM but the result does not show as sql queries. like it read the database_status and poplulate the data but in converted query it shows only few on graph data
following is my original sql query added some variable information to understand the data types.
teams = [
['team1', 'team1', 60],
['team2', 'team2', 50],
]
teamNames = []
for team in range(len(squads)):
teamNames.append("'" +teams[squad][0] + "'")
teamString = ', '.join(teamNames)
areas = [ "'Area01'", "'%localarea%'", "'Area02'" ]
areaString = ', '.join(areaNames)
startdate = '2024-09-11'
contentTypes = [ "'device'" ]
contentTypeString = ', '.join(contentTypes)
team = 1
my_query = """
WITH query_data AS
(SELECT postchange_data :: JSON -> 'custom_fields' ->> 'Working_Team' AS team,
TIME :: timestamp :: date AS date,
cast(prechange_data :: JSON ->> 'status' AS text) AS pre_status,
cast(postchange_data :: JSON ->> 'status' AS text) AS post_status,
cast(prechange_data :: JSON ->> 'area' AS integer) AS area
FROM core_objectchange
WHERE action = 'update'
AND changed_object_type_id = (SELECT id FROM django_content_type WHERE model IN (""" + contentTypeString + """))
AND TIME :: timestamp :: date >= '""" + startdate + """'
AND cast(postchange_data :: JSON ->> 'status' AS text) != cast(prechange_data :: JSON ->> 'status' AS text))
SELECT team, date, count(*) AS server
FROM query_data
WHERE area IN (select id from dcim_site WHERE name IN (""" + str(areaString) + """))
AND post_status IN ('working',
'notworking',
'offline')
AND pre_status NOT IN ('working',
'notworking',
'offline')
AND team IN (""" + str(teamString) + """)
GROUP BY date, team
ORDER BY date, team;
"""
following sql has following output
('team1', datetime.date(2024, 11, 14), 1) ## where status changed from 'new' to 'working'
while my other queries with debugging is as below
contentTypes = ["device"]
content_type_ids = ContentType.objects.filter(model__in=contentTypes).values_list('id', flat=True)
team = 1
area_ids = Area.objects.filter(name__in=areas).values_list('id', flat=True)
print("All Object Changes Count:", all_object_changes.count())
# Filter and annotate the data
annotated_data = ObjectChange.objects.filter(
action='update',
changed_object_type_id__in=content_type_ids,
time__date__gte=startdate
)
print("Annotated Data Step 1 Count:", annotated_data.count())
annotated_data = annotated_data.filter(
postchange_data__status__in=['working', 'notworking', 'offline'],
prechange_data__status__in=['working', 'notworking', 'offline],
)
print("Annotated Data Step 2 Count:", annotated_data.count())
annotated_data = annotated_data.exclude(
postchange_data__status=F('prechange_data__status')
)
print("Annotated Data Step 3 Count:", annotated_data.count())
annotated_data = annotated_data.annotate(
prechange_area_id=Cast('prechange_data__area', output_field=IntegerField())
)
print("Annotated Data Step 4 Count:", annotated_data.count())
annotated_data = annotated_data.filter(
prechange_area_id__in=area_ids
)
print("Annotated Data Step 5 Count:", annotated_data.count())
annotated_data = annotated_data.values(
team=F('postchange_data__custom_fields__Working_Team'),
date=F('time__date')
).annotate(server=Count('id')).order_by('date', 'team')
print("Annotated Data Step 6 Count:", annotated_data.count())
which only shows old queries
Rows <ObjectChangeQuerySet
[{'squad': 'team1', 'date': datetime.date(2024, 10, 17), 'server': 1}, ## it doesnot show the status change from 'new' to 'working' status from database
I tried with google and AI but i am not getting graph similar to original SQL queries
Appreciate your input/suggestion