def table_city(state, city): """It includes top 10 data""" qry = db.session.query( basic.name, func.count(state_fips.description), func.round(func.avg(metrics.tuition_in_state)), func.round(func.avg(metrics.tuition_out_of_state)), func.round(func.avg(metrics.instructional_expenditure_per_fte)), func.round(func.avg(metrics.faculty_salary)), func.round(func.avg(metrics.tuition_revenue_per_fte)), ).filter(basic.id==metrics.id).filter(state_fips.code==basic.state_fips) qry = qry.filter(state_fips.description == state) qry = qry.filter(basic.city == city) qry = qry.group_by(basic.name) qry = qry.order_by(basic.name) table_data = [] for name,count,tuition_IS,tuition_OS, expenditure, faculty_Sal, revenue in qry: json = {} json["State"] = name json["No_Schools"] = count json["tuitionIn"] = tuition_IS json["tuitionOut"] = tuition_OS json["expenditure"] = expenditure json["facSalary"] = faculty_Sal json["tuiRevenue"] = revenue table_data.append(json) # Return a list of the column names (sample names) json_data = jsonify(table_data) return json_data
def metric_country(): """It includes Country Averages""" qry = db.session.query( func.count(basic.state), func.round(func.avg(metrics.tuition_in_state)), func.round(func.avg(metrics.tuition_out_of_state)), func.round(func.avg(metrics.instructional_expenditure_per_fte)), func.round(func.avg(metrics.faculty_salary)), func.round(func.avg(metrics.tuition_revenue_per_fte)), ).filter(basic.id==metrics.id) country_data = [] for count,tuition_IS,tuition_OS, expenditure, faculty_Sal, revenue in qry: json = {} json["No_Schools"] = count json["tuitionIn"] = tuition_IS json["tuitionOut"] = tuition_OS json["expenditure"] = expenditure json["facSalary"] = faculty_Sal json["tuiRevenue"] = revenue country_data.append(json) # Return a list of the column names (sample names) return jsonify(country_data)
def produktionsmittel(): produktionsmittel_qry = db.session.query(Kaeufe.id, Angebote.name, Angebote.beschreibung,\ func.concat(func.round(Angebote.preis, 2), " Std.").label("preis"),\ func.concat(func.round(func.coalesce(func.sum(Produktionsmittel.prozent_gebraucht), 0), 2), " %").\ label("prozent_gebraucht"))\ .select_from(Kaeufe)\ .filter(Kaeufe.betrieb==current_user.id).outerjoin(Produktionsmittel,\ Kaeufe.id==Produktionsmittel.kauf).join(Angebote, Kaeufe.angebot==Angebote.id).\ group_by(Kaeufe, Angebote, Produktionsmittel.kauf) produktionsmittel_aktiv = produktionsmittel_qry.having(func.coalesce(func.sum(Produktionsmittel.prozent_gebraucht).\ label("prozent_gebraucht"), 0).label("prozent_gebraucht")<100).all() produktionsmittel_inaktiv = produktionsmittel_qry.having(func.coalesce(func.sum(Produktionsmittel.prozent_gebraucht).\ label("prozent_gebraucht"), 0).label("prozent_gebraucht")== 100).all() table_aktiv = ProduktionsmittelTable( produktionsmittel_aktiv, no_items="(Keine Produktionsmittel vorhanden.)") table_inaktiv = ProduktionsmittelTable( produktionsmittel_inaktiv, no_items="(Noch keine Produktionsmittel verbraucht.)") return render_template('produktionsmittel.html', table_aktiv=table_aktiv, table_inaktiv=table_inaktiv)
def last_day(self): utc = timezone("UTC") eastern = timezone('US/Eastern') hours = 30 now = datetime.utcnow() query = db.session.query( func.round(func.avg(Reading.temperature)), func.round(func.avg(Reading.humidity)), Reading.timestamp )\ .filter_by(channel=self.channel)\ .filter(Reading.timestamp > func.datetime('now', '-30 hours'))\ .group_by(func.strftime('%d %H', Reading.timestamp))\ .order_by(Reading.timestamp.desc())\ .limit(hours) readings = query.all() times = ["" for i in range(hours)] est = utc.localize(datetime.utcnow()).astimezone(eastern) for i in range(0, hours): times[i] = "\"" + ( est - timedelta(hours=i)).strftime("%I%p").lstrip('0') + "\"" times.reverse() temps = [0 for i in range(hours)] hums = [0 for i in range(hours)] for r in readings: idx = hours - self._hours(now - r[2]) - 1 temps[idx] = r[0] / 10 hums[idx] = r[1] self.last_day_data = { "temperatures": temps, "humidities": hums, "timestamps": times } return self.last_day_data
def last_day(self): query = db.session.query( func.round(func.avg(Reading.temperature)), func.round(func.avg(Reading.humidity)), Reading.timestamp )\ .filter_by(channel=self.channel)\ .group_by(func.strftime('%H', Reading.timestamp))\ .order_by(Reading.timestamp.desc())\ .limit(30) return [{ "temperature": r[0], "humidity": r[1], "timestamp": r[2] } for r in query.all()]
async def get_sales_by_customer(from_date, to_date): """Return report about sales by customer in set period of time.""" expression = order_details.c.unit_price * order_details.c.quantity * (1 - order_details.c.discount) query = select( [ orders.c.customer_id, customers.c.company_name, func.round(func.sum(expression)).label('profit') ] ).select_from( join(join(orders, order_details, orders.c.order_id == order_details.c.order_id), customers, orders.c.customer_id == customers.c.customer_id) ).where( and_( orders.c.order_date >= from_date, orders.c.order_date <= to_date, ) ).group_by( orders.c.customer_id, customers.c.company_name, ).order_by( desc('profit') ) print(query) return await database.fetch_all(query=query)
def last_day(self): query = db.session.query( func.round(func.avg(Reading.temperature)), func.round(func.avg(Reading.humidity)), Reading.timestamp )\ .filter_by(channel=self.channel)\ .group_by(func.strftime('%H', Reading.timestamp))\ .order_by(Reading.timestamp.desc())\ .limit(30) return [ { "temperature": r[0], "humidity": r[1], "timestamp": r[2] } for r in query.all() ]
def tuition(): """Return a dictionary of tuitions grouped by state.""" qry = db.session.query(basic.state,func.round(func.avg(metrics.tuition_in_state)),func.round(func.avg(metrics.tuition_out_of_state))).filter(basic.id==metrics.id) qry = qry.group_by(basic.state) tuition = [] for state,tuition_IS,tuition_OS in qry: json = {} json["State"] = state json["tuitionIn"] = tuition_IS json["tuitionOut"] = tuition_OS tuition.append(json) return jsonify(tuition)
def get_total_chart(user_id): days_interval = datetime.today() - timedelta(days=365) total = db.session.query( Total.date, func.round(cast(func.sum(Total.value), NUMERIC), 2)).join(Total.portfolio).filter( Portfolio.user_id == user_id, Total.date >= days_interval).group_by( Total.date).order_by(Total.date).all() labels = [] values = [] for portfolio in total: date, value = portfolio labels.append(date) values.append(value) return labels, values
def select_departments(): """ Select departments as a list of departments with such parameters as id, name, manager, phone, average salary, count of employees. :return: the list of departments """ session = db.session departments = session.query(Department.id, Department.name, Department.manager, Department.phone, func.round(func.avg(Employee.salary), 2).label('average_salary'), func.count(Employee.id).label('count_of_employees')) \ .select_from(Department) \ .join(Employee,isouter=True) \ .group_by(Department.id) session.close() return departments
def meine_kaeufe(): try: user_type = session["user_type"] except: user_type = "nutzer" if user_type == "betrieb": return redirect(url_for('auth.zurueck')) else: session["user_type"] = "nutzer" kaufhistorie = db.session.query(Kaeufe.id, Angebote.name, Angebote.beschreibung,\ func.concat(func.round(Angebote.preis, 2), " Std.").label("preis") ).\ select_from(Kaeufe).\ filter_by(nutzer=current_user.id).\ join(Angebote, Kaeufe.angebot==Angebote.id).all() kaufh_table = KaeufeTable(kaufhistorie, no_items="(Noch keine Käufe.)") return render_template('meine_kaeufe.html', kaufh_table=kaufh_table)
def index(): top_graded = Movie.query.join(Review)\ .with_entities(Movie.title, Movie.year, Movie.id, func.round(func.avg(Review.grade), 2)\ .label('avg'))\ .group_by(Movie.id).order_by(desc('avg')).limit(10).all() newbies = Movie.query.order_by(Movie.timestamp.desc()).limit(10).all() form = RequestForm() if form.validate_on_submit() and current_user.is_authenticated: movreq = MovieRequest(user_id=current_user.id, name=form.name.data, year=form.year.data, other_info=form.other_info.data) db.session.add(movreq) db.session.commit() flash('Request sent!') return redirect(url_for('main.index')) return render_template('index.html', title='home', top_graded=top_graded, newbies=newbies, form=form)
def test_index(self): response = self.client.get('/') department = Department('IT', 'Ivanov I.I', '+38(093)1548-093') employee_1 = Employee('Anna', 'Olekseevna', 'Ivanova', 22, '1998-01-01', '+38(099)1548123', 'trainee', 2, 1, 10000) employee_2 = Employee('Inna', 'Igorevna', 'Leonova', 22, '1998-01-01', '+38(099)1548123', 'trainee', 2, 1, 11000) db.session.add(department) db.session.add(employee_1) db.session.add(employee_2) db.session.commit() departments = db.session.query( Department.id, Department.name, Department.manager, Department.phone, func.round(func.avg(Employee.salary), 2).label('average_salary'), \ func.count(Employee.id).label('count_of_employees')).select_from(Department). \ join(Employee, isouter=True).group_by(Department.id) self.assertEqual(departments[0].id, 1) self.assertEqual(departments[0].name, 'IT') self.assertEqual(departments[0].manager, 'Ivanov I.I') self.assertEqual(departments[0].phone, '+38(093)1548-093') self.assertEqual(departments[0].average_salary, 10500.00) self.assertEqual(departments[0].count_of_employees, 2)
def updateAllocation(self, settings, question_cap=DEFAULT_QUESTION_CAP): # Get all existing allocations from the DB and their questions allocsByType = dict() hist_sel = float(settings.get('hist_sel', '0')) if hist_sel > 0.001: allocsByType['historical'] = [] # Only get half the question cap if there's not much chance of the questions being used if hist_sel < 0.5 and 'question_cap_historical' not in settings: settings['question_cap_historical'] = int(settings.get('question_cap', DEFAULT_QUESTION_CAP)) / 2 if hist_sel < 0.999: # NB: Need to add rows for each distinct question type, otherwise won't try and assign them allocsByType['regular'] = [] allocsByType['template'] = [] # Fetch all existing allocations, divide by allocType for (dbAlloc, dbQn) in (Session.query(db.Allocation, db.Question) .join(db.Question) .filter(db.Allocation.studentId == self.student.studentId) .filter(db.Allocation.active == True) .filter(db.Allocation.lectureId == self.dbLec.lectureId)): if not(dbQn.active) or (dbAlloc.allocationTime < dbQn.lastUpdate): # Question has been removed or is stale dbAlloc.active = False else: # Still around, so save it if (dbAlloc.allocType or dbQn.defAllocType) in allocsByType: # NB: If hist_sel has changed, we might not want some types any more allocsByType[dbAlloc.allocType or dbQn.defAllocType].append(dict(alloc=dbAlloc, question=dbQn)) # Each question type should have at most question_cap questions for (allocType, allocs) in allocsByType.items(): questionCap = int(settings.get('question_cap_' + allocType, settings.get('question_cap', DEFAULT_QUESTION_CAP))) # If there's too many allocs, throw some away for i in sorted(random.sample(xrange(len(allocs)), max(len(allocs) - questionCap, 0)), reverse=True): allocs[i]['alloc'].active = False del allocs[i] # If there's questions to spare, and requested to do so, reallocate questions if len(allocs) == questionCap and self.reAllocQuestions: if self.targetDifficulty is None: raise ValueError("Must have a target difficulty to know what to remove") # Make ranking how likely questions are, based on targetDifficulty suitability = [] for a in allocs: if a['question'].timesAnswered == 0: # New questions should be added regardless suitability.append(1) else: suitability.append(1 - abs(self.targetDifficulty - float(a['question'].timesCorrect) / a['question'].timesAnswered)) ranking = sorted(range(len(allocs)), key=lambda k: suitability[k]) # Remove the least likely tenth for i in sorted(ranking[0:len(allocs) / 10 + 1], reverse=True): allocs[i]['alloc'].active = False del allocs[i] # Assign required questions randomly if len(allocs) < questionCap: query = Session.query(db.Question).filter_by(qnType='tw_questiontemplate' if allocType == 'template' else 'tw_latexquestion').filter_by(active=True) if allocType == 'historical': # Get questions from lectures "before" the current one targetQuestions = (Session.query(db.LectureQuestion.questionId) .join(db.Lecture) .filter(db.Lecture.plonePath.startswith(re.sub(r'/[^/]+/?$', '/', self.dbLec.plonePath))) .filter(db.Lecture.plonePath < self.dbLec.plonePath) .subquery()) query = query.filter(db.Question.questionId.in_(targetQuestions)) else: # Git questions from current lecture query = query.filter(db.Question.lectures.contains(self.dbLec)) # Filter out anything already allocated allocIds = [a['alloc'].questionId for a in allocs] if len(allocIds) > 0: query = query.filter(~db.Question.questionId.in_(allocIds)) # Give a target difficulty if self.targetDifficulty is not None: query = query.order_by(func.abs(round(self.targetDifficulty * 50) - func.round((50.0 * db.Question.timesCorrect) / db.Question.timesAnswered))) for dbQn in query.order_by(func.random()).limit(max(questionCap - len(allocs), 0)): dbAlloc = db.Allocation( studentId=self.student.studentId, questionId=dbQn.questionId, lectureId=self.dbLec.lectureId, allocationTime=datetime.datetime.utcnow(), allocType='historical' if allocType == 'historical' else None, ) Session.add(dbAlloc) allocs.append(dict(alloc=dbAlloc, question=dbQn, new=True)) Session.flush() for allocType, allocs in allocsByType.items(): for a in allocs: yield ( self._questionUrl(a['alloc'].publicId), allocType, a['question'], )
def get_ridership_w_sqlalchemy(summary_date): """""" # !!!As of now this function is incomplete, primarily due to the # fact that the passenger_census table doesn't appear to be a # part of the 'trans' sqlalchemy model at this time from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.sql import func from trimet.model.oracle.trans \ import Location as loc, Passenger_Census as pc oracle_url = 'oracle://{user}:{pw}@{db}' engine = create_engine(oracle_url) Session = sessionmaker(bind=engine) session = Session() if not summary_date: summary_date = session.query(func.max(pc.summary_begin_date)).scalar() day_dict = dict() for service_day in ('W', 'S', 'U'): day_query = ( session.query( pc.location_id, func.sum(pc.ons).label('ons'), func.sum(pc.offs).label('offs'), pc.summary_begin_date) .filter(pc.service_key == service_day) .group_by(pc.location_id, pc.summary_begin_date)) day_dict[service_day] = day_query w = day_dict['W'] s = day_dict['S'] u = day_dict['U'] rows = ( session.query( loc.public_location_description.label('stop_desc'), w.ons.label('wkdy_ons'), w.offs.label('wkdy_offs'), s.ons.label('sat_ons'), s.offs.label('sat_offs'), u.ons.label('sun_ons'), u.offs.label('sun_offs'), func.round(loc.x_coordinate, 2).label('x_coord'), func.round(loc.y_coordinate, 2).label('y_coord'), w.summary_begin_date.label('begin_date')) .join(loc) .outerjoin(s) .outerjoin(u) .filter( loc.location_id == w.location.id, w.summary_begin_date == summary_date, w.location_id == s.location_id, w.summary_begin_date == s.summary_begin_date, s.location_id == u.location_id, s.summary_begin_date == u.summary_begin_date) .order_by(loc.location_id) .all() ) # http://stackoverflow.com/questions/2258072 row_types = [col.type for col in rows.columns] return rows, row_types
def build_filter( field_name, data_type, filter_type, inverted, value ): if DEBUG_FILTERS: logger.info('build filter: %r, %r, %r, %r, %r', field_name, data_type, filter_type, inverted, value) expression = None col = column(field_name) if data_type in ['integer', 'float', 'decimal']: col = cast(col, sqlalchemy.sql.sqltypes.Numeric) elif data_type == 'boolean': col = cast(col, sqlalchemy.sql.sqltypes.Boolean) if data_type == 'string': col = cast(col, sqlalchemy.sql.sqltypes.Text) if filter_type in ['exact', 'eq']: if data_type == 'string': value = str(value) expression = col == value if data_type == 'list': expression = text( "'%s'=any(string_to_array(%s,'%s'))" % (value, field_name, LIST_DELIMITER_SQL_ARRAY)) elif filter_type == 'about': decimals = 0 if '.' in value: decimals = len(value.split('.')[1]) expression = func.round(col, decimals) == value if DEBUG_FILTERS: logger.info( 'create "about" expression for: %r, %r, decimals %r', field_name, value, decimals) elif filter_type == 'contains': if data_type == 'string': value = str(value) expression = col.contains(value) elif filter_type == 'icontains': if data_type == 'string': value = str(value) expression = col.ilike('%{value}%'.format(value=value)) elif filter_type == 'lt': expression = col < value elif filter_type == 'lte': expression = col <= value elif filter_type == 'gt': expression = col > value elif filter_type == 'gte': expression = col >= value elif filter_type == 'is_blank': if data_type == 'string': col = func.trim(col) if value and str(value).lower() == 'true': expression = col == None if data_type == 'string': expression = col == '' else: expression = col != None if data_type == 'string': col = func.trim(col) if (data_type == 'string' or data_type == 'list'): expression = col != '' elif filter_type == 'is_null': if value and str(value).lower() == 'true': expression = col == None else: expression = col != None # TODO: test that col <> '' expression is created elif filter_type == 'in': if data_type == 'list': # NOTE: for the list type, interpret "in" as any of the # given values are in the field temp_expressions = [] for _val in value: temp_expressions.append(col.ilike('%{value}%'.format(value=_val))) expression = or_(*temp_expressions) else: expression = col.in_(value) elif filter_type == 'ne': if data_type == 'string': value = str(value) expression = col != value elif filter_type == 'range': if len(value) != 2: logger.error('field: %r, val: %r, ' 'range expression must be list of length 2', field_name, value) else: expression = col.between(value[0], value[1], symmetric=True) else: logger.error( 'field: %r, unknown filter type: %r for value: %r', field_name, filter_type, value) if inverted: expression = not_(expression) return expression
def updateAllocation(self, settings, question_cap=DEFAULT_QUESTION_CAP): # Get all existing allocations from the DB and their questions allocsByType = dict() hist_sel = float(settings.get('hist_sel', '0')) if hist_sel > 0.001: allocsByType['historical'] = [] # Only get half the question cap if there's not much chance of the questions being used if hist_sel < 0.5 and 'question_cap_historical' not in settings: settings['question_cap_historical'] = int( settings.get('question_cap', DEFAULT_QUESTION_CAP)) / 2 if hist_sel < 0.999: # NB: Need to add rows for each distinct question type, otherwise won't try and assign them allocsByType['regular'] = [] allocsByType['template'] = [] # Fetch all existing allocations, divide by allocType for (dbAlloc, dbQn) in (Session.query(db.Allocation, db.Question).join( db.Question ).filter(db.Allocation.studentId == self.student.studentId).filter( db.Allocation.active == True).filter( db.Allocation.lectureId == self.dbLec.lectureId).order_by( db.Allocation.allocationId)): if not (dbQn.active) or (dbAlloc.allocationTime < dbQn.lastUpdate): # Question has been removed or is stale dbAlloc.active = False else: # Still around, so save it if (dbAlloc.allocType or dbQn.defAllocType) in allocsByType: # NB: If hist_sel has changed, we might not want some types any more allocsByType[dbAlloc.allocType or dbQn.defAllocType].append( dict(alloc=dbAlloc, question=dbQn)) # Each question type should have at most question_cap questions for (allocType, allocs) in allocsByType.items(): questionCap = int( settings.get( 'question_cap_' + allocType, settings.get('question_cap', DEFAULT_QUESTION_CAP))) # If there's too many allocs, throw some away for i in sorted(random.sample(xrange(len(allocs)), max(len(allocs) - questionCap, 0)), reverse=True): allocs[i]['alloc'].active = False del allocs[i] # If there's questions to spare, and requested to do so, throw away oldest questions if len(allocs) == questionCap and self.reAllocQuestions: for i in reversed( xrange( int(questionCap * float( settings.get('allocation_realloc_perc', 20)) / 100))): allocs[i]['alloc'].active = False del allocs[i] # Assign required questions randomly if len(allocs) < questionCap: query = Session.query(db.Question).filter_by( qnType='tw_questiontemplate' if allocType == 'template' else 'tw_latexquestion').filter_by(active=True) if allocType == 'historical': # Get questions from lectures "before" the current one targetQuestions = (Session.query( db.LectureQuestion.questionId).join(db.Lecture).filter( db.Lecture.plonePath.startswith( re.sub(r'/[^/]+/?$', '/', self.dbLec.plonePath))).filter( db.Lecture.plonePath < self.dbLec.plonePath).subquery()) query = query.filter( db.Question.questionId.in_(targetQuestions)) else: # Git questions from current lecture query = query.filter( db.Question.lectures.contains(self.dbLec)) # Filter out anything already allocated allocIds = [a['alloc'].questionId for a in allocs] if len(allocIds) > 0: query = query.filter(~db.Question.questionId.in_(allocIds)) # Give a target difficulty if self.targetDifficulty is not None: query = query.order_by( func.abs( round(self.targetDifficulty * 50) - func.round((50.0 * db.Question.timesCorrect) / db.Question.timesAnswered))) for dbQn in query.order_by(func.random()).limit( max(questionCap - len(allocs), 0)): dbAlloc = db.Allocation( studentId=self.student.studentId, questionId=dbQn.questionId, lectureId=self.dbLec.lectureId, allocationTime=datetime.datetime.utcnow(), allocType='historical' if allocType == 'historical' else None, ) Session.add(dbAlloc) allocs.append(dict(alloc=dbAlloc, question=dbQn, new=True)) Session.flush() for allocType, allocs in allocsByType.items(): for a in allocs: yield ( self._questionUrl(a['alloc'].publicId), allocType, a['question'], )
s = select(c) r = conn.execute(s) results = r.fetchall() for i in r.keys(): print(i) print('\n') for i in results[0]: print(i) pl(29) # ______________________ # Aggregate functions c = [ func.sum(items.c.quantity), func.round(func.avg(items.c.quantity), 2), func.max(items.c.quantity), func.min(items.c.quantity), func.count(customers.c.id), ] s = select(c) r = conn.execute(s) results = r.fetchall() print(s, '\n') for i in r.keys(): print(i) print('\n') for i in results[0]: print(i)
def func_round(self, property, tree): return sqlfunctions.round(self.get_selectable(property.property, tree))
def getLevel1NodesWithInfo(exactFilterBy={}, likeFilterBy={}, orderField=None, orderDir=None, excludeRoot=False, infoDepth=None, page=None, perPage=None): if infoDepth != 3: raise BadRequest('Requesting level 1 nodes with information to a depth other than 3 not supported yet') if exactFilterBy.get('ownerId', None): ownerQuery = User.query.filter_by(id= exactFilterBy['ownerId']) if ownerQuery.count() == 0: raise NotFound('Invalid ownername specified in main/nodes/info/depth/3 request, so no nodes could be found') owner = ownerQuery.first() rootNode = Node.query.filter_by(parent=None).first() # get all nodes with the given filter all1 = db.session.query(Node)\ .filter_by(**exactFilterBy) for likeKey, likeValue in likeFilterBy.items(): if likeKey == 'name': all1 = all1.filter(Node.name.ilike('%' + likeValue + '%')) if likeKey == 'description': all1 = all1.filter(Node.description.ilike('%' + likeValue + '%')) if likeKey == 'review': all1 = all1.filter(Node.review.ilike('%' + likeValue + '%')) all1 = all1.subquery() exactFilterBy['parentId'] = rootNode.id # get all sub nodes of main nodes under root node sub1 = db.session.query(Node)\ .filter(Node.parentId.in_(db.session.query(Node.id)\ .filter_by(**exactFilterBy))) for likeKey, likeValue in likeFilterBy.items(): if likeKey == 'name': sub1 = all1.filter(Node.name.ilike('%' + likeValue + '%')) if likeKey == 'description': sub1 = all1.filter(Node.description.ilike('%' + likeValue + '%')) if likeKey == 'review': sub1 = all1.filter(Node.review.ilike('%' + likeValue + '%')) sub1 = sub1.subquery() # accumulate all leaf item info and add to each sub item sub2 = db.session.query(sub1)\ .join(all1, sub1.c.id==all1.c.parentId)\ .distinct()\ .add_columns(select([func.avg(Node.rating)]).where(Node.parentId==sub1.c.id).label('averageRating'), select([func.count()]).where(and_(Node.parentId==sub1.c.id, Node.need==True)).label('needChildren'), select([func.count()]).where(and_(Node.parentId==sub1.c.id, Node.haveTried==True)).label('haveTriedChildren'), select([func.count()]).where(Node.parentId==sub1.c.id).label('numberChildren'))\ .subquery() # get all main nodes main1 = db.session.query(Node)\ .filter_by(**exactFilterBy) for likeKey, likeValue in likeFilterBy.items(): if likeKey == 'name': main1 = main1.filter(Node.name.ilike('%' + likeValue + '%')) if likeKey == 'description': main1 = main1.filter(Node.description.ilike('%' + likeValue + '%')) if likeKey == 'review': main1 = main1.filter(Node.review.ilike('%' + likeValue + '%')) if orderField != 'averageLeafRating': if orderDir == 'asc': main1 = main1.order_by(asc(orderField)) if orderDir == 'desc': main1 = main1.order_by(desc(orderField)) main1 = main1.subquery() asub2 = aliased(sub2) asub1=aliased(sub1) main1WithNumSubs = db.session.query(main1)\ .outerjoin(sub1, main1.c.id==sub1.c.parentId)\ .add_columns(select([func.count()]).where(asub1.c.parentId==main1.c.id).label('numberSubs'))\ .distinct()\ .subquery() # outer join sub nodes to main nodes on sub.parentId=main.id rowssq = db.session.query(main1WithNumSubs)\ .outerjoin(sub2, main1WithNumSubs.c.id==sub2.c.parentId)\ .add_columns(select([func.round(func.avg(asub2.c.averageRating))]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('averageLeafRating'), select([func.sum(asub2.c.needChildren)]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('needLeaves'), select([func.sum(asub2.c.haveTriedChildren)]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('haveTriedLeaves'), select([func.sum(asub2.c.numberChildren)]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('numberLeaves'))\ .distinct()\ .subquery() if orderField == 'averageLeafRating': if orderDir == 'asc': rows = db.session.query(rowssq).filter(getattr(rowssq.c, orderField)!=None).order_by(asc(orderField)) if orderDir == 'desc': rows = db.session.query(rowssq).filter(getattr(rowssq.c, orderField)!=None).order_by(desc(orderField)) else: rows = db.session.query(rowssq) if perPage is None: #paginate perPage = rows.count() rowPages = rows.paginate(page=None, per_page=perPage) else: rowPages = rows.paginate(page=page, per_page=perPage) rows = rowPages.items nodes = [] for row in rows: rowDict = row._asdict() node = Node.query.filter(Node.id==rowDict['id']).first() if node.nodeInfo is None: node.nodeInfo = {} nodeInfo = json.loads(node.nodeInfo) try: nodeInfo['averageLeafRating'] = int(rowDict.pop('averageLeafRating')) except TypeError: nodeInfo['averageLeafRating'] = None try: nodeInfo['needLeaves'] = int(rowDict.pop('needLeaves')) except TypeError: nodeInfo['needLeaves'] = 0 try: nodeInfo['haveTriedLeaves'] = int(rowDict.pop('haveTriedLeaves')) except TypeError: nodeInfo['haveTriedLeaves'] = 0 try: nodeInfo['numberLeaves'] = int(rowDict.pop('numberLeaves')) except TypeError: nodeInfo['numberLeaves'] = 0 try: nodeInfo['numberSubs'] = int(rowDict.pop('numberSubs')) except TypeError: nodeInfo['numberSubs'] = 0 node.nodeInfo = nodeInfo nodes.append(node) if(not excludeRoot): nodes.append(rootNode) db.session.commit() nodesJson = {'nodes':[]} nodesJson['nodeCount'] = len(nodes) nodesJson['totalNodes'] = rowPages.total nodesJson['page'] = rowPages.page nodesJson['pages'] = rowPages.pages nodesJson['perPage'] = rowPages.per_page nodesJson['nextPage'] = rowPages.next_num nodesJson['prevPage'] = rowPages.prev_num for node in nodes: node.childCount = len(node.children); nodesJson['nodes'].append(node.buildPublicJson()) return jsonify(nodesJson)