def getQuestionsDynamicSql(sort, type):
operatorSql = {0:'''
select a.id,a.title,a.visits,a.quest_time,c.type_name,a.lastest_anwser_time,
count(b.id) as anwser_counts
from questions_question a
left join questions_anwser b on (a.id = b.question_id )
left join questions_question_type c on (a.type_id = c.id)
%(type_filter)s
group by a.id order by a.lastest_anwser_time desc
''',
1:'''
select * from (
select t.*, sum(d.visits) as tag_vistis_counts
from ( select a.id,a.title,a.visits,a.quest_time,c.type_name,a.lastest_anwser_time,
count(b.id) as anwser_counts
from questions_question a
left join questions_anwser b on (a.id = b.question_id )
left join questions_question_type c on (a.type_id = c.id)
%(type_filter)s
group by a.id )t
left join questions_question_tags b on (t.id = b.question_id )
left join questions_question_tag d on (b.question_tag_id = d.id )
group by t.id )s order by s.tag_vistis_counts
''',
2:'''
select a.id,a.title,a.visits,a.quest_time,c.type_name,a.lastest_anwser_time,
count(b.id) as anwser_counts
from questions_question a
left join questions_anwser b on (a.id = b.question_id )
left join questions_question_type c on (a.type_id = c.id)
%(type_filter)s
group by a.id order by a.visits desc
''',
3:'''
select * from (
select a.id,a.title,a.visits,a.quest_time,c.type_name,a.lastest_anwser_time,
count(b.id) as anwser_counts
from questions_question a
left join questions_anwser b on (a.id = b.question_id )
left join questions_question_type c on (a.type_id = c.id)
%(type_filter)s
group by a.id ) t order by t.anwser_counts desc
'''
}
if int(type) > 0 :
type_filter = " where a.type_id =%s " % (type)
else:
type_filter = " "
dynamicSql = operatorSql.get(int(sort)) % {'type_filter' : type_filter}
question_list = DBUtils.executeQuerySql(dynamicSql, []);
return question_list
