We do not have any problem to map case classes.
We usually do not use native sql at all for orm oriented task.
It is very inconvinient to work with query like:
with userRole as (
select u.id as idUser
,u.susername as sUser
,pg.idacrole
from btk_user u
join btk_acusergrant g on u.id = g.iduser
join btk_acprofilegrant pg on g.idacprofile = pg.idacprofile
where coalesce(u.bnotactive,0) = 0 and coalesce(u.bIsTemplate,0) = 0
#$svFlt
)
select tt.idUser
,tt.sUser
,tt.sAcObj
,tt.idAcObj
,tt.sAcObjBundle
,tt.idAcObjBundle
,tt.sAcObjItem
,tt.idAcObjItem
,string_agg(tt.sPrivTypeCode||'.'||tt.sPrivName,chr(10)) as cPriv
from (
select ur.idUser
,ur.sUser
,o.scode as sAcObj
,o.id as idAcObj
,b.scaption as sAcObjBundle
,b.id as idAcObjBundle
,oi.scode as sAcObjItem
,oi.id as idAcObjItem
,pt.sCode as sPrivTypeCode
,ip.ssystemname as sPrivName
,max(r.bhasaccess) as bhasaccess
,coalesce(max(r.bdenied), 0) as bdenied
from userRole ur
join btk_acrolegrantreg r
on r.idacrole = ur.idacrole
join btk_acroleobjectgranttype gt
on r.idacroleobjectgranttype = gt.id
and gt.scode = 'Element'
join BTK_ACItemPrivilege ip
on r.idacitemprivilege = ip.id
and coalesce(ip.bold,0) = 0
join btk_acprivilegetype pt
on ip.idacprivilegetype = pt.id
join btk_acitem i
on ip.idacitem = i.id
and coalesce(i.bold,0) = 0
join btk_acobjectitem oi
on i.id = oi.idacitem
and r.idacobjectitem = oi.id
and coalesce(oi.bold,0) = 0
join btk_acobjectbundle b
on oi.idacobjectbundle = b.id
and coalesce(b.bold,0) = 0
join btk_acobject o
on b.idacobject = o.id
and coalesce(o.bold,0) = 0
group by ur.idUser,
ur.sUser,
o.id,
b.id,
oi.scode,
oi.id,
pt.scode,
ip.ssystemname
) tt
where tt.bdenied = 0
and tt.bhasaccess = 1
group by tt.idUser
,tt.sUser
,tt.sAcObj
,tt.idAcObj
,tt.sAcObjBundle
,tt.idAcObjBundle
,tt.sAcObjItem
,tt.idAcObjItem
It is just inconvenient to create a case class on each query.
