#StackBounty: #python #django #django-forms #django-templates #django-admin Django ModelAdmin use custom query

Bounty: 50

Hi currently i’m having trouble add a custom ModelAdmin section to the app admin side without using any defined model in the models.py

For example i have 3 models(topups, withdrawals, transfers) and i would like to add a separate ModelAdmin transactions section that a combination from those 3 model, because i like it’s paging, change listing and detail view.

My models:

#TOPUP
class TopUp(SafeDeleteModel):
    class Meta:
        db_table = "topups"
        verbose_name = 'TopUp Request'
        verbose_name_plural = 'TopUp Requests'

    user = models.ForeignKey("backend.User", null=True, blank=True, related_name='user_toptup', on_delete=models.CASCADE)
    currency = models.ForeignKey("backend.Currency", null=True, blank=True, related_name='user_topup_currency', on_delete=models.SET_NULL)

    TOPUP_METHOD_CHOICES = [
        (1, 'method 1'),
        (2, 'method 2')
    ]
    method = models.PositiveSmallIntegerField("Method", choices=TOPUP_METHOD_CHOICES)

    amount = models.DecimalField("Amount", max_digits=65, decimal_places=0, default=0)
    fee = models.DecimalField("Fee", max_digits=65, decimal_places=0, default=0)

    TOPUP_STATUS_CHOICES = [
        (0, 'Pending'),
        (1, 'Success'),
        (2, 'Failed'),
    ]
    status = models.PositiveSmallIntegerField("Status", choices=TOPUP_STATUS_CHOICES, default=0)
    created = models.DateTimeField(auto_now_add=True)
    received = models.DateTimeField(null=True, blank=True)

# WITHDRAWALS
class Withdrawals(SafeDeleteModel):
    class Meta:
        db_table = "withdrawals"
        verbose_name = 'Withdraw Request'
        verbose_name_plural = 'Withdraw Requests'

    user = models.ForeignKey("backend.User", null=True, blank=True, related_name='user_withdrawal', on_delete=models.CASCADE)
    currency = models.ForeignKey("backend.Currency", null=True, blank=True, related_name='user_withdrawal_currency', on_delete=models.SET_NULL)

    WITHDRAWAL_METHOD_CHOICES = [
        (1, 'method 1'),
        (2, 'method 2')
    ]
    method = models.PositiveSmallIntegerField("Method", choices=WITHDRAWAL_METHOD_CHOICES)
    to_bank = models.ForeignKey("backend.UserBank", null=True, blank=True, related_name='user_withdrawal_userbank', on_delete=models.SET_NULL, db_column='to_bank')
    to_address = models.CharField("To address", max_length=255, null=True, blank=True, db_column='to_address')
    to_card = models.ForeignKey("backend.CardBinding", null=True, blank=True, related_name='user_withdrawal_to_card', on_delete=models.SET_NULL, db_column='to_card')
    amount = models.DecimalField("Amount", max_digits=65, decimal_places=0, default=0)
    fee = models.DecimalField("Fee", max_digits=65, decimal_places=0, default=0)

    WITHDRAWAL_STATUS_CHOICES = [
        (0, 'Pending'),
        (1, 'success'),
        (2, 'failed')
    ]
    status = models.PositiveSmallIntegerField("Status", choices=WITHDRAWAL_STATUS_CHOICES, default=0)
    created = models.DateTimeField(auto_now_add=True)
    submitted = models.DateTimeField(null=True, blank=True)
    confirmed = models.DateTimeField(null=True, blank=True)

# TRANSFERS

class Transfers(SafeDeleteModel):
    class Meta:
        db_table = "transfers"
        verbose_name = 'Transfer'
        verbose_name_plural = 'Transfers'

    user = models.ForeignKey("backend.User", null=True, blank=True, related_name='user_transfer', on_delete=models.CASCADE)
    currency = models.ForeignKey("backend.Currency", null=True, blank=True, related_name='user_transfer_currency', on_delete=models.SET_NULL)

    TRANSFER_METHOD_CHOICES = [
        (2, 'method 1'),
        (3, 'method 2')
    ]
    method = models.PositiveSmallIntegerField("Method", choices=TRANSFER_METHOD_CHOICES)

    to_address = models.CharField("To Address", max_length=255, null=True, blank=True, db_column='to_address')
    to_account = models.ForeignKey("backend.User", null=True, blank=True, related_name='user_transfer_to_account', on_delete=models.SET_NULL, db_column='to_account')
    amount = models.DecimalField("Amount", max_digits=65, decimal_places=0, default=0)
    fee = models.DecimalField("Fee", max_digits=65, decimal_places=0, default=0)

    TRANSFER_STATUS_CHOICES = [
        (0, 'Pending'),
        (1, 'Success'),
        (2, 'Failed')
    ]
    status = models.PositiveSmallIntegerField("Status", choices=TRANSFER_STATUS_CHOICES, default=0)
    created = models.DateTimeField(auto_now_add=True)
    submitted = models.DateTimeField(null=True, blank=True)
    confirmed = models.DateTimeField(null=True, blank=True)

So if i have a query for example like so:

        cursor = connection.cursor()
        cursor.execute('''
            SELECT * FROM 
                (SELECT id,
                    user_id, 
                    'top up'  AS transaction_type, 
                    method, 
                    NULL     AS to_bank, 
                    NULL     AS to_address, 
                    user_id  AS to_account, 
                    NULL     AS to_card, 
                    currency_id, 
                    amount, 
                    fee,
                    status, 
                    created AS created, 
                    received AS confirmed 
                FROM   topups 
                WHERE deleted IS NULL
                UNION ALL 
                SELECT id,
                    user_id, 
                    'transfer' AS transaction_type, 
                    method, 
                    NULL       AS to_bank, 
                    to_address, 
                    to_account, 
                    NULL       AS to_card, 
                    currency_id, 
                    amount, 
                    fee,
                    status, 
                    created AS created, 
                    confirmed  AS confirmed 
                FROM   transfers 
                WHERE deleted IS NULL
                UNION ALL 
                SELECT id,
                    user_id, 
                    'withdrawal' AS transaction_type, 
                    method, 
                    to_bank, 
                    to_address, 
                    NULL         AS to_account, 
                    to_card, 
                    currency_id, 
                    amount, 
                    fee,
                    status, 
                    created AS created, 
                    confirmed    AS confirmed 
                FROM   withdrawals
                WHERE deleted IS NULL
                ) AS T
            ORDER BY created DESC'''
        )

        row = namedtuplefetchall(cursor)

It return UNION of 3 tables and with columns like so:

  {
    "user_id": 120,
    "transaction_type": "transfer",
    "method": 3,
    "to_bank" null,
    "to_card" null,
    "to_address" null,
    "to_account": 170,
    "currency_id": 1,
    "amount": "-10000",
    "fee": "100000000",
    "status": 2,
    "created": 1582272307,
    "confirmed": 1582272307
  },

How do i make a ModelAdmin to use this query ? i haven’t found any solution for admin section that use only raw query instead of model


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.