Add OneToOne relation type #256

Activity

1. collerek

   added

   enhancementNew feature or requestNew feature or request

   on Jun 28, 2021
2. pylakey commented on Aug 22, 2021

   

   pylakey

   on Aug 22, 2021

   Last edited by pylakey

   More actions

   Hello, what about this feature?
   Maybe I can try to implement this but I am a bit confused about what I should start from, what do you think?

   If I understood right, the magic starts to happen here?
3. collerek commented on Aug 22, 2021

   

   collerek

   on Aug 22, 2021

   CollaboratorAuthor

   More actions

   If you have the capacity i would be more than grateful.

   The new relation should be added as a separate class very similar to ForeignKey one, the difference is that while reverse side of fk exposed the queryset proxy in o2o it should be the related model directly.

   Another difference is that reversed fk returns a list and you can add to the relation, with o2o it should return one related model and setting a new one should replace the old one.

   Yes the "magic" start around there😂 it's registering the other side of the relation in the related model by creating a reversed side of the field.

   Adding the field itself should be easy, the tricky part will come with ensuring that proper joins are declared during the queries and modification of relation manager that intercepts calls to relations in every model.

   The join relation in theory should be exactly the same as with fk, just always one related row per relation will be returned, that's why you need to ensure you cannot add more than one related model from any side of the relation.
4. pylakey commented on Aug 22, 2021

   

   pylakey

   on Aug 22, 2021

   More actions

   Thanks, I'll try and make a PR

   👍React with 👍1Reacted by Gianfranco Cossani Morales
5. maslyankov commented on Sep 16, 2021

   

   maslyankov

   on Sep 16, 2021

   More actions

   Following 👍
6. derzinn commented on Sep 16, 2021

   

   derzinn

   on Sep 16, 2021

   More actions

   This feature would be absolutely welcome! 👍
7. pylakey commented on Sep 16, 2021

   

   pylakey

   on Sep 16, 2021

   More actions

   Thanks, I'll try and make a PR

   I realized that this is beyond my competence, sorry)
8. bstrdsmkr commented on Nov 1, 2021

   

   bstrdsmkr

   on Nov 1, 2021

   More actions

   Is there currently a way to implement this relationship? For example, I have two tables defined as follows:

   CREATE TABLE data_reception.file_info (
   	versioned_filename varchar(64) NOT NULL,
   	md5_checksum bpchar(32) NOT NULL,
   	-- skip some columns for brevity
   	CONSTRAINT file_info_pkey PRIMARY KEY (versioned_filename),
   );
   
   CREATE TABLE data_reception.file_contents (
   	versioned_filename varchar(64) NOT NULL,
   	-- skip some columns for brevity
   	n_samples int4 NULL,
   	CONSTRAINT file_contents_pkey PRIMARY KEY (versioned_filename),
   	CONSTRAINT file_contents_vf_fk FOREIGN KEY (versioned_filename) REFERENCES data_reception.file_info(versioned_filename)
   );

   I've tried to model them, but can't seem to produce a working version. Here is my current iteration:

   class Files(Model):
       class Meta:
           tablename = 'file_info'
           metadata = sqlalchemy.MetaData(schema='data_reception')
           database = database
       versioned_filename: str = String(primary_key=True, max_length=64)
       md5_checksum: str = String(max_length=32, min_length=32, regex=r'[a-fA-F\d]+')
   
   class FileContents(Model):
       class Meta:
           tablename = 'file_contents'
           metadata = sqlalchemy.MetaData(schema='data_reception')
           database=database
       versioned_filename: Files = ForeignKey(Files, related_name='contents', primary_key=True)
       n_samples: int = Integer()

   In order to run some query like:

   Files.objects.select_related("contents").filter(contents__n_samples_gte=500).get()

   But this fails while assembling the Model with ormar.exceptions.ModelDefinitionError: Table has to have a primary key.
9. collerek commented on Nov 2, 2021

   

   collerek

   on Nov 2, 2021

   CollaboratorAuthor

   More actions

   No, unfortunately right now you cannot set relation fields to the primary key.
   This would change with compound primary keys (#137), but that's not yet ready.
10. bstrdsmkr commented on Nov 2, 2021

    

    bstrdsmkr

    on Nov 2, 2021

    More actions

    So before I saw your reply, I was looking through the source to see if this was something I could hack in and discovered that the ForeignKey function hardcodes primary_key=False which explains the error above. I monkey patched the field like this:

    patched_field = ForeignKey(Files, related_name='contents')
    patched_field.primary_key=True
    
    class FileContents(Model):
        class Meta:
            tablename = 'file_contents'
            metadata = sqlalchemy.MetaData(schema='data_reception')
            database=database
        versioned_filename: Files = patched_field
        n_samples: int = Integer()

    And that allowed things to continue and me to actually run the query. I'm not sure if this is an "expected" failure (given the impending compound pk work) or a sort of bug that would otherwise allow this kind of work around to work, so thought I'd report it. The query produces SQL like this:

    SELECT data_reception.file_info.versioned_filename AS versioned_filename,
    	   data_reception.file_info.md5_checksum AS md5_checksum,
    	   qb6461_file_contents.versioned_filename AS qb6461_versioned_filename,
    	   qb6461_file_contents.n_samples AS qb6461_n_samples
    FROM data_reception.file_info
    	LEFT OUTER JOIN data_reception.file_contents AS qb6461_file_contents
    	ON qb6461_file_contents.versioned_filename=file_info.versioned_filename
    WHERE data_reception.file_info.datastream IN ('sgpxsaprvptI6.a1')
    AND data_reception.qb6461_file_contents.n_samples >= 500

    resulting in an SQL error:

    SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "qb6461_file_contents"
      Hint: There is an entry for table "qb6461_file_contents", but it cannot be referenced from this part of the query.
    

    If I remove data_reception (the Postgres Schema) from the AND condition, the query executes as expected.

    Is this a new bug with the schema being inappropriately inserted, or a known effect that will be covered by the compound pk work?