Summe aus 3 counts aus 2 Tabellen

[PECeu]

Hallo zusammen,

ich brüte seit Längerem über einer Query für ein xenForo, also die selbe Forensoftware, die hier läuft.

MySQL ist mir an sich nicht neu, allerdings tue ich mich mit JOINs etwas schwer (k.A., warum ^^), mit Sub Selects habe ich mich ehrlich gesagt noch nie so richtig beschäftigt

Nun stoße ich sehr deutlich an meine Grenzen und bitte deswegen nun um Hilfe

Kurz zur Ausgangssituation und zum Ziel:
xF hat u.a. die folgenden Tabellen, deren Namen eigentlich selbstredend sind: xf_user, xf_liked_content und xf_post.
Ich würde gern, basierend auf dem gemeinsamen Nenner der User-ID, die 3 Tabellen so miteinander verknüpfen und abfragen, daß eine Top15 der User erstellt wird, die sortiert wird nach der Summe der jeweiligen Userwerte von erhaltenen und abgegebenen Likes sowie erstellten Posts innerhalb eines bestimmten Zeitraums.

Hier Teile der Tabellenstruktur (mit fiktiven Daten):

Tabelle xf_user
user_id | username
1 | Hans
2 | Petra
3 | Knut
4 | Uschi

Tabelle xf_liked_content
content_user_id | like_user_id | like_date
1 | 3 | 1442703832
1 | 2 | 1442704842
2 | 3 | 1442725632
2 | 1 | 1442743811
3 | 1 | 1442748832
2 | 4 | 1442755519
1 | 4 | 1442763832
Die content_user_id entspricht hierbei der user_id des Posterstellers, der den Like "erhält".
like_user_id entspricht der user_id des Users, der den Like "vergibt".

Tabelle xf_post
user_id | post_date | message_state
3 | 1442704842 | visible
1 | 1442725632 | visible
2 | 1442743811 | visible
3 | 1442748832 | visible
1 | 1442755519 | visible
4 | 1442763832 | visible
1 | 1442763833 | visible

Wunschergebnis wäre dann:
username | likes_erhalten | likes_abgegeben | posts | Summe
Hans | 3 | 2 | 3 | 8
Knut | 1 | 2 | 2 | 5
Petra | 3 | 1 | 1 | 5
Uschi | 0 | 2 | 1 | 3

Wobei ich ehrlich gesagt schon ganz froh wäre, wenn ich wenigstens folgendes hinbekommen würde:
username | likes_erhalten | posts | Summe
Hans | 3 | 2 | 5
Petra | 3 | 1 | 4
Knut | 1 | 2 | 3
Uschi | 0 | 2 | 2
Bei allen bisher erstellten Queries waren bei mir immer die Werte für likes und posts identisch
Scheinbar hab ich da ein Verständnisproblem.

Ich weiß nicht, ob man die Summen mit mySQL abbilden kann. Notfalls kann ich die im php-Script berechnen.
Ich wäre euch aber unendlich dankbar, wenn ihr mir bei der Bestimmung der Einzelwerte helfen könntet

EDIT: username und Anzahl der likes hole ich mir derzeit folgendermaßen aus der DB:

SELECT user.username, count(xf_liked_content.content_user_id) as l
                    FROM xf_liked_content
                    LEFT JOIN xf_user as user
                      ON xf_liked_content.content_user_id = user.user_id
                    WHERE xf_liked_content.like_date BETWEEN '1433113200'    AND '1435705200'
                    GROUP BY xf_liked_content.content_user_id
                    ORDER BY l DESC, xf_liked_content.like_date ASC
                    LIMIT 15;

Den Rest addier ich dann manuell ^^

Danke und Gruß,
PECeu

 

[akretschmer]

Mit Deinen Tabellen und Werten:

test=*# select *, likes_erhalten+likes_gegeben+posts as summe from (select xu.username, (select count(1) from xf_liked_content where content_user_id=xu.user_id) likes_erhalten, (select count(1) from xf_liked_content where like_user_id=xu.user_id) likes_gegeben, (select count(*) from xf_post where user_id=xu.user_id) posts from xf_user xu order by xu.username) bla;
 username | likes_erhalten | likes_gegeben | posts | summe
----------+----------------+---------------+-------+-------
 Hans  |  3 |  2 |  3 |  8
 Knut  |  1 |  2 |  2 |  5
 Petra  |  3 |  1 |  1 |  5
 Uschi  |  0 |  2 |  1 |  3
(4 rows)

Gemacht mit PostgreSQL, sollte vielleicht mit viel Glück MySQL auch beherrschen...

 

[PECeu]

Vielen Dank @akretschmer
Allerdings bekomme ich die FM
#1248 - Every derived table must have its own alias

Die bekomme ich aber auch, wenn ich jeder Tabelle einen eigenen Alias gebe:

select *,
    likes_erhalten+likes_gegeben+posts as summe from
        (select xu.username,
            (select count(1) from xf_liked_content xl where xl.content_user_id=xu.user_id) likes_erhalten,
            (select count(1) from xf_liked_content xl1 where xl1.like_user_id=xu.user_id) likes_gegeben,
            (select count(*) from xf_post xp where xp.user_id=xu.user_id) posts
        from xf_user xu order by xu.username);

 

[PECeu]

Nachtrag, da ich nicht mehr editieren kann:
Funktioniert jetzt
Nun muß ich nur noch rausfinden, wie ich verhindern kann, daß ich in allen 3 Sub Selects den Zeitraum einzeln angeben muß

select xall.*,
likes_erhalten+likes_gegeben+posts as summe from
(select xu.username,
(select count(1) from xf_liked_content xl1 where xl1.content_user_id=xu.user_id) likes_erhalten,
(select count(1) from xf_liked_content xl2 where xl2.like_user_id=xu.user_id) likes_gegeben,
(select count(*) from xf_post xp where xp.user_id=xu.user_id) posts
from xf_user xu order by xu.username) xall
ORDER BY summe DESC
LIMIT 15