How can i do a self-join?

With the RelationPredicateBucked I can only add tables with are releted to each other. When I do a self-job then I do not have any relation, just twice the same table. Then how do I create my query?

I am using the adapter concept with the version 2.0.

Hello,

if you want to create custom relations, why don't you make this with the designer? Here you will be able to add your custom relations if your database doesn't contains one.

That works when I compare values which have to match exactely but then how can I do someting like that?

SELECT tbl1.ID, tbl1.Value FROM Object tbl1, Object tbl2 where lower(tbl1.Value) = lower(tbl2.Value) and tbl1.ID <> tbl2.ID;

here I am using a function so I can not just add custom relations. Is there any way to execute a query like that with LLBL 2.0?

Thanks

Hello,

with a fieldcomparesetpredicate you can compare two fields even if there is no relation between them. You can use operators In,Exists but also equal, greaterthan, not equal .... You can find the complete list in the manual, in the section "Using the generated code->Adapter->Filtering and sorting->The predicate system"

Yes I know I can use unrelated tables with the fieldcomparesetpredicate. But it does not work with fieldcomparesetpredicate when I want to do a self-join. I my case I have the id of a record and I need to find all equal and similar records. Similar mean maybe I need to ignore the case, ignore white space, even just consider a substring of my value field. I could not find any way of doing the query below with LLBL.

SELECT tbl1.ID, tbl1.Value, tbl2.ID, tbl2.Value FROM Object tbl1, Object tbl2 where lower(tbl1.Value) = lower(tbl2.Value) and tbl1.ID <> tbl2.ID and tbl1.ID = 399;

Is there a way I could simply execute the raw SQL statement against the Database? I will want to use the LLBL to open the connection not to build the SQL statement. Is that possible? May be I could do it that way.

SELECT tbl1.ID, tbl1.Value, tbl2.ID, tbl2.Value FROM Object tbl1, Object tbl2 where lower(tbl1.Value) = lower(tbl2.Value) and tbl1.ID <> tbl2.ID and tbl1.ID = 399;

Please try the following code (un-tested):

DataAccessAdapter adapter = new DataAccessAdapter();
IRelationPredicateBucket bucket = new RelationPredicateBucket();

ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(ObjectFields.Id, 0, "Id1", "tbl1");
fields.DefineField(ObjectFields.Value, 1, "Value1", "tbl1");
fields.DefineField(ObjectFields.Id, 2, "Id2", "tbl2");
fields.DefineField(ObjectFields.Value, 3, "Value2", "tbl2");

EntityField2 field1 = new EntityField2();
field1 = ObjectFields.Value;
field1.ObjectAlias = "tbl1";
field1.ExpressionToApply = new DbFunctionCall( "LOWER", new object[] { field1 } );

EntityField2 field2 = new EntityField2();
field2 = ObjectFields.Value;
field2.ObjectAlias = "tbl2"
field2.ExpressionToApply = new DbFunctionCall( "LOWER", new object[] { field2 } );

bucket.Relations.Add(New EntityRelation(field1, field2, RelationType.ManyToMany), JoinHint.Inner);

bucket.PredicateExpression.Add(fields[1] != fields[3]);
bucket.PredicateExpression.Add(fields[1] == 399);

looks like this is the way to do it. but when I tried to fetch the data I got an StackOverFlow exception.

the code below assignes the "Value" field of my "Object" table to the variable field1 and in the last line I am assigning an expression which is depending on my field1 variable again. I guess this seems to be the problem but I could not figure out how to solve it correctly.

EntityField2 field1 = new EntityField2(); field1 = ObjectFields.Value; field1.ObjectAlias = "tbl1"; field1.ExpressionToApply = new DbFunctionCall( "LOWER", new object[] { field1 } );

Don't use the field you add the expression to inside the same expression. so specify a different field INSTANCE inside the constructor call on the dbfunctioncall.