SELECT TableA.* FROM MyTable TableA, MyTable TableB WHERE TableA.L < TableB.R AND TableB.Field = 5

Basically, how do I do a select from one table but using two instances of the table type?

mikeg22 wrote:

SELECT TableA.* FROM MyTable TableA, MyTable TableB WHERE TableA.L < TableB.R AND TableB.Field = 5

Basically, how do I do a select from one table but using two instances of the table type?

Your query is the same as:

SELECT  MyTable.*
FROM    MyTable
WHERE   L < ALL 
(
    SELECT  R
    FROM    MyTable M
    WHERE   MyTable.ID = M.ID
            AND Field = 5
)

which you can solve with a FieldCompareSetPredicate, specifying an objectalias for the setfield and an extra filter on the PK field(s) so the subquery is co-related.

If I read your query correctly (which results in a crossjoin, not what you want) is that you want all TableA's if L< R if field = 5 or field != 5 ? (so only apply the filter L<R when field is 5)

So isn't it so that your query is the same as:

SELECT MyTable.*
FROM MyTable 
WHERE (L<R
AND Field=5)
OR
field!=5

? Which you can create without relations and just with a predicate expression which contains a predicate expression with teh L<R AND field=5 predicates, and OR-ed to that a predicate which does a comparevalue on 5 but negated.

Yes, the FieldCompareSetPredicate is the way we are dealing with it now, but I was hoping there was a way to set it up so it was actually selecting from two aliased tables (we are translating some stored procedure code which does it this way.)

If the tables are two different ones, you can just specify the aliases when you add the relation to the relationcollection with an overload of relationcollection.Add()

If they are the same table, a FieldCompareSet predicate is sufficient. However, with your query, you could do it without a fieldcompareset predicate, as I suggested, did that work? If you have problems formulating the FieldCompareSetPredicate statement, let me know.

Its actually a bit more complicated than I first described.

We have a table called TableA. Table A has fields: TableA.TableA_ID (PK) TableA.TableB_ID (FK to an employee table) TableA.LeftTableA_ID TableA.RightTableA_ID

TableB represents a list of employees, and TableA defines their position in the company. The rule is for a given employee TableB_ID, there is a set of other TableB_IDs that are beneith this person in the company. This is what we are trying to find here. Here's an example row (that represents an employee) to explain the rule that defines the heirarchy in TableA.

TableA_ID = 1 TableB_ID = 1 TableA.LeftTableA_ID = 1 TableA.RightTableA_ID = 3

All employees under this employee would have LeftTableA_ID values between 1 and 3.

This is what we are trying to model with the predicates/relations.

mikeg22 wrote:

Its actually a bit more complicated than I first described.

We have a table called TableA. Table A has fields: TableA.TableA_ID (PK) TableA.TableB_ID (FK to an employee table) TableA.LeftTableA_ID TableA.RightTableA_ID

TableB represents a list of employees, and TableA defines their position in the company. The rule is for a given employee TableB_ID, there is a set of other TableB_IDs that are beneith this person in the company. This is what we are trying to find here. Here's an example row (that represents an employee) to explain the rule that defines the heirarchy in TableA.

TableA_ID = 1 TableB_ID = 1 TableA.LeftTableA_ID = 1 TableA.RightTableA_ID = 3 All employees under this employee would have LeftTableA_ID values between 1 and 3.

Ah weighted tree model ala Celko (IIRC)

I guess you want for a given tableb_ID all employees beneath this person in the hierarchy? I think the most efficient approach is a 2-fetch approach. This means: first fetch the TableA entity for that given tableb_ID, and then formulate a query like:

SELECT  Employees.*
FROM    Employees
WHERE   Employees.ID IN
(
    SELECT  TableB_ID
    FROM    TableA
    WHERE   LeftTableA_ID BETWEEN leftValue AND rightValue
)

