Dealing with non-object database structure

8 posts, 1 answers
  1. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 15 Apr 2010 Link to this post

    Hi,
    I'm not sure whether the title of this topic is clear - it's better to give an example of my problem:

    There are tables:
    • TableA, with an ID column and some other stuff (and similar TableB, TableC etc. tables)
    • TableZ, which is more interesting; it consists of columns:
      • ID
      • TargetName (with values as "TableA", "TableB" and so on)
      • TargetID
      • and again - some more stuff

    TargetName says what table is pointed by TargetID. When TargetName = "TableA" then TargetID contains a value of TableA.ID; when "TableB" - a value of TableB.ID - and so on.
    And now - I would like to query TableA for data that are referenced by TableZ only. Using the SQL syntax it's easy:

    select TableA.* from TableA inner join TableZ on TableA.ID = TableZ.TargetID where TableZ.TargetName = 'TableA' 
    How to deal with it using OQL? I can design classes inheriting from TableZ (for each value of TargetName) but all of them should share the same TargetID column presenting it as a reference to objects of different types. Is it possible? If "yes" there would be a collection of TableZ inheritors in the TabelA class and the query would test its emptiness (BTW: is it possible to test emptiness of a collection? and more: how to test its cardinality?)

    Regards
    Tomasz
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Apr 2010 Link to this post

    Hello Tomasz,

    the TableZ could become the root of a table hierarchy, effectively creating a class hierarchy :

    class Z { int ID; ... }
    class A : Z { ... }
    class B : Z { ... }
    class C : Z { ... }

    This would then require, that the class discriminator is set to 'TableA' for class A, 'TableB' for class B etc.
    Then normal operations fetching A instances (OQL: select * from AExtent) will perform an inner join between TableA and TableZ and use the 'TableA' discriminator value (need to map the discriminator to the TargetName column) to filter on the correct instances. For this to work you will need to assure that all ID columns are defined in the same way. 

    All the best,
    Thomas
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  3. DevCraft banner
  4. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 15 Apr 2010 Link to this post

    Hi,

    I haven't explicitly stated that there can be multiple instances of TableZ for one instance of TableA. Therefore making TableZ the top of the hierarchy with TableA as its inheritor doesn't solve the problem.
    The other problem is that there can be another TableZ's  - another tables being related with TableA and company in the similar way.

    Regards
    Tomasz

    PS. What about testing emptiness and cardinality of collections? I know that it's a little bit off-topic but if you could answer this question...
  5. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 15 Apr 2010 Link to this post

    Hello Tomasz,

    then there will be no way other than using manual joins and manual filters.

    As for your OQL question: 
    To test if a collection contains at least one element you can use
    SELECT * FROM PersonExtent AS p, p.Addresses AS a WHERE a != null
    To test if a collection contains no element you can use
    SELECT * FROM PersonExtent AS p WHERE COUNT(p.Addresses) == 0

    Kind regards,
    Thomas
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  6. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 15 Apr 2010 Link to this post

    Hi,

    OK. I've expected that there is no rescue for me :-)
    One little correction: in the first example it should be "nil" instead of "null"; ... WHERE a != nil

    Thanks and regards
    Tomasz
  7. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 15 Apr 2010 Link to this post

    Hi,

    And one more thing. The query:
    ... WHERE a != nil
    works well. But the complementary one:
    ... WHERE a = nil
    always gives zero. Fortunately I can use the second test mode:
    ... COUNT(.Addresses) = 0
    and its complement:
    ... COUNT(.Addresses) != 0
    But just to know: why "a = nil" doesn't work for collections (when applied to a simple value column it works as expected)?

    Regards
    Tomasz

  8. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 16 Apr 2010 Link to this post

    Hello Tomasz,

    that is because when you use the 'SELECT * FROM PersonExtent AS p, p.Addresses AS a WHERE a != nil' form, the a is actually a result of a join. And therefore the NULL value is not calculated, and a != nil will always be true : a IS NOT NULL.
    However, if you compare with = nil, there will be no join result at all...
    It is SQL semantics, after all.  

    Best wishes,
    Thomas
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  9. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 16 Apr 2010 Link to this post

    Hi,

    Thank's for explanations.

    Regards
    Tomasz
Back to Top
DevCraft banner