Hi,

are recursive prefetch paths handled by the LLBLGen?

I've got the following situation. Class A which is refferring to itself by some id - typical person - manager problem. Can I retrieve all parents(managers) of my entity using single fetch? I know that I can define many prefetch paths but I don't know how many of them I'll need.

Best regards, MiloszeS

In these cases, you'd better fetch the entire table, and build the tree at client side.

Please check this post: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11983&StartAtMessage=0&#66585

But it would be problematic, because I've got over 20 mln records there .

The mssql and oracle (probably most of databases too) have some mechanisms to find proper records/ids. I'll try to search whether my db offers such functionality and use custom expression, but I'm trying to find out other possibilities. Maybe I don't need to reinvent a wheel.

Regards, MiloszeS

How can I execute a following sql query?

WITH RECURSIVE referenced_event_ids AS
(
SELECT id_event_from
  FROM event.event_links
  where id_event_to = 4794707

UNION ALL

SELECT e.id_event_from
  FROM event.event_links as e, referenced_event_ids re
  where e.id_event_to = re.id_event_from  
)
select * from referenced_event_ids

I need to make an update on entities possesing id's founded by a previous query.

Regards, MiloszeS

You might consider switching from Parent ID to a nested set architecture (LeftID / Right ID) on your table.

This allows you to fetch an entire tree or subtree without recursion.

Hope this helps -

Ryan

Indeed a have such situation - i just simplified a question.

I Have a table events and table links (with from and to ids) which links all events. Maybe it wasn't a good idea to simplify to much.

How can I use it?

Exactly what I use:

http://en.wikipedia.org/wiki/Nested_set_model

Ryan

Also check this thread for additional ideas and further reading storing trees in a db (incl. a reference to CELKO's idea of balanced trees) http://llblgen.com/tinyforum/Messages.aspx?ThreadID=3208

rdhatch wrote:

Exactly what I use:

http://en.wikipedia.org/wiki/Nested_set_model

Very interesting approach. Unfortunately it would be very costly in my case. In addition I cannot modify my client's data model.

Probably I use previously mentioned solution:

WITH RECURSIVE referenced_event_ids AS
(
SELECT id_event_from
FROM event.event_links
where id_event_to = 4794707

UNION ALL

SELECT e.id_event_from
FROM event.event_links as e, referenced_event_ids re
where e.id_event_to = re.id_event_from 
)
select * from referenced_event_ids

It's similar to Otis solution given in May 2005. In this scenario db creates a temporary table transparently and it's quite smooth solution. This is a postrgresql solution but MSSQL has similar syntax.

My first think is to implement a custom Expression which will return ids which will be used in a sub query. Using that approach I can use that predicate to update something directly in the DB or fetch EntityCollection.

Do I need to create a custom Expression to accomplish my goal or is it possible to create such query without implement addictional classes (expression)? If yes which already existing predicate expresion you are recommend to inherit?

Best Regards, MiloszeS

A) If you want to implement an expression that achieve the expression maybe the best predicate to inherit would be FieldCompareValuePredicate

B) You also could write an StoredProcedure that executes the query that returns the IDs so you can use such IDs later on you predicate to fetch the entity collection.

C) You also could write an StoredProcedure that returns all the needed records (your first query above) and then you can project such SP result onto an entity collection. (more info...)

How can I achieve a following SQL Query ?

UPDATE "event"."events" SET "id_main_content"=((837742)::int8) WHERE ( ( ( "event"."events"."id" IN (
                WITH RECURSIVE referenced_event_ids AS
                (
                SELECT id_event_from
                FROM event.event_links
                where id_event_to = ((4615))

                UNION ALL

                SELECT e.id_event_from
                FROM event.event_links as e, referenced_event_ids re
                where e.id_event_to = re.id_event_from 
                )
                select * from referenced_event_ids
                union ALL 
                select ((4615)) 
                
            ))))

I've created a custom Expression which generates a following part:

WITH RECURSIVE referenced_event_ids AS
                (
                SELECT id_event_from
                FROM event.event_links
                where id_event_to = ((4615))

                UNION ALL

                SELECT e.id_event_from
                FROM event.event_links as e, referenced_event_ids re
                where e.id_event_to = re.id_event_from 
                )
                select * from referenced_event_ids
                union ALL 
                select ((4615)) 

But I don't know how can I use it in predicate Expression. I need to use _in _clause and used a following predicate:

IRelationPredicateBucket filterBucket = new RelationPredicateBucket
                (
                    new FieldCompareSetPredicate(EventFields.ID, null, EventFields.ID, null, SetOperator.In, new PostgreSQLFindAllParentsExpression(EventLinkFields.IdEventFrom, EventLinkFields.IdEventTo, idEvent))
                );

which gives me a following query.

UPDATE "event"."events" SET "id_main_content"=((837742)::int8) WHERE ( ( ( "event"."events"."id" IN (SELECT "event"."events"."id" AS "ID" FROM "event"."events" WHERE
                WITH RECURSIVE referenced_event_ids AS
                (
                SELECT id_event_from
                FROM event.event_links
                where id_event_to = ((4615))

                UNION ALL

                SELECT e.id_event_from
                FROM event.event_links as e, referenced_event_ids re
                where e.id_event_to = re.id_event_from 
                )
                select * from referenced_event_ids
                union ALL 
                select ((4615)) 
            ))))