(I used 'leftValue and 'rightValue' as placeholders for variables, I don't know which db flavor you're using)

// first fetch TableA record for manager with employeeid located in _id
TableAEntity managerLocation = new TableAEntity();
managerLocation.TableB_ID = _id;
// I assume there is a unique constraint on TableB_ID, as an employee
// is located in the tree just once
adapter.FetchEntityUsingUniqueConstraint(managerLocation.ConstructFilterForTableB_ID());

// then formulate the query for our employees fetch
EntityCollection managedEmployees = new EntityCollection(new EmployeeEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
IPredicateExpression subQueryFilter = new PredicateExpression();
subQueryFilter.Add(PredicateFactory.Between(
    TableAFieldIndex.LeftTableA_ID, managerLocation.LeftTableA_ID, managerLocation.RightTableA_ID));
// now formulate the subquery. We specify the compare field Employee.ID and the setfield
// TableA.TableB_ID, and the filter we just setup: 
// LeftTableA_ID BETWEEN leftValue AND rightValue, with the operator 'IN'.
filter.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(EmployeeFieldIndex.ID), null,
    EntityFieldFactory.Create(TableAFieldIndex.TableB_ID), null,
    SetOperator.In, subQueryFilter));
    
// and fetch the employees
adapter.FetchEntityCollection(managedEmployees, filter);

Otis wrote:

mikeg22 wrote:

Its actually a bit more complicated than I first described.

We have a table called TableA. Table A has fields: TableA.TableA_ID (PK) TableA.TableB_ID (FK to an employee table) TableA.LeftTableA_ID TableA.RightTableA_ID

TableB represents a list of employees, and TableA defines their position in the company. The rule is for a given employee TableB_ID, there is a set of other TableB_IDs that are beneith this person in the company. This is what we are trying to find here. Here's an example row (that represents an employee) to explain the rule that defines the heirarchy in TableA.

TableA_ID = 1 TableB_ID = 1 TableA.LeftTableA_ID = 1 TableA.RightTableA_ID = 3 All employees under this employee would have LeftTableA_ID values between 1 and 3.

Ah weighted tree model ala Celko (IIRC)

I guess you want for a given tableb_ID all employees beneath this person in the hierarchy? I think the most efficient approach is a 2-fetch approach. This means: first fetch the TableA entity for that given tableb_ID, and then formulate a query like:

SELECT  Employees.*
FROM    Employees
WHERE   Employees.ID IN
(
    SELECT  TableB_ID
    FROM    TableA
    WHERE   LeftTableA_ID BETWEEN leftValue AND rightValue
)

(I used 'leftValue and 'rightValue' as placeholders for variables, I don't know which db flavor you're using)

// first fetch TableA record for manager with employeeid located in _id
TableAEntity managerLocation = new TableAEntity();
managerLocation.TableB_ID = _id;
// I assume there is a unique constraint on TableB_ID, as an employee
// is located in the tree just once
adapter.FetchEntityUsingUniqueConstraint(managerLocation.ConstructFilterForTableB_ID());

// then formulate the query for our employees fetch
EntityCollection managedEmployees = new EntityCollection(new EmployeeEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
IPredicateExpression subQueryFilter = new PredicateExpression();
subQueryFilter.Add(PredicateFactory.Between(
    TableAFieldIndex.LeftTableA_ID, managerLocation.LeftTableA_ID, managerLocation.RightTableA_ID));
// now formulate the subquery. We specify the compare field Employee.ID and the setfield
// TableA.TableB_ID, and the filter we just setup: 
// LeftTableA_ID BETWEEN leftValue AND rightValue, with the operator 'IN'.
filter.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(EmployeeFieldIndex.ID), null,
    EntityFieldFactory.Create(TableAFieldIndex.TableB_ID), null,
    SetOperator.In, subQueryFilter));
    
// and fetch the employees
adapter.FetchEntityCollection(managedEmployees, filter);

Thanks! That works