Semi-Comple Entity Query

Semi-Comple Entity Query


Howdy all, I have a little bit of a complicated query across a set of entities that I can't figure out how to get to work in the Agile Platform.

We have three entites, let's call them Foo, Bar, and Baz.  (I've attached an entity diagram to clarify.)

Foo is a parent record, and it can have 0, 1, or 2 associated Bar records.

Bar records have one of two types: Top and Bottom.  There will be at most one Bar record of each type for a given Foo.

Baz records are a type table to define the type of Bar records.

Foo and Baz have no foreign key, but Bar has one each pointing to a specific Foo and a specific Baz.

I want to get a selection that lists each foo, and its associated Bar(s) like so:

Foo, Bar ("Top"), Bar ("Bottom")

However, other valid scenarios are:

Foo, Bar ("Top"), NULL
Foo, NULL, Bar ("Bottom")

I can create a query in SQL that gives me what I want:

    LEFT OUTER JOIN tblBar R1 ON F.nFooID = R1.nFooID AND R1.nBazID = 1
    LEFT OUTER JOIN tblBar R2 ON F.nFooID = R2.nFooID AND R2.nBazID = 2

But I can't seem to get a query working in the platform.

I've attached a sample eSpace with static entities that demonstrated my problem.  Just look at the query "joinQuery" in the preparation of Webscreen1.

Here's the sql example.

And here's the ERD. (BTW, any way to attach multiple files to a post?)
Hi Jefferey,

I think you can only do outer joins in simple queries when the join condition is simple (e.g. Bar.FooID = Foo.Id).
To get what you are looking for you will need an advanced query like this:

(Regarding forum posts you can only attach one file to a post but you can add several images to it.)

Tiago Simões

Thanks, Tiago.

Is there any way to reference named instances of static entities in an advanced query?
I believe you would need to send those in parameters.