category model

edit.html

{% extends "base.html" %}
{% import 'bootstrap/wtf.html' as wtf %}

{% block head %}
    {{super()}}
    <link href="{{ url_for('static', filename='css/dashboard.css') }}" rel="stylesheet"/ >
{% endblock %}

{% block app_content %}
{{ super() }}
<main role="main" class="col-md-9 ml-sm-auto col-lg-10 pt-3 px-4">
        <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pb-2 mb-3 border-bottom">
          <h1 class="h2">Category</h1>
          <div class="btn-toolbar mb-2 mb-md-0">
            <div class="btn-group mr-2">
                    <button class="btn btn-sm btn-outline-secondary" id="btn-list">
                        <span data-feather="calendar"></span>
                        Back
                    </button>
            </div>
          </div>
        </div>
        {% for field, errors in form.errors.items() %}
        <div class="alert alert-error" style="color:red">
            {{ form[field].label }}: {{ ', '.join(errors) }}
        </div>
        {% endfor %}
        <div class="table-responsive">
            {% if form %}
                {{ wtf.quick_form(form) }}
            {% endif %}
        </div>
      </main>
{% endblock %}

{% block scripts %}
{{super()}}
<script>
    feather.replace()
</script>
  
<script>
    $(document).ready(function(){
        $("#btn-new").on('click', function(e){
            e.preventDefault();
            window.location.href = "{{ url_for('category.add') }}";
        });

        $("#btn-list").on('click', function(e){
            e.preventDefault();
            window.location.href = "{{ url_for('category.index') }}";
        });
    });
</script>
{% endblock %}

form.py

from flask_wtf import FlaskForm
from wtforms import validators, StringField, SubmitField
from wtforms.validators import DataRequired, EqualTo, ValidationError
from wtforms.ext.sqlalchemy.fields import QuerySelectField
from app.category.models import Category


def categories(columns=None):
    return Category.query


class CategoryForm(FlaskForm):
    name_en = StringField('Category (en)',[
        validators.DataRequired(),
        validators.Length(min=1, max=100)
    ])
    name_cn = StringField('Category (cn)',[
        validators.Length(min=0, max=100)
    ])
    name_my = StringField('Category (my)',[
        validators.Length(min=0, max=100)
    ])

    parent = QuerySelectField('Parent', query_factory=categories, allow_blank=True, 
                                          get_label='name_en', get_pk=lambda a: a.id,
                                          blank_text=u'Select a categories...')
    submit = SubmitField('Save')

model.py

from app import db
import time
from datetime import datetime
from geoalchemy2 import Geometry, func
from geoalchemy2.elements import WKTElement
from datetime import datetime, date 

# Category relationship table
category_tree = db.Table(
    'category_tree', 
    db.Column('parent_id', db.Integer, db.ForeignKey('category.id')),
    db.Column('children_id', db.Integer, db.ForeignKey('category.id'))
)


""" WorkType = Category """
class Category(db.Model):
    __tablename__ = 'category'
    id = db.Column(db.Integer, primary_key=True)
    name_en = db.Column(db.String(100), nullable=False, unique=True)
    name_my = db.Column(db.String(100), nullable=True)
    name_cn = db.Column(db.String(100), nullable=True)
    created_date = db.Column(db.DateTime, server_default=db.func.current_timestamp())
    created_by = db.Column(db.String(100), default="Admin")
    updated_date = db.Column(db.DateTime, server_default=db.func.current_timestamp())
    updated_by = db.Column(db.String(100), default="Admin")
    
    children = db.relationship(
        'Category', 
        secondary=category_tree,
        primaryjoin=("Category.id==category_tree.c.parent_id"),
        secondaryjoin=("Category.id==category_tree.c.children_id"),
        backref=db.backref('parents', lazy='dynamic'),
        lazy='dynamic'
    )

    def remove_children(self):
        if self.children.all() and len(self.children.all()) > 0:
            for c in self.children:
                self.children.remove(c)
    
    def audit_log(self, username):
        self.updated_date = datetime.now()
        self.updated_by = username

    def __repr__(self):
        return '<Category> {}'.format(self.id)

    @property
    def serialize(self):
        return {
            'id': self.id,
            'name_en': self.name_en,
            'name_my': self.name_my,
            'name_cn': self.name_cn,
            'parent_id' : [i.id for i in self.children]
        }

    

services.py

from flask import Flask, json, url_for, request, render_template, redirect, flash, make_response, session, jsonify
from app import db
from .models import *

def get_category_parent_reference(category):
    childref = None
    if category.children.all() and len(category.children.all()) > 0:
        childref = category.children[0]
    return childref

def get_self_refences_banlist(category):
    banlist = []
    categories = Category.query.all()
    for cat in categories:
        if cat.children.first():
            if cat.children.first().id == category.id:
                banlist.append(cat.id)
    return banlist

views.py

@bp.route('/edit/<id>', methods = ['GET','POST'])
@login_required
@admin_required
def edit(id=None, error=None):
    
    id is None or redirect(url_for('category.index'))
    
    category = Category.query.get(id)
    if category is None:
        return render_template('404.html'), 404

    childref = get_category_parent_reference(category)
    form = CategoryForm(parent=childref, 
                name_en=category.name_en,
                name_my=category.name_my,
                name_cn=category.name_cn)
    
    if form.validate_on_submit():
        banlist = get_self_refences_banlist(category)
        
        """ validation for infinite loop """
        if form.parent.data is not None and form.parent.data.id == category.id:
            flash(u'Error: Parent category cannot be the same', 'green')
            return redirect( url_for('category.edit', id=id))
        else:
            if banlist is not None and len(banlist) > 0:
                for i in banlist:
                    if form.parent.data.id == i:
                        flash(u'Error: Infinite self reference, please choose other parent category', 'green')
                        return redirect( url_for('category.edit', id=id))
        
        category.name_en = form.name_en.data.strip()
        category.name_cn = form.name_cn.data.strip()
        category.name_my = form.name_my.data.strip()
        category.remove_children()
        category.audit_log(current_user.displayname())

        if form.parent.data is not None:
            category.children.append(form.parent.data)

        db.session.add(category)
        db.session.commit()    
        flash(u'Category has been update successfully!', 'green')
            
    return render_template('category/edit.html', error=error, form=form)