Django multiple foreign key to a same table

user_8912012 0
Viewed 1923

I need to log the transaction of the item movement in a warehouse. I've 3 tables as shown in the below image. However Django response error:

ERRORS: chemstore.ItemTransaction: (models.E007) Field 'outbin' has column name 'bin_code_id' that is used by another field.

which is complaining of multiple uses of the same foreign key. Is my table design problem? or is it not allowed under Django? How can I achieve this under Django? thankyou

DB design

[Models]

class BinLocation(models.Model):
    bin_code = models.CharField(max_length=10, unique=True)
    desc = models.CharField(max_length=50)

    def __str__(self):
        return f"{self.bin_code}"

    class Meta:
        indexes = [models.Index(fields=['bin_code'])]


class ItemMaster(models.Model):
    item_code = models.CharField(max_length=20, unique=True)
    desc = models.CharField(max_length=50)
    long_desc = models.CharField(max_length=150, blank=True)
    helper_qty = models.DecimalField(max_digits=10, decimal_places=4)
    unit = models.CharField(max_length=10, blank=False)

    def __str__(self):
        return f"{self.item_code}"

    class Meta:
        verbose_name = "Item"
        verbose_name_plural = "Items"
        indexes = [models.Index(fields=['item_code'])]


class ItemTransaction(models.Model):
    trace_code = models.CharField(max_length=20, unique=False)
    item_code = models.ForeignKey(
        ItemMaster, related_name='trans', on_delete=models.CASCADE, null=False)
    datetime = models.DateTimeField(auto_now=False, auto_now_add=False)
    qty = models.DecimalField(max_digits=10, decimal_places=4)
    unit = models.CharField(max_length=10, blank=False)
    action = models.CharField(
        max_length=1, choices=ACTION, blank=False, null=False)
    in_bin = models.ForeignKey(
        BinLocation, related_name='in_logs', db_column='bin_code_id', on_delete=models.CASCADE, null=False)
    out_bin = models.ForeignKey(
        BinLocation, related_name='out_logs', db_column='bin_code_id', on_delete=models.CASCADE, null=False)
    remarks = models.TextField(blank=True)

    def __str__(self):
        return f"{self.trace_code} {self.datetime} {self.item_code} {dict(ACTION)[self.action]} {self.qty} {self.unit} {self.in_bin} {self.out_bin}"
django python
3 Answers

you have same db_column in two fields so change it

    in_bin = models.ForeignKey(
        BinLocation, related_name='in_logs', db_column='bin_code_id', on_delete=models.CASCADE, null=False)
    out_bin = models.ForeignKey(
        BinLocation, related_name='out_logs', db_column='other_bin_code', on_delete=models.CASCADE, null=False) /*change db_column whatever you want but it should be unique*/
user_122617520

If are linked to the same model name, You should use different related_name for each foreign_key filed . here is the exemple :

    address1 = models.ForeignKey(Address, verbose_name=_("Address1"),related_name="Address1", null=True, blank=True,on_delete=models.SET_NULL)

    address2 = models.ForeignKey(Address, verbose_name=_("Address2"),related_name="Address2", null=True, blank=True,on_delete=models.SET_NULL)
user_78165840

thank you for everyone helped. According to Aleksei and Tabaane, it is my DB design issue (broken the RDBMS rule) rather than Django issue. I searched online and find something similar: ONE-TO-MANY DB design pattern

In my case, I should store in bin and out bin as separated transaction instead of both in and out in a single transaction. This is my solution. thankyou.

p.s. alternative solution: I keep in bin and out bin as single transaction, but I don't use foreign key for bins, query both in bin and out bin for the bin selection by client application.

user_89120120
Related