[odb-users] Emulating a sql union in ODB

[David Stocking]

I am working on an application where I basically need to do a union but ill explain the data first.

The classes I’m working with are

#pragma db object polymorphic
class RosterEntry
{
….
}

#pragma db object
class Contact : public RosterEntry
{
….
}

#pragma db object
class Group : public RosterEntry
{
….
}

Basically my old native query goes like this.

SELECT Contact.name as name
        …. all the columns here
FROM Contact
WHERE
    some conditions here
UNION
SELECT Group.name as name
        …. all the columns here
FROM ( SELECT Group.*
              — Get a count of the number of members in this group
              (SELECT COUNT(*) FROM ContactGroup AS cg LEFT JOIN Contact ON cg.contact_id = Contact.id WHERE cg.group_id = Group.id) AS members
       FROM Group
       WHERE
           some other conditions here )
ORDER BY name ASC

I thought I could pull this off with views and polymorphic types but I haven’t been able to figure out how I could. Is this possible? Should I even bother trying to get this query in a non native form or should i just make this huge query of doom a native sql query view?

Any enlightenment on the subject would be greatly appreciated.

David Stocking
Software Engineer, Windows Desktop
Extension Healthcare
General:  877-207-3753

Helping hospitals achieve compliance with TJC NPSG on clinical alarm safety.

Ask us about data-driven change with Extension Evaluate™<http://bit.ly/1ooTuOz>



This communication is confidential, intended only for the named recipient(s) above and may contain trade secrets or other information that is exempt from disclosure under applicable law. Any use, dissemination, distribution or copy of this communication by anyone other than the named recipient(s) is strictly prohibited. If you have received this communication in error, please immediately notify us by calling (877-207-3753).