SQL query for Multilang classes.

Piotr Pokora

SQL query for Multilang classes.
Sun May 11 2008 12:00:06 UTC
Hi!

Last time when I checked similiar query I did it with MySQL 4.1, and now
things looks very good.

Here's query :

SELECT sid, title, lang FROM (SELECT sid, title, lang, CASE WHEN lang=44
THEN 1 ELSE 0 END AS lang_set FROM article,article_i WHERE
article_i.sid = article.id AND lang IN (44,125) ORDER BY lang_set DESC)
AS t GROUP BY sid;

It works with MySQL and PostgreSQL, so we can implement and start using
quickly.
Most important i sfact that we should fix all annoying LIMIT issues and
select as much records as we really need.
With default language fallback of course!

I would like to rewrite this part of QB this week and make 1.8.8 release
with this fix.
Please, let me know if you found some hidden crap in the query.

Ah! Very important. How should we treat user's LIMIT or OFFSET?
Should we take into account all languages records or final results only?
The latter seems to be very OK for me.

Basically, this:

SELECT sid, title, lang FROM (SELECT sid, title, lang, CASE WHEN lang=44
THEN 1 ELSE 0 END AS lang_set FROM article,article_i WHERE
article_i.sid = article.id AND lang IN (44,125) ORDER BY lang_set DESC
LIMIT 2) AS t GROUP BY sid;

VS

SELECT sid, title, lang FROM (SELECT sid, title, lang, CASE WHEN lang=44
THEN 1 ELSE 0 END AS lang_set FROM article,article_i WHERE
article_i.sid = article.id AND lang IN (44,125) ORDER BY lang_set DESC)
AS t GROUP BY sid LIMIT 2;

Piotras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev
- Reply
- Report abuse
Arttu Manninen

Re: [midgard-dev] SQL query for Multilang classes.
Sun May 11 2008 15:55:06 UTC
Piotr Pokora wrote:
> Hi!
>
> Last time when I checked similiar query I did it with MySQL 4.1, and now
> things looks very good.
>
> Here's query :
>
>
> SELECT sid, title, lang FROM (SELECT sid, title, lang, CASE WHEN lang=44
> THEN 1 ELSE 0 END AS lang_set FROM article,article_i WHERE
> article_i.sid = article.id AND lang IN (44,125) ORDER BY lang_set DESC)
> AS t GROUP BY sid;

Very good. This has been long waited for, since I remember bumping up
into the problem already with Midgard 1.8.2.

> Ah! Very important. How should we treat user's LIMIT or OFFSET?
> Should we take into account all languages records or final results only?
> The latter seems to be very OK for me.

The best option would be to have it configurable. Especially with
`multilang_strict` I think it should take *only* the translated and to
do it automatically. Of the results in general I think that latter would
do fine.

--
Arttu

_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev
- Reply
- Report abuse
Piotr Pokora

Re: [midgard-dev] SQL query for Multilang classes.
Sun May 11 2008 17:50:08 UTC
Arttu Manninen writes:

>> Ah! Very important. How should we treat user's LIMIT or OFFSET?
>> Should we take into account all languages records or final results only?
>> The latter seems to be very OK for me.
>
> The best option would be to have it configurable. Especially with
> `multilang_strict` I think it should take *only* the translated and to
> do it automatically. Of the results in general I think that latter would
> do fine.

In such case QB can transparently run typical, non ML query.
I'll take it into account :)

Poitras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev
- Reply
- Report abuse
Piotr Pokora

Re: [midgard-dev] SQL query for Multilang classes.
Mon May 12 2008 14:15:03 UTC
Arttu Manninen writes:
>> SELECT sid, title, lang FROM (SELECT sid, title, lang, CASE WHEN lang=44
>> THEN 1 ELSE 0 END AS lang_set FROM article,article_i WHERE
>> article_i.sid = article.id AND lang IN (44,125) ORDER BY lang_set DESC)
>> AS t GROUP BY sid;
>
> Very good. This has been long waited for, since I remember bumping up
> into the problem already with Midgard 1.8.2.

Found first issue. Ordering seems to be a bit wrong depending on what
you expect.
Part of SQL is : "ORDER BY midgard_fallback_language DESC ,
article_i.title ASC"
so in my case, result is ( in form title(lang) ):

1: PL(125)
2: Sample site started(0)
3: Aegir Sample Site(0)

Which obviously should be :

1: Sample site started(0)
2: PL(125)
3: Aegir Sample Site(0)

Comments?

Piotras

