Insert Select in between model. (Noob question) | Django | Page 1

weak veldt Mar 21, 2024, 3:05 PM

#

I have multiple models with relationships with some.

example query that is currently in use

activities = Activity.objects.filter(massive_filter).select_related("DEVICE")

and i want to grab a field from one device.
select related from the device, but there is a seperate table that is user specific to the device.
which the query basically becomes.

Nickname.objects.get(USER=user, DEVICE=activity.DEVICE).NICKNAME

But this is not performant, as it would be a query for every entry in Activity.
Is there a way to add this select without using raw sql?

#

DEVICE__NICKNAMES would be a many-to-one relationship, or NICKNAME__DEVICE as a many-to-one. and NICKNAME is filtered by User

#

is it possible to use annotate?

activities \
.annotate(
                DEVICE_NICKNAME=Nickname.objects.get(USER=user, DEVICE__DEVICE_ID=activity.DEVICE.DEVICE_ID).NICKNAME
            )```

bit unsure on how to do the filter based on the current activity.

wooden dew Mar 22, 2024, 5:52 AM

#

Can you post the relevant models?

weak veldt Mar 22, 2024, 9:12 AM

#

wooden dew Can you post the relevant models?

Sure, here are the relevant models and fields: (removed alot of fields that are not related)

class Device(models.Model):
    DEVICE_ID = models.TextField(max_length=100, null=True, unique=True)

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)

class Nickname(models.Model):
    NICKNAME = models.TextField(max_length=100)
    USER = models.ForeignKey(Profile, on_delete=models.CASCADE)
    DEVICE = models.ForeignKey(Device, on_delete=models.CASCADE)

class Activity(models.Model):
    DEVICE = models.ForeignKey(to=Device, on_delete=models.SET_NULL, null=True)

wooden dew Mar 22, 2024, 11:20 AM

#

Great, now what are you trying to do? Formulate your answer irrespective of django technicalities.

Example: Given a user, I want to get all .... where ... etc

#

A few points, that are not related to your question:

Try to use full lower case field names, while it's not "required" it is standard practice, as not to confuse it with "Constants" / "Settings" / "Choices"
Django creates an id field which (in the case of Device) when referenced via a foreignkey would be named device_id, hence your naming of the DEVICE_ID may cause some confusion. I would suggest: internal_id, serial, code, or anything else
You're using USER to point to a Profile, this could cause confusion to the developer maintaining the code. it's preferable to call this field profile

weak veldt Mar 22, 2024, 12:20 PM

#

wooden dew A few points, that are not related to your question: - Try to use full lower cas...

So, i want to grab SELECT * on Activity, based on filters, that is done, then i want to have device_nickname for the activity, which is based on the user.

So, I believe or i think if we were going to do this in sql from my limited knowledge of it.

SELECT "core_activity"."id", "core_activity"."DEVICE_(model)_id", "core_compactor"."id", "core_device"."DEVICE_ID" FROM "activity" INNER JOIN "core_device" ON ("core_activity"."DEVICE_(model)_id" = "core_compactor"."id") WHERE (long filter) as t1;

and i guess for a select would be

SELECT "core_nickname"."id", "core_nickname"."nickname" FROM "core_nickname" WHERE "core_nickname"."DEVICE_(model)_id" = t1."DEVICE_(model)_id" AND "core_nickname"."PROFILE_id" = (from variable);

or else i think i believe all in one could be solved in an another inner join with a parameter as user.

SELECT "core_activity"."id", "core_activity"."DEVICE_(model)_id", "core_device"."id", "core_device"."DEVICE_ID", "core_nickname"."id", "core_nickname"."nickname" FROM "activity" INNER JOIN "core_device" ON ("core_activity"."DEVICE_(model)_id" = "core_compactor"."id") LEFT JOIN "core_nickname" ON ("core_activity"."DEVICE_(model)_id" = "core_nickname"."DEVICE_(model)_id" AND "core_nickname"."PROFILE_id" = ($someUserArgInCode)) WHERE (long filter);

and yeah the device model is in a different name, i think i just made it a bit confusing, but i think it should be as it is. "DEVICE_(model)_id" generated by django for the foreign key, and the "DEVICE_ID" is the self made one, but it isn't used for the query.

#

LEFT JOIN "core_nickname" ON ("core_activity"."DEVICE_(model)_id" = "core_nickname"."DEVICE_(model)_id" AND "core_nickname"."PROFILE_id" = ($someUserArgInCode))

wooden dew Mar 22, 2024, 1:32 PM

#

Alright so in plain english (yes, you seem to have a MakeItConfusing super power 😇 ):

Given an user and a device
Get all activities for this device
All the while annotating the nickname that this user has for this device

Did i get it right?

wooden dew Mar 22, 2024, 2:01 PM

#

If so, see if this helps.

    >>> Activity.objects.filter(
    ...     DEVICE=d1,
    ...     DEVICE__nickname__USER=p1
    ... ).annotate(
    ...     nickname=F('DEVICE__nickname__NICKNAME')
    ... )

Where d1 is your Device and p1 is your Profile

weak veldt Mar 22, 2024, 2:02 PM

#

if there are no nicknames for that device for the user, would that not return any activities?

#

if it is on a filter

wooden dew Mar 22, 2024, 2:04 PM

#

then i want to have device_nickname for the activity, which is based on the user.

I assumed that this is always the case.

weak veldt Mar 22, 2024, 2:04 PM

#

no, i want to have the device nickname if there is

wooden dew Mar 22, 2024, 2:04 PM

#

if not, then we have to adjust the given.

weak veldt Mar 22, 2024, 2:04 PM

#

i managed to test out the sql in the way i wanted it to be, simple left join

wooden dew Mar 22, 2024, 2:04 PM

#

Given a device
Get all activities for this device
If a nickname exists for the device, annotate that nickname on the activity.

weak veldt Mar 22, 2024, 2:05 PM

#

if a nickname for the device exists that also has a foreign key with the user, annotate it on to the activity

wooden dew Mar 22, 2024, 2:13 PM

#

if you have the SQL, share it, we could try and transform it to django orm

weak veldt Mar 22, 2024, 2:13 PM

#

SELECT "core_activity"."id", "core_activity"."DEVICE_(model)_id", "core_device"."id", "core_device"."DEVICE_ID", "core_nickname"."id", "core_nickname"."nickname" 
FROM "activity" 
  INNER JOIN "core_device" ON ("core_activity"."DEVICE_(model)_id" = "core_compactor"."id") 
  LEFT JOIN "core_nickname" ON ("core_activity"."DEVICE_(model)_id" = "core_nickname"."DEVICE_(model)_id" AND "core_nickname"."PROFILE_id" = ($someUserArgInCode)) 

WHERE (long filter);

wooden dew Mar 22, 2024, 2:15 PM

#

right, that left join seems to be a clear indication of a FilteredRelation. (the AND aspect of it)

https://docs.djangoproject.com/en/5.0/ref/models/querysets/#filteredrelation-objects

I'm checking as well.

weak veldt Mar 22, 2024, 2:27 PM

#

FilteredRelation's relation_name cannot contain lookups (got 'DEVICE__NICKNAME')."

wooden dew Mar 22, 2024, 2:28 PM

#

DEVICE__nickname

#

it seems to ignore the nested relationship.

weak veldt Mar 22, 2024, 2:36 PM

#

yeah i don't see it being fetched at all

wooden dew Mar 22, 2024, 2:37 PM

#

it fetched for me, but it's adding another left join on the user instead of adding the AND [user check here]

#

which means I'm getting duplicate results for activities

#

{'id': 1, 'DEVICE_id': 1, 'nickname': 'u1n1', 'user': 'user1'}
{'id': 1, 'DEVICE_id': 1, 'nickname': 'u2n1', 'user': 'user2'}
{'id': 2, 'DEVICE_id': 1, 'nickname': 'u1n1', 'user': 'user1'}
{'id': 2, 'DEVICE_id': 1, 'nickname': 'u2n1', 'user': 'user2'}
{'id': 3, 'DEVICE_id': 1, 'nickname': 'u1n1', 'user': 'user1'}
{'id': 3, 'DEVICE_id': 1, 'nickname': 'u2n1', 'user': 'user2'}

#

(whereas it should be 3 results for user1 only)

weak veldt Mar 22, 2024, 2:40 PM

#

how does your query function look like

#

as i tried with

activities.annotate(
    DEVICE_NICKNAME=FilteredRelation(
        "DEVICE__nickname",
        condition=Q(DEVICE__nickname__USER=user)
    )
)

#

can i do a select_related with a filter?

#

like select_related("DEVICE__nickname", blablafilter)

wooden dew Mar 22, 2024, 2:45 PM

#

no, that's the job of FilteredRelation. Something like this exists for prefetch_related

#

we're not far off with FilteredRelation

weak veldt Mar 22, 2024, 2:51 PM

#

looking at what the db logs seems to be the exact same query as the non-annotated one

wooden dew Mar 22, 2024, 2:55 PM

#

Your query above yields "core_nickname"."nickname" = NULL in the case no nickname exists for the device for $SomeUserArgsInCode ?

weak veldt Mar 22, 2024, 2:56 PM

#

yes, it would do that

#

wrong query

wooden dew Mar 22, 2024, 2:57 PM

#

That's fine, I believe you.

weak veldt Mar 22, 2024, 2:57 PM

#

https://i.imgur.com/eJcCL5x.png

Imgur

wooden dew Mar 22, 2024, 2:59 PM

#

coincidentally, I'm able to get the same query, just not the same result

#

SELECT "core_activity"."id",
       "core_device"."DEVICE_ID",
       nicknames."NICKNAME"
  FROM "core_activity"
 INNER JOIN "core_device"
    ON ("core_activity"."DEVICE_id" = "core_device"."id")
  LEFT OUTER JOIN "core_nickname" nicknames
    ON ("core_device"."id" = nicknames."DEVICE_id" AND (nicknames."USER_id" = 1))
 WHERE "core_activity"."DEVICE_id" = 3

#

actually...

#

Have look, see if this makes sense / is correct to you:

#


In [115]: Nickname.objects.filter(USER=p1).values_list('DEVICE', 'NICKNAME')
Out[115]: SELECT "core_nickname"."DEVICE_id",
       "core_nickname"."NICKNAME"
  FROM "core_nickname"
 WHERE "core_nickname"."USER_id" = 1
 LIMIT 21

Execution time: 0.001039s [Database: default]
<QuerySet [(1, 'u1n1'), (2, 'u1n2')]>

In [116]: qs = Activity.objects.annotate(
     ...:     nicknames=FilteredRelation(
                  'DEVICE__nickname',
                  condition=Q(DEVICE__nickname__USER=p1)
              )
     ...: ).values('id', 'DEVICE__DEVICE_ID', 'nicknames__NICKNAME')

SELECT "core_activity"."id",
       "core_device"."DEVICE_ID",
       nicknames."NICKNAME"
  FROM "core_activity"
  LEFT OUTER JOIN "core_device"
    ON ("core_activity"."DEVICE_id" = "core_device"."id")
  LEFT OUTER JOIN "core_nickname" nicknames
    ON ("core_device"."id" = nicknames."DEVICE_id" AND (nicknames."USER_id" = 1))

Execution time: 0.003174s [Database: default]
{'id': 1, 'DEVICE__DEVICE_ID': 'D1', 'nicknames__NICKNAME': 'u1n1'}
{'id': 2, 'DEVICE__DEVICE_ID': 'D1', 'nicknames__NICKNAME': 'u1n1'}
{'id': 3, 'DEVICE__DEVICE_ID': 'D1', 'nicknames__NICKNAME': 'u1n1'}
{'id': 4, 'DEVICE__DEVICE_ID': 'D2', 'nicknames__NICKNAME': 'u1n2'}
{'id': 5, 'DEVICE__DEVICE_ID': 'D3', 'nicknames__NICKNAME': None}
{'id': 6, 'DEVICE__DEVICE_ID': 'D3', 'nicknames__NICKNAME': None}
{'id': 7, 'DEVICE__DEVICE_ID': 'D3', 'nicknames__NICKNAME': None}

#

The input is as follows:
3 Users: u1, u2, u3
3 Devices: D1, D2, D3
3 Nicknames: u1n1, u1n2, u2n1

#

D3 has no nicknames.

#

Obviously, 7 activities in total.

weak veldt Mar 22, 2024, 3:13 PM

#

ah, yeah i do seem to be able to get the data out trough if i get it from .values

#

is there a function where i can just append .values and keep all * values?

wooden dew Mar 22, 2024, 3:16 PM

#

As in, everything in Activity alongside the above ?

weak veldt Mar 22, 2024, 3:18 PM

#

yeah, well i only need the nickname

#

since i also use select_related, i also include the compactor to select

wooden dew Mar 22, 2024, 3:19 PM

#

you can still follow this up with annotate(nickname=F('nicknames__NICKNAME'))

weak veldt Mar 22, 2024, 3:19 PM

#

.annotate().annotate()?

#

ah nvm i am stupid

wooden dew Mar 22, 2024, 3:20 PM

#

well, or add (I didn't know django was smart enough to support the FilteredRelation in the same annotate apparently it does)

#

I think this could be the final solution for you, so good luck!

weak veldt Mar 22, 2024, 3:21 PM

#

ah yes, now it works exactly how i want it

#

thank you so much

#

i can reduce it finally to 1 call instead of having it be 100

#Insert Select in between model. (Noob question)