data in orm not exactly fetched as sql

Using Django Using the ORM
1

Hi All,

I have an issue with the orm code where when I converted my sql queries to orm data is not correctly generated it some how changes the data and not getting table on web page

I have already defined the models here is the snap of the code which I need to convert to the ORM

" # Data
    dev_name = """
        SELECT
            s.name,
            l.name,
            r.name,
            d.position,
            dt.model,
            d.name,
            d.serial
        FROM
            ccim_dev AS d
        INNER JOIN ccim_devtype AS dt ON d.dev_type_id=dt.id
        INNER JOIN ccim_jack AS j ON d.jack_id=j.id
        INNER JOIN ccim_sit AS s ON d.sit_id=s.id
        INNER JOIN ccim_loc AS l ON d.loc_id=l.id
    """

    # Prepare tags for devices using Django ORM
    
    id = ContentType.objects.get(app_label="ccim", model="dev").id
    # Fetch tags data
    tune_name = """
        SELECT
            dev.name,
            t.name
        FROM
            extras_taggeditem ti
            JOIN ccim_dev dev ON dev.id = ti.object_id
            JOIN extras_tune t ON t.id = ti.tun_id
        WHERE
            content_type_id = %s
        ORDER BY
            dev.id;
    """
      
       with connection.cursor() as cursor:
        cursor.execute(dev_name)
        dev_row = cursor.fetchall()
        cursor.execute(tune_name,(id,))
        tune_row = cursor.fetchall()
tune_map = {dev: tune for device, tune in tune_rows}
print(tune_row)
print(tune_map)

with SQL code it gives output of the

tune_row ouput like   [('test1', 'data1'),,
tune_map like  {'test1': ' data1',,....

I want to get same output using orm so how can i achieve it ?

I have tried with the SQL to ORM query converter but not getting same output

2

You don’t, at least not solely through the QuerySets generated by the ORM.

The ORM is designed to return instances of the model objects representing the tables in the database, it is not going to return arbitrary data structures from the queries it generates.

To recreate those specific formats, you could:

  • Create those specific structures within your code from the queryset objects being returned,
  • Execute custom sql directly.
  • Create a view in the database that maps to a Django model
  • Create a stored procedure in the database and call it.

You might also re-evaluate if that output format is truly a requirement for the purposes for which that data will be used.

3

Thanks for your time and response!
Actually our core requirement is to convert sql to orm queryset

even I tried to convert the equal queries through the sql to django coverter
which was but still data not getting as per requirement

Tune Row : [{'name': 'test1', 'tune_name': 'TestValue0'}   ## it is different the above sql output
Tune Map :  {'name': 'tune_name'}  ## there is not data only field name displayed

Thanks

4

is it possible for you to provide some input on following formats
how can doit ?

  • Create those specific structures within your code from the queryset objects being returned,
  • Create a view in the database that maps to a Django model

Thanks in Advance

5

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