How to create ForeignKey to self?

[hlozadaccs]

What is the optimal solution for this model?

from django.db import models

class Business(models.Model):
    business_name=models.CharField(
        max_length=128)

    class Meta:
        db_table='business'
        verbose_name='Business'
        verbose_name_plural='Business'

# This class have a foreign key to self, parent_id to id
class AccountingEntries(models.Model):
    ENTRY_TYPES = (
        ('D', 'Debtor'),
        ('A', 'creditor'),
    )
    parent=models.ForeignKey(
        'self',
        null=True,
        blank=True,
        on_delete=models.CASCADE)
    business=models.ForeignKey(
        Business,
        on_delete=models.CASCADE)
    entry_code=models.CharField(
        null=True,
        blank=True,
        max_length=18)
    entry_name=models.CharField(
        max_length=128)
    entry_type=models.CharField(
        max_length=2,
        choices=ENTRY_TYPES,
        default='D')
    status=models.BooleanField(
        default=True)

    class Meta:
        unique_together = ['id', 'business']
        db_table='accounting_entries'
        verbose_name='Accounting Entries'
        verbose_name_plural='Accounting Entries'

[saicitus]

Hey @imatsu, there is the construct TenantForeignKey which by default creates composite foreign keys. Can you try that out?

class AccountingEntries(TenantModel):
    ENTRY_TYPES = (
        ('D', 'Debtor'),
        ('A', 'creditor'),
    )
    parent=TenantForeignKey(
        'self',
        null=True,
        blank=True,
        on_delete=models.CASCADE)
    business=models.ForeignKey(
        Business,
        on_delete=models.CASCADE)
    entry_code=models.CharField(
        null=True,
        blank=True,
        max_length=18)
    entry_name=models.CharField(
        max_length=128)
    entry_type=models.CharField(
        max_length=2,
        choices=ENTRY_TYPES,
        default='D')
    status=models.BooleanField(
        default=True)

    class Meta:
        unique_together = ['id', 'business']
        db_table='accounting_entries'
        verbose_name='Accounting Entries'
        verbose_name_plural='Accounting Entries'

[hlozadaccs]

By definition, in the tutorial, you need to create a composite primary key

Citus requires that primary and foreign key constraints include the distribution column. This requirement makes enforcing these constraints much more efficient in a distributed environment as only a single node has to be checked to guarantee them.

Like this

--Before
CREATE TABLE campaigns (
  id bigserial PRIMARY KEY,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blacklisted_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

--After
CREATE TABLE campaigns (
  id bigserial,       -- was: PRIMARY KEY
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blacklisted_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  PRIMARY KEY (company_id, id) -- added
);

My problem is to create a field that I can not relate to a composite primary key, after I update my model to make it compatible.

Alternative: move relational logic to programming Example:

class AccountingEntries(models.Model):
    # fields
    parent=models.IntegerField(
        verbose_name='Padre del asiento contable',
        null=True,
        blank=True)
    # more fields

    def get_parent(self):
        if self.parent:
            return AccountingEntries.objects \
                .get(business=self.business, id=self.parent)

@receiver(pre_save, sender=AccountingEntries)
def check_parent(sender, instance, **kwargs):
    if instance.parent:
        if isinstance(instance.parent, int):
            try:
                entry_parent = AccountingEntries.objects.get(
                    business=instance.business, id=instance.parent)
            except AccountingEntries.DoesNotExist:
                message = 'Parent Does Not Exist'
                raise ValidationError(message, code='invalid')
        elif isinstance(instance.parent, AccountingEntries):
            entry_parent = instance.parent

        instance.parent = entry_parent.id

What do you think about this?

[hlozadaccs]

@saicitus I have not tried that solution yet, I will try, thank you very much :smile:

[hlozadaccs]

@saicitus, I had to make a migration

python manage.py makemigrations accounting --empty --name regenerate_keys

# Generated by Django 2.0.5 on 2018-05-17 18:52

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        ('accounting', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL(
            "ALTER TABLE accounting_entries DROP CONSTRAINT accounting_entries_parent_id_8f6eaa64_fk_accounting_entries_id;",
            "ALTER TABLE accounting_entries ADD CONSTRAINT accounting_entries_parent_id_8f6eaa64_fk_accounting_entries_id FOREIGN KEY(id, business_id) REFERENCES accounting_entries(id, business_id);",
        ),
        migrations.RunSQL(
            "ALTER TABLE accounting_entries DROP CONSTRAINT accounting_entries_pkey;",
            "ALTER TABLE accounting_entries ADD CONSTRAINT accounting_entries_pkey PRIMARY KEY(id, business_id);",
        )
    ]