> > Im trying to make a "data" system that is language-independent, ie the
data
> > stored can have any language therefore the same information is stored
> > several times but with different values (depending on language)
>
> =Is this correct?
> "data" ~ news reports/magazine articles
> "article" ~ a news report (cf an item or product)
> "language" = spoken language (cf computer probgramming)
> "different values (depending on language)" = translations
>
> =Is "name" that of the author or the title of the article?
> =Is "id" a UNIQUE column, or if there are both English and French versions
of the same article do they have the
> same id? Please describe "id".
ID is a unique column (primary key on the table could be (id,language)
the same Article in different languages have the same id but different
language codes.
> > lets say that I have this table.. articles(id,language,name,description)
> > what I would like to do is retrieve 1 row per unique id in the chosen
> > language
> > select * from articles where language='en';
>
> = this will select ALL of the articles written in English
> - if there were two such articles with the same 'id', and in English, then
they would both appear
> - unless all articles (id-s) have an English language translation, then
this will not produce a list of all
> unique ids amongst the 'hits' (I assume this is not possible - right?)
>
> =do you want ALL articles, or only the one with a matching id?
In some cases all, in some cases only one, in other cases some of them...
You should know that the table I'm talking about is not the REAL table, but
only something to talk about
posting the real table-information would be breach of contract.. so the
table I described is an example the
real tables (there are many) have more keys, etc...
> > that is quite simple, unfourtunally, not all articles have the
description
> > or name written in english and the above query would not return these
> > articles... PROBLEM is what I get.
> > however doing a
> > select * from articles group by (id);
> > will return all articles even if they do not have an english
translation,
>
> =assuming that all the translations of the same article have the same id!
The following will achieve a similar
> effect:
>
> SELECT * FROM articles ORDER BY id [ASC];
Yes, but I would still see ALL the different translations, and if 1 article
have 7 translations, it would be found
by the application 7times (where only 1 of them will be presented, which is
a waste of network/processing time)
> > IF I however could somehow order the results before the group by was
done, I
>
> =or question the need to group the data - this clause is useful when you
want to further analyse the data in
> those 'groups', but may not otherwise be useful. You do not appear to be
using SQL to analyse the groups, you
> appear to only want the data collected/sequenced.
that is correct, I want to collect the darta to the client and present them
to the user..
> > could sort it so that first there would be english, then swedish,
norwegian,
> > etc... and at the bottom French ( :-) ) and then do the group by and
the
> > first language in this list would be the one I would get, English, and
if
> > there is no English I would get Swedish, and if there is no swedish, I
would
> > getnorwegian, and so on..
>
> =so let's add language into the sequencing instruction:
>
> SELECT * FROM articles ORDER BY id, language;
>
> =now this does NOT answer your question because an article with a full set
of translations will appear in the
> sequence dv, en, fr, no, se.
And that is unfourtunally what I get today...(which I then have to
programaticly have to discard several rows)
> =there are ways for you to dictate this sequence to be en, no, fr..., and
if you're interested I'll try to
> recall the method...
fins_in_set will help me with that... (see below)
> > so what I do is the following
> >
> > select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder
by
> > language2 desc;
>
> SELECT * FROM articles
> WHERE language = 'no' (etc or set construct)
> ORDER BY id;
well I could build an "where language='no' or language='se' or language...."
but I'd prefere not to do it that way since I'm trying to make a system
(standard within the company)
for this so that I can use the system on several different tables with
complex where clauses, etc..
> > I hope this explains better what I would like to do..
>
> =why ALL the articles and not just one?
Well getting a List of articles for example (that displays only the heading,
or the name, etc)
> =what is your 'prime' language - or better, what is the order of language
preference (from most prefered to
> least-used)
Well that depends on the user, for me it would be swedish, english,
norwegian, for a partner of mine it would be
norwegian, swedish, english..
I am after all not doing this program for me, but for the users, and the
users in this case will be very international!
> =it's getting too complicated for me - but someone who's good at this
stuff might be able to solve it for you
> without change.
>
> =At this point I'm wondering if it would be easier to act cowardly and run
away? How about changing the language
> column to a digit. Select which language is represented by which digit
according to your language preference.
> Then the ASC | DESC will work cheerfully/easily!
changeing the language column to a digit will not help since the sorting
would then have to be first #7 then #2 then #4 then all the rest.. no big
difference, besides it is a hell lot easier to debug when it says
language='en' and not language=34
for instance :-)
I have found out that with the version running here now, the temporary table
sollution works
(observer, the below is not TRUE sql)
create temporary table
tmp_articles(id,language,name,description,languagorder);
insert into tmp_articles (id,language,name,description,languagorder)
select *,FIND_IN_SET(language,'en,fr,no') languageorder from articles
order by languageorder desc;
select * from tmp_articles group by id;
will do what I want, however if the temporary table:s rows are not retrieved
inthe same order that they were inserted, or if the insert is not donw
ordered or if the group by does not take the first row it finds for each
group... then I'm at loss
but so far, all is allright, hopefully (if there is anyone reading this by
now :-) someone out there knows how rows are inserted, retrieved from
temporary tables and how group by works..
but I'd still would like to do this in a single query (order by before
group by perhepes? :-))))
/Christian Andersson
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
