Sering kali kita menghadapi situasi dalam pekerjaan kita yang sedikit rumit atau bahkan sangat rumit. Pada artikel ini saya akan menjelaskan tips dan trik SQLAlchemy agar query SQL yang rumit menjadi kelihatan lebih sederhana karena SQLAlchemy bukan alat untuk menyederhanakan SQL melainkan hanya toolkit dan ORM agar penulisan kode SQL dapat dituliskan dengan gaya Python .

Kasus kita kali ini adalah relasi antar tabel mengenai akun kartu kredit. Tabel terdiri dari 2 bagian:

1. Tabel User
   Tabel ini menampung data user seperti nama, limit kartu kredit.

2. Tabel Transaksi
   Tabel ini menampung data transaksi User dengan field user_id sebagai foreign key yang merelasi ke tabel User

Agar lebih jelas dalam memahami lihat gambar(ERD) dibawah ini:

Merujuk pada ERD diatas maka kita dapat membuat model class pada SQLAlchemy, Saya menggunakan Flask untuk mempermudah. Berikut Class-class yang merepresentasikan ERD diatas.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)
    limit = db.Column(db.Numeric(precision=12), default=0)
    transactions = db.relationship('Transaction', backref='user', lazy='dynamic')

class Transaction(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    amount = db.Column(db.Numeric(precision=12), default=0)

Pada class User saya membuat attribute transactions yang menyatakan merelasi ke class Transaction. Kemudian backref merupakan jalan pintas ketika objek dari class Transaction dibuat maka kita dengan mudah memanggil objek user transaction.user.limit

Menghitung jumlah transaksi User menggunakan Hybrid Property

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)
    limit = db.Column(db.Numeric(precision=12), default=0)
    transactions = db.relationship('Transaction', backref='user', lazy='dynamic')

    @hybrid_property
    def total(self):
        return sum(i.amount for i in self.transactions)

Dengan konfigurasi diatas kita dapat dengan mudah mendapatkan semua total transaksi setiap user dengan method total.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)
    limit = db.Column(db.Numeric(precision=12), default=0)
    transactions = db.relationship('Transaction', backref='user', lazy='dynamic')

    @hybrid_property
    def total(self):
        return sum(i.amount for i in self.transactions)

Hybrid Property bekerja mirip dengan property yang ada pada Python, yang membedakan adalah Hybrid Property dapat digunakan untuk beberapa ekspesi SQL.

Menyaring User Berdasarkan Jumlah Transaksi

@total.expression
    def total(cls):
        return db.select([db.func.sum(Transaction.amount)]).where(Transaction.user_id==cls.id).label('total')

Dengan tambahan kode diatas maka kita dapat menggunakan method total menjadi ekspresi SQL.

user = User.query.filter(User.total > 1_000_000)

print(str(user))

SELECT user.id AS user_id, user.username AS user_username, user."limit" AS user_limit
FROM user
WHERE (SELECT sum("transaction".amount) AS sum_1
FROM "transaction"
WHERE "transaction".user_id = user.id) > 1000000

Mengurutkan User berdasarkan total transaksi

Jika kita ingin mengurutkan User berdasarkan total transaksi juga dapat memanggil method total

user = User.query.order_by(User.total.desc())

print(str(user))

SELECT user.id AS user_id, user.username AS user_username, user."limit" AS user_limit
FROM user ORDER BY (SELECT sum("transaction".amount) AS sum_1
FROM "transaction"
WHERE "transaction".user_id = user.id) DESC

Kustom Comparator Ekspesi SQL

Kasus kali ini adalah, kita ingin melihat daftar User sudah melakukan transaksi berdasarkan limit User tersebut. Kasusnya adalah kita memfilter User yang melakukan total transaksi lebih dari persentase yang kita tentukan (misalnya 50%) berdasarkan limit usr tersebut.

Untuk melakukan Comparator kita akan menggunakan special method atau magic method Python yaitu __eq__, __gt__ dan __lt__

class CaseFiftyComparator(Comparator):
    def __gt__(self, other):
        cls = self.__clause_element__()
        return db.select([db.func.sum(Transaction.amount)]).where(Transaction.user_id==cls.id).label('total') > cls.limit * other


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)
    limit = db.Column(db.Numeric(precision=12), default=0)
    transactions = db.relationship('Transaction', backref='user', lazy='dynamic')

    @hybrid_property
    def total(self):
        return sum(i.amount for i in self.transactions)
    
    @total.expression
    def total(cls):
        return db.select([db.func.sum(Transaction.amount)]).where(Transaction.user_id==cls.id).label('total')


    @hybrid_property
    def percentage(self):
        return sum(i.amount for i in self.transactions) > self.limit * 0.5
    
    @percentage.comparator
    def percentage(cls):
        return CaseFiftyComparator(cls)

Saya menambahkan decorator comparator yang mereturn ke class CaseFiftyComparator. Decorator comparator prinsipnya sama dengan decorator classmethod pada python.

Dengan konfigurasi diatas kita dapat membuat objek python lebih ringkas tapi menghasilkan query SQL yang sesuai.

user = User.query.filter(User.percentage > 0.5)

print(str(user))

SELECT user.id AS user_id, user.username AS user_username, user."limit" AS user_limit
FROM user
WHERE (SELECT sum("transaction".amount) AS sum_1
FROM "transaction"
WHERE "transaction".user_id = user.id) > user."limit" * 0.5

Jika kita ingin membuat comparator lebih kecil gunakan magic method __lt__ atau sama dengan __eq__