Everything will be fine except that I receive unnecessarily SELECT "event"."events"."id" AS "ID" FROM "event"."events" WHERE part of code.

How can I define in predicate in my case ?

Kind Regards, MiloszeS

Hi MiloszeS,

Please post your custom predicate, so we can reproduce your problem. If you could post a tiny repro solution that uses your predicate, that would be helpful as well.

Custom Predicate:

[Serializable]  
public class PostgreSQLFindAllParentsExpression : FieldCompareSetPredicate
    {
        Int64 idEventChild;

        public PostgreSQLFindAllParentsExpression(IEntityFieldCore linkFieldFrom, IEntityFieldCore linkFieldTo, Int64 idEventChild)
            : base(linkFieldFrom, null, linkFieldTo, null, SetOperator.Equal, null)
        {
            this.idEventChild = idEventChild;
        }

        public override string ToQueryText()
        {
            return this.ToQueryText(false);
        }

        public override string ToQueryText(bool inHavingClause)
        {
            DbParameter parameter = base.DatabaseSpecificCreator.CreateParameter(System.Data.ParameterDirection.Input, this.idEventChild);

            base.Parameters.Add(parameter);

            return string.Format(@"
                WITH RECURSIVE referenced_event_ids AS
                (
                SELECT {1}
                FROM {3}.{4}
                where {2} = {0}

                UNION ALL

                SELECT e.{1}
                FROM {3}.{4} as e, referenced_event_ids re
                where e.{2} = re.{1} 
                )
                select * from referenced_event_ids
                union ALL 
                select {0} 
            "
             , parameter.ParameterName // id child event
             , this.PersistenceInfoField.SourceColumnName // id link from
             , this.PersistenceInfoSetField.SourceColumnName // id link to
             , this.PersistenceInfoField.SourceSchemaName // schema name
             , this.PersistenceInfoField.SourceObjectName); // table name
        }

I'm using it in a following way:

IRelationPredicateBucket filterBucket = new RelationPredicateBucket
                (
                    new FieldCompareSetPredicate(EventFields.ID, null, EventFields.ID, null, SetOperator.In, new PostgreSQLFindAllParentsExpression(EventLinkFields.IdEventFrom, EventLinkFields.IdEventTo, idEvent))
                );


            EventEntity entityToSet = new EventEntity();
            entityToSet.IdMainContent = idNewContent;

            using (var adapter = CreateAdapter())
            {
                adapter.UpdateEntitiesDirectly(entityToSet, filterBucket);
            }

For sure I'm doing something wrong - I don't know how can I use IN clausule in my case.

I'm not sure this is going to work as you want. Thus I recommend using David's third approach.

C) You also could write an StoredProcedure that returns all the needed records (your first query above) and then you can project such SP result onto an entity collection.

IMHO, this is the most suitable path you can take.

I prefer to use an Expression, because the mentioned code will be used in many scenarious. Ex. update many rows, select linked entities, etc. The sql query works as I want and fast enough. I would like to use it as an expression because of scalability.

Maybe I Should inherit from FieldCompareSetPredicate and create a custom IN predicate? I prefer not create to many custom Expressions - maybe I can model it using build in predicates?

FieldCompareSetPredicate is used to produce a fragment like: fieldA <operator> (SELECT fieldB FROM ... )

So the unnecessary select where comes from the usage of FieldCompareSetPredicate.

To me, it seems best if you create your own predicate class which emits what you want, so if you want to emit your own fragment which starts with

fieldA IN ( WITH RECURSIVE referenced_event_ids AS ....

you can better use your own custom predicate class. This is really easy, just check how the predicate classes are done in the runtime sourcecode to get an idea. You don't have to implement interpret etc., just implement ToQueryText. It's important you pass an unused integer number for the PredicateType value, e.g. 1000

important As you use adapter, you have to do something else as well: be sure persistenceinfo is injected properly for the fields in your predicate class. So if you have, say, 4 fields in your predicate class, you have to make sure for all 4 fields persistenceinfo is injected. This is done by creating a partial class of DataAccessAdapter and override OnInsertPersistenceObjects(predicate)

in that override, you test if the predicate variable passed in is of the type of your custom predicate class. If so, you insert the persistenceinfo's. This is easy, I've given an example below for a 4 field custom predicate class

protected override void OnInsertPersistenceObjects(IPredicate currentPredicate)
{
    if(currentPredicate.InstanceType==1000) // cast to your own enum type here for better readable code
    {
        PostgreSQLFindAllParentsExpression predicate = (PostgreSQLFindAllParentsExpression)currentPredicate;
        if(predicate.Field1!=null)
        {
            predicate.Field1PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)predicate.Field1);
        }
        if(predicate.Field2!=null)
        {
            predicate.Field2PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)predicate.Field2);
        }
        if(predicate.Field3!=null)
        {
            predicate.Field3PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)predicate.Field3);
        }
        if(predicate.Field4!=null)
        {
            predicate.Field4PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)predicate.Field4);
        }
    }
}

That should be it.