def get_by_id(line_id):
q = """
select
l.*,
jsonb_object_agg(setting, value) as settings
from line_settings as s
join lines as l on s.line_id = l.id
where l.id = %(line_id)s
group by l.id
"""
return Db.execute(q, {"line_id": line_id}, method="fetchone")
def get_by_user_id(user_id):
q = """
select
d.*,
jsonb_object_agg(setting, value) as settings
from device_settings as s
join devices as d on s.device_id = d.id
where d.user_id = %(user_id)s
group by d.id
"""
return Db.execute(q, {"user_id": user_id}, method="fetchall")
def get_by_user_id(user_identity):
q = """
select g.* from groups as g where id in (
select group_id from device_groups where device_id in (
select device_id from device_user where user_id in (
select id from users where name = %(user_identity)s
)
)
)
"""
return Db.execute(q, params={"user_identity": "admin"}, method="fetchall")
def get_by_group_id(group_id):
q = """
select
d.*,
jsonb_object_agg(setting, value) as settings
from device_settings as s
join devices as d on s.device_id = d.id
where s.device_id in (
select device_id from device_groups where group_id = %(group_id)s
)
group by d.id
"""
return Db.execute(q, {"group_id": group_id}, method="fetchall")
def get_by_device_id(device_id):
q = """
select
l.*,
jsonb_object_agg(setting, value) as settings
from line_settings as s
join lines as l on s.line_id = l.id
where l.id in (
select line_id from line_device where device_id = %(device_id)s
)
group by l.id
"""
return Db.execute(q, {"device_id": device_id}, method="fetchall")
def get_next_for_device_id(device_id):
q = """"""
return Db.execute(query=q,
params={"device_id": device_id},
method="fetchone")
def get_by_id(task_id):
q = """"""
return Db.execute(query=q,
params={"task_id": task_id},
method="fetchone")
def get_by_id(group_id, user_identiry):
q = """select * from groups where id = %(group_id)s and user_id = %(user_id)s"""
return Db.execute(q, params={"group_id": group_id, "user_id": user_identiry}, method="fetchone")