def skills_search(name):
user_id = flask_session.get('user').get('id')
skills = db.session.query(Skill) \
.filter(Skill.name.like('%' + name + '%')) \
.filter(~sqlalchemy_sql.exists([sqlalchemy_text('skill_id')])
.where(sqlalchemy_sql
.and_(UserSkill.user_id == user_id,
UserSkill.skill_id == Skill.id)))
response = make_response(
json.dumps(list(map((lambda x: x.serialize), skills.all()))), 200)
response.headers['Content-Type'] = 'application/json'
return response
def get_endorse_counts(user_id):
endorse_counts = db.session.query(UserSkill.user_id, UserSkill.skill_id,
sqlalchemy_func.count(
Endorse.endorser_id).label(
'endorse_count')) \
.filter(UserSkill.user_id == user_id) \
.outerjoin(Endorse,
sqlalchemy_sql.and_(
Endorse.user_id == UserSkill.user_id,
Endorse.skill_id == UserSkill.skill_id)) \
.group_by(UserSkill.user_id, UserSkill.skill_id) \
.order_by(sqlalchemy_text('endorse_count DESC')) \
.all()
return endorse_counts
def execute_anything(query):
result = None
try:
engine = create_engine(settings.PANGEA_DB_URI)
except:
return
try:
with engine.connect().execution_options(autocommit=True) as conn:
result = conn.execute(sqlalchemy_text(query))
if result.cursor:
result = result.cursor.fetchall()
else:
result = None
except Exception as e:
raise (e)
finally:
conn.close()
return result
def get_anything(query):
result = conn.execute(sqlalchemy_text(query))
result = result.cursor.fetchall()
if len(result) > 0:
return result
return None
_RETRAIN_MODELS_SQL = sqlalchemy_text('''
WITH modles_by_user_tagset_source_trained AS (
SELECT model.id, model.created_by_user_id, model.tagset_id, jsonb_agg(DISTINCT src_mdl.source_id ORDER BY src_mdl.source_id) AS sources, model.trained_ts
FROM activity.model AS model
JOIN activity.source_model AS src_mdl ON model.id = src_mdl.model_id
GROUP BY model.id, model.created_by_user_id, model.id, model.trained_ts
),
current_models AS (
SELECT created_by_user_id, tagset_id, sources, max(trained_ts) AS trained_ts
FROM modles_by_user_tagset_source_trained
GROUP BY created_by_user_id, tagset_id, sources),
relevant_data AS (
SELECT model.id AS model_id, dat.id AS data_id, dat.crawled_ts AS crawled_ts, model.trained_ts AS trained_ts
FROM activity.data AS dat
JOIN activity.language AS lang ON lang.data_id = dat.id
JOIN activity.text AS text ON text.data_id = dat.id
LEFT OUTER JOIN activity.translation AS trans ON trans.text_id = text.id
JOIN activity.source_model AS sm ON sm.source_id = dat.source_id
JOIN activity.model AS model ON model.id = sm.model_id
WHERE (lang.language = 'en' OR trans.target_language = 'en')
),
outdated_models AS (
SELECT relevant_data.model_id AS id
FROM relevant_data
WHERE relevant_data.crawled_ts > relevant_data.trained_ts
GROUP BY relevant_data.model_id
HAVING count(*) > 50
UNION
SELECT relevant_data.model_id AS id
FROM relevant_data
JOIN activity.tagging AS tagging ON tagging.data_id = relevant_data.data_id
WHERE tagging.tagging_ts > relevant_data.trained_ts
GROUP BY relevant_data.model_id
HAVING count(*) > 50 OR min(tagging.tagging_ts) < (now() - INTERVAL '4 hours')
)
SELECT created_by_user_id, tagset_id, sources, trained_ts
FROM modles_by_user_tagset_source_trained AS models
JOIN outdated_models AS outdated ON models.id = outdated.id
INTERSECT
SELECT * FROM current_models''')