Hi all:

I am still relatively new to llblgen, so please bear with me.

We are running a bilingual site which means that a lot of our data needs to be different language (english and french) for now. Therefore, the db is set up to have a generic entity table and a translation table for the generic entity. For example, we have REGION and REGION_TL, where TL stands for translation. Now, I am trying to have a write code in llblgen that does the following query:

SELECT  RT.REGION_ID
,RT.NAME
,RT.DESCRIPTION
,R.CREATE_DATE
,R.CREATE_USER
,R.MODIFY_DATE
,R.MODIFY_USER 
FROM REGION R, REGION_TL RT 
WHERE R.REGION_ID = RT.REGION_ID
AND RT.LANG = '{0}'  
AND R.ACTIVE_YN = 'Y'

Now, I can't map this query into any entity object, at least I don't know how. My other option is to create typedList or create dynamicList on the fly. However, this means that I'd have to create "A LOT" of typedList since a lot of our data has translation.

My question is: Is TypeList the best practice? Has anyone else have a more elegant solution?

Thanks!

Hi there,

Some questions about your scenario:

• That REGION+REGION_TL object that you fetch, Do you need it to be updatable, or read-only is ok?
• How do you obtain the LANG parameter of the system (session, some helper object, user info)?
• Using SelfServicing or Adapter?
• Just to confirm, I suppose the structure is similar to:

REGION REGION_ID (PK) CREATE_DATE ...

REGION_TL REGION_ID(PK) (FK to REGION.REGION_ID) LANG(PK) DESCRIPTION ...

and that approach is similar to a bunch of tables in your schema, right?

• That REGION+REGION_TL object that you fetch, Do you need it to be updatable, or read-only is ok?

It needs to be updatable, not for this specific case, but we do have other table with similar structure that needs to be updatable

• How do you obtain the LANG parameter of the system (session, some helper object, user info)?

some helper object

• Using SelfServicing or Adapter?

adapter

Just to confirm, I suppose the structure is similar to:

REGION REGION_ID (PK) CREATE_DATE ...

REGION_TL REGION_ID(PK) (FK to REGION.REGION_ID) LANG(PK) DESCRIPTION ...

and that approach is similar to a bunch of tables in your schema, right?

Yes, that's correct

BTW, I found out that I can do a fetch on region_tl and then do a prefetch on region to get the result that I want. But that would result in 2 query instead of one, do you have a better approach?

Thanks

REGION REGION_ID (PK) CREATE_DATE ... REGION_TL REGION_ID(PK) (FK to REGION.REGION_ID) LANG(PK) DESCRIPTION

This qualifies the relation to be an Inheritance relation, so you can have REGION as the super-entity, and REGION_TL as a sub-entity.

This would fetch fields from both tables in one query.

Thanks for the reply, however I can't seem to get inheritance relation working.

In this case, I think I fall into a TargetPerEntity case. However, when I try to do this in the designer by choosing the "Create Sub-Type on this Entity" entry in the context menu of the Region Entity, it is asking for the disriminator value, which I don't have and the designer won't let me go on without entering a value.

Ineritance will solve most of my problem, any help is appreciated.

Thanks!

Go to the sub-Type entity and right click, then select "Make sub type of..."

Ok, so I went into RegionTL, the subtype in this case, right clicked on "Create Sub type of..." and it is asking for a discriminator value which I don't have.

I also read somewhere the forum that primary key within a hieracrhy be (1:1), however, the relationship between REGION AND REGION_TL is (1:M).

REGION REGION_ID (PK) CREATE_DATE ...

REGION_TL REGION_ID(PK) (FK to REGION.REGION_ID)

So the above sketch is not true, is it?

(Edit) This leaves you with your current approach.

BTW, I found out that I can do a fetch on region_tl and then do a prefetch on region to get the result that I want. But that would result in 2 query instead of one, do you have a better approach?

REGION_TL has 2 PK, one is REGION_ID and the other is LANG.

Can inheritance be a solution in this situation then?

Please let me know since this is one of a major point if we are going to go with llblgen pro or not.

any help is appreciated.

Thanks.

Inheritance can't be achieved in your schema as the REGIN-REGION_TL is not a 1:1 relation. And you can't use TypedList / TypedView / DynamicList as those are for read-only access. That leave us with the entity + prefetch approach. You can add "fields on related fields" on LLBLGen Designer to facilitate this. And it's true, two queries to fetch it, but that is not that bad. As a matter of fact having all your schema in a inheritance scenario may have a significant impact in performance.

hm.... thank you very much. I am surprised that no one has had similar problem in llblgen. I mean, how do you have translated information in a database then?