Query Text tables

Query Text tables

  
ive got 2 tables, Entity and Entity_Text (containing Entity.Id and Locale.Id) with the according text translations for Entity

i can perform a simple query now for specific languages that exist in the text table
but if i query a language that doesnt exist, i dont get any records (where search parameter or left/right-join dont seem to help)

how can i still get a record list for the Entity records, without or with the text in a default language?
Hi Enigma,

In the Conditon string you have to put this: "Entity with or without Entity_Text"
It must operate, this is a simple left join.
If you have problems try this in an Advanced Query:
SELECT *
FROM {ENTITY}
LEFT OUTER JOIN {ENTITY_TEXT}     ON ({ENTITY_TEXT}.[Entity.Id] = {ENTITY}.[Id])

Best Regards,
Diogo Miguel
hi diogo

i tried entity with or without entity_text, but it only works without the where entity_text.localeid = locale

i couldnt get the LEFT OUTER JOIN working either, the only result i every got the query going with,
was a list of entity_text.id's but all other fields are empty
thats without entity as output structure, if i add entity as an output structure, the query fails assigning a decimal to datetime
Enigma,

"i tried entity with or without entity_text, but it only works without the where entity_text.localeid = locale"

If you pass a localeid value but dont have that value on entity_text.localeid is normal then you are not able to have the values you expect.
If you are passing null you have to put this in your where clause:
locale = NullIdentifier() or (entity_text.localeid = locale)

If you want you can put your oml to i see what is happening.

Best Regards,
Diogo Miguel
hi diogo

yes its passing a value, the user can basically select a language (ie. english) and create different subjects
which creates an entity record plus an entity_text record in the according language.

if the user then changes the language (ie. to spanish) via dropdown, the subjects are queried in spanish.
no matter if the according translations exist or not.

the thing is, the user should be able to translate the subjects in entity, without having to create new subjects - since it would be 2 different subjects

so u mean it would require doing the query 2ce?
1ce to check if ie. spanish exists
and if it doesnt, 2ce with locale = null

sending the oml would be a little difficult, sicne it is linked with other oml's etc
Can you create an example oml with you want?

With the same tables you are using, because i don't understand why didn't work for you.

Best Regards,
Diogo Miguel
here you go =)
my test was:
- entity 1, label test 1
- entity 2, label test 2
- save in english
- change to spanish
and the reocrds dont show
Hi Enigma,

I found it, the problem is the Where clause: entity_text.localeid = locale.
You saved in English and when you change to spanish you will try find something which not exists, thats why the query is empty.

To this situation you could try have two queries, the first one is only for the Entity records and the second one is for the Entity_Text and in this you passe the Entity.Id and the Locale.Id.
This way you always have the Entity records.

Best Regards,
Diogo Miguel
hi miguel

but that would also mean that i have no possibilities to sort or filter by the text?
which is quite relevant when it comes to product data and similar

and i would have to query entity_text once for every record in entity?

what is the parameter "entity with or without entity_text" good for then?

thank you
Hi  Enigma,

You can do that with an Entity Structure, but unfortunately i dont have time to tell you how you can do it.
I will try to do it in your oml test.

Best Regards,
Diogo Miguel
please do, that would be a huge help
thank you very much!
Hi,

I only could did this. I dont know if its all correct because, and i dont know why, i couldn't open the oml in browser.

Try it and put it in your way.

Best Regards,
Diogo Miguel
hi miguel, thank you very much
i think i got the idea

i took the liberty to make it a little cleaner, including the possibility to sort/order the columns of both entities
maybe you can give it a look when you have time, if you might find any possibilities for improvements

thanks again =)
Glad i could help you :)

I think you will go on right way and don't need changes.

Best Regards,
Diogo Miguel