model setup returning an array of another model | Django | Page 1

elder shale Jul 8, 2024, 5:54 PM

#

I have these models, generated from inspectdb:

class Maps(models.Model):
    map_name = models.TextField()
    map_type = models.TextField()  # This field type is a guess.
    map_code = models.CharField(primary_key=True, max_length=6)
    desc = models.TextField()
    official = models.BooleanField()
    image = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'maps'

class MapCreators(models.Model):
    map_code = models.OneToOneField('Maps', models.DO_NOTHING, db_column='map_code', primary_key=True)
    user = models.ForeignKey('Users', models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'map_creators'
        unique_together = (('map_code', 'user'),)

A Map can have multiple creators, so if I do:

x = Maps.objects.all() 
# x[0] has one map creator associated with it
# x[1] has more than one map creator associated with it
x[0].mapcreators
# <MapCreators: MapCreators object (1ZYAM)>

x[1].mapcreators
# This results in an error

Traceback (most recent call last):
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\fields\related_descriptors.py", line 506, in __get__
    rel_obj = self.related.get_cached_value(instance)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\fields\mixins.py", line 15, in get_cached_value
    return instance._state.fields_cache[cache_name]
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
KeyError: 'mapcreators'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\fields\related_descriptors.py", line 514, in __get__
    rel_obj = self.get_queryset(instance=instance).get(**filter_args)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\query.py", line 652, in get
    raise self.model.MultipleObjectsReturned(
mapsearch.models.MapCreators.MultipleObjectsReturned: get() returned more than one MapCreators -- it returned 2!

How do I resolve this issue?

#

As a side issue, i would prefer it if the Maps object MapCreators attribute could be named map_creators instead of mapcreators, is this also possible?

cursive lynx Jul 8, 2024, 6:41 PM

#

elder shale I have these models, generated from inspectdb: ```py class Maps(models.Model): ...

A Map can have multiple creators, so if I do:
No, you models say otherwise

#

What the purpose of MapCreators model in the first place?

elder shale Jul 8, 2024, 6:42 PM

#

I used inspectdb because this db is in use with a separate non django project

#

it's just normalized tables from my psql

elder shale Jul 8, 2024, 6:44 PM

#

cursive lynx > A Map can have multiple creators, so if I do: No, you models say otherwise

What is needing to be changed?

cursive lynx Jul 8, 2024, 6:45 PM

#

Perfectly remove the model, it doesn't seem to have any reason except increasing complexity

elder shale Jul 8, 2024, 6:46 PM

#

Ok sure, then how do I access the map creators?

cursive lynx Jul 8, 2024, 6:47 PM

#

have a ManyToMany between users and maps

#

Unless I'm missing some reason in this structure

elder shale Jul 8, 2024, 6:47 PM

#

users aren't necessarily creators

#

mapcreators are a subset of users

cursive lynx Jul 8, 2024, 6:49 PM

#

mmm, and what about it?

#

I don't see how it different from what you have now

#

except current doesn't seem to match actual data

elder shale Jul 8, 2024, 6:50 PM

#

I'm not sure I follow. The MapCreators table in my db tells me which user id is associated with each map code

#

The user table is for other user based attributes that I have saved unrelated to maps

#

If i were to remove the MapCreator model, how then can the Maps model infer who the creator(s) are

#

There is no map creator information in the users table/model

cursive lynx Jul 8, 2024, 6:52 PM

#

elder shale There is no map creator information in the users table/model

So you've omitted information in your models, I see

elder shale Jul 8, 2024, 6:52 PM

#

no, these are directly modelled from the database

#

the model is identical to the db tables

#

so, is that something I am missing?

cursive lynx Jul 8, 2024, 6:53 PM

#

elder shale no, these are directly modelled from the database

There is no information on MapCreators

#

That's why I say that model is not needed

elder shale Jul 8, 2024, 6:54 PM

#

What do you mean by information?

#

CREATE TABLE public.users (
  user_id bigint PRIMARY KEY NOT NULL,
  nickname character varying(25) NOT NULL,
  alertable boolean NOT NULL DEFAULT true
);

CREATE TABLE public.map_creators (
  map_code character varying(6) NOT NULL,
  user_id bigint NOT NULL,
  PRIMARY KEY (map_code, user_id),
  FOREIGN KEY (map_code) REFERENCES public.maps (map_code)
  MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES public.users (user_id)
  MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE public.maps (
  map_name text NOT NULL,
  map_type text[] NOT NULL,
  map_code character varying(6) PRIMARY KEY NOT NULL,
  "desc" text NOT NULL,
  official boolean NOT NULL DEFAULT false,
  image text
);

#

for what it's worth

cursive lynx Jul 8, 2024, 6:55 PM

#

Ok, so use ManyToManyField then

#

as I first said

elder shale Jul 8, 2024, 6:55 PM

#

in MapCreators, correct?

#

for the map_code

#

?

cursive lynx Jul 8, 2024, 6:56 PM

#

No MapCreators is not needed, and looks wrong

#

remove the model and use ManyToMany on either users or maps

elder shale Jul 8, 2024, 6:57 PM

#

Ok, but how does django infer to use the map_creators table in my database?

#

I'm assuming Django models and actual DB tables are not extremely coupled

#

based on this conversation

#

I figured, modelling each table would be what I need- or are you saying to also remove the usage of map_creators in my database?

cursive lynx Jul 8, 2024, 7:00 PM

#

elder shale Ok, but how does django infer to use the map_creators table in my database?

By ManyToManyField. It creates an implicit intermediate table itself, if it's what you wanted to know

#

If you on to fixing code as you have it - well, I'd ask why one side of MapCreators is one-to-one field?

#

Basically that says you map always have only 0 or 1 creator

#

Which doesn't match with your description

elder shale Jul 8, 2024, 7:02 PM

#

python manage.py inspectdb gave me this exact structure. I was hoping it would be accurate, but I guess it couldn't make assumptions

cursive lynx Jul 8, 2024, 7:04 PM

#

Well, maybe it's bugged somehow, can't say I've used inspectdb a lot

#

But every time I see a OneToOneField I immediately question it purpose

elder shale Jul 8, 2024, 10:04 PM

#

I was able to get it to work with the following:

class MapCreators(models.Model):
    map_code = models.OneToOneField('Maps', models.DO_NOTHING, primary_key=True, db_column="map_code")
    user = models.ForeignKey('Users', models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'map_creators'
        unique_together = (('map_code', 'user'),)

class Maps(models.Model):
    map_name = models.TextField()
    map_type = models.TextField()  # This field type is a guess.
    map_code = models.CharField(primary_key=True, max_length=6)
    desc = models.TextField()
    official = models.BooleanField()
    image = models.TextField(blank=True, null=True)
    map_creators = models.ManyToManyField('Users', through='MapCreators')

    class Meta:
        managed = False
        db_table = 'maps'

Now I want the exact same thing for MapLevels. But I get an error when migrating.

class MapLevels(models.Model):
    map_code = models.OneToOneField('Maps', models.DO_NOTHING, primary_key=True, db_column="map_code")
    level = models.TextField()

    class Meta:
        managed = False
        db_table = 'map_levels'
        unique_together = (('map_code', 'level'),)

class Maps(models.Model):
    map_name = models.TextField()
    map_type = models.TextField()  # This field type is a guess.
    map_code = models.CharField(primary_key=True, max_length=6)
    desc = models.TextField()
    official = models.BooleanField()
    image = models.TextField(blank=True, null=True)
    map_creators = models.ManyToManyField('Users', through='MapCreators')
    map_levels = models.ManyToManyField(MapLevels)

    class Meta:
        managed = False
        db_table = 'maps'

code:

>>> from mapsearch.models import Maps
>>> x = Maps.objects.filter(map_code='SH9G9')
>>> x[0].map_levels.values()

#

Traceback (most recent call last):
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\backends\utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedTable: relation "maps_map_levels" does not exist
LINE 1: ..."map_levels"."level" FROM "map_levels" INNER JOIN "maps_map_...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\query.py", line 376, in __repr__
    data = list(self[: REPR_OUTPUT_SIZE + 1])
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\query.py", line 400, in __iter__
    self._fetch_all()
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\query.py", line 1928, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\query.py", line 210, in __iter__
    for row in compiler.results_iter(
               ^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 1513, in results_iter
    results = self.execute_sql(
              ^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\models\sql\compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\backends\utils.py", line 122, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\backends\utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\backends\utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\backends\utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "E:\Coding\Parkour\doompk\.venv\Lib\site-packages\django\db\backends\utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: relation "maps_map_levels" does not exist
LINE 1: ..."map_levels"."level" FROM "map_levels" INNER JOIN "maps_map_...

If I use django-extensions to view the raw SQL it shows:

SELECT "map_levels"."map_code",
       "map_levels"."level"
  FROM "map_levels"
 INNER JOIN "maps_map_levels"
    ON ("map_levels"."map_code" = "maps_map_levels"."maplevels_id")
 WHERE "maps_map_levels"."maps_id" = 'SH9G9'
 LIMIT 21

This weird inner joining of the intermediate table (maps_map_levels) and the actual table (map_levels- this exists in the database) just doesn't make sense at all. I don't want to do that. How do I skip all of that and just relate the two tables together??

#

The only difference between MapLevels and MapCreators is that MapCreators is essentially an intermediate (but actually exists in the database) table which I set using through but MapLevels isn't similar in that way. I just want a list of all the MapLevels from within the Map object

#

I have to be missing something extremely obvious because it does not make sense why it's so hard to understand lol

#

I want to do this:

maps = Maps.objects.all() # I now have access to all map objects
maps[0].map_creators      # I now have a list of all the Users that are map creators associated with this map
maps[0].map_levels        # I now have a list of all the level names associated with this map

#

And the first two are working seemingly correct. I just can't get the last line to work properly

#

If I change MapLevels.map_code to a ManyToMany, I get an internal error. So that can't be right. If I add the db_table and db_column, it alters the SQL in a good direction but it still has this INNER JOIN going on for no reason

#

When running maps[0].map_levels, I want Django to do this

SELECT "map_levels"."map_code",
       "map_levels"."level"
  FROM "map_levels"
  JOIN "maps"
    ON ("map_levels"."map_code" = "maps"."map_code")
 WHERE "map_levels"."map_code" = 'SH9G9'

elder shale Jul 9, 2024, 12:37 AM

#

Ok.. removing the many to many from Maps and only using a foreign key with MapLevels, gives me a maplevels_set and works

#model setup returning an array of another model