Dealing with non-object database structure

[Tomasz M.Lipiński]

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

[Thomas]

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.

[Tomasz M.Lipiński]

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...

[Thomas]

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.

[Tomasz M.Lipiński]

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

[Tomasz M.Lipiński]

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

[Thomas]

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.

[Tomasz M.Lipiński]

Hi,

Thank's for explanations.

Regards
Tomasz