_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev
- Reply
- Report abuse
Eero af Heurlin

Re: [midgard-dev] SQL query for Multilang classes.
Wed May 14 2008 06:19:10 UTC

MidCOM 'multilang_strict' "only" sets qb/collector lang transparently and when loading ML DBA objects checks their lang against the current language and returns failure if mismatched.

Core works as usual, returning fallback language if requested language is not available (unless qb lang is set).

Of course for Midgard 2 it could be handy to specify some global ML strict setting but that will inevitable lead to issues like http://trac.midgard-project.org/ticket/237 and similar setting value juggling.

/Rambo
- Reply
- Report abuse
Piotr Pokora

Re: [midgard-dev] SQL query for Multilang classes.
Wed May 14 2008 06:35:02 UTC
Eero af Heurlin writes:
> MidCOM 'multilang_strict' "only" sets qb/collector lang transparently and when loading ML DBA objects checks their lang against the current language and returns failure if mismatched.
> Core works as usual, returning fallback language if requested language is not available (unless qb lang is set).
>
> Of course for Midgard 2 it could be handy to specify some global ML strict setting but that will inevitable lead to issues like http://trac.midgard-project.org/ticket/237 and similar setting value juggling.

QB and collector strict mode is desired mode.
Midgard core ( 1.8.x, 1-9 branch, trunk ) uses two kind of languages.
Default one and "settable" one, if both are set to the same language,
then you have kind of global strict mode.

But the real problem with SQL and ML issue I mentioned are real use
cases for ML.
How many cases there are, where ordering with language fallback is
feature key?

Piotras
_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev
- Reply
- Report abuse
Eero af Heurlin

Re: [midgard-dev] SQL query for Multilang classes.
Wed May 14 2008 07:05:02 UTC
Piotr Pokora wrote:
> Eero af Heurlin writes:
>> Of course for Midgard 2 it could be handy to specify some global ML strict setting but that will inevitable lead to issues like http://trac.midgard-project.org/ticket/237 and similar setting value juggling.
>>
>
> QB and collector strict mode is desired mode.
> Midgard core ( 1.8.x, 1-9 branch, trunk ) uses two kind of languages.
> Default one and "settable" one, if both are set to the same language,
> then you have kind of global strict mode.
>

Point, didn't think it like that (setting fallback language and
preferred language to same)...

>
> But the real problem with SQL and ML issue I mentioned are real use
> cases for ML.
> How many cases there are, where ordering with language fallback is
> feature key?
>

I'm not sure if there are any real use cases for ordering by the
language, some people have tried add_order('lang') in order to work
around the limit issue (http://trac.midgard-project.org/ticket/129), but
the "fix" in fact isn't, it causes other seemingly random ordering
issues (which are *nasty* to debug).

Then a log of my discussion with piotras:

---<snip>---

9:31Wednesday, May 14, 2008
Piotr Pokora
9:31
hi, just replied to your mail

Eero af Heurlin
9:31
which one, the ML strict ?
Piotr Pokora
9:32
yep
I think we need to sacrifice something wrt multilang

Eero af Heurlin
9:35
probably, it's a difficult thing
Piotr Pokora
9:35
nope it's not difficult :)
it's f** difficult :)
I think we could resolve it somehow using kind of "foreach" subqueries
but it's not acceptable for performance reason
even if those could be done internally by provider

Eero af Heurlin
9:39
I wonder if Tero has any good ideas
Piotr Pokora
9:40
either this is really difficult issue or solution is so simply that we
can not see it :)

Eero af Heurlin
9:40
yep
I guess if we decide that unless one adds an order to QB the order of
results will be undefined (because the ML order by lang messes them up)
though even then we have wierd order when we have equal weight before
the lang order
Piotr Pokora
9:43
yes, but I need to order them by lang to exclude one if both exists
the other solution is to order returned list , but it will work in
object properties scope only
so no orders by topic.creator for example

Eero af Heurlin
9:44
it is problematic...
Piotr Pokora
9:45
or , maybe we could add spepcial QB method to make kind of "post order"
but API won't be clean and intuitive I suppose

Eero af Heurlin
9:46
I wonder if we should make this in 1.8 afterall
it seems we'll have to rethink some parts of ML
Can I post this discussion ?
9:51 *is too lazy to write it into coherent format*
Piotr Pokora
9:57
sure

---</snip>---

/Rambo

_______________________________________________
dev mailing list
dev@lists.midgard-project.org
http://lists.midgard-project.org/mailman/listinfo/dev
- Reply
- Report abuse