Vermutlich ist es einfach nur schon zu spät, aber irgendwie hängt’s grade in meinem Kopf.

Folgendes Szenario:
Ich hab’ Objekt a das via Zuordnungstabelle (mittels ID) mit Tags 1 und 2 verknüpft wird. (Die max. Anzahl der Tags ist n …)
Jetzt möchte ich zufällig n andere Objekte x die ebenfalls mit Tag 1 oder 2 oder n verknüpft sind.
Und das ganze möglichst mit einem SQL-Query …

Quasi Objekte die logisch irgendwie mit Objekt a verknüpft sind. :)

Chriss bei Twitter ;)

Sowas?

Quellcode:select distinct obj from obj_tags where tag in (select tag from obj_tags where obj = a) order by random() limit n

\o tanz den naziprau! o/

And more than ever, I hope to never fall,
Where enough is not the same it was before

Jain :)

Also ich hab’ drei Tabellen:

Quellcode:Rezepte | Rez2Tag | Tags
--------|---------|--------
rID     |   rID   | tID
rTitel  |   tID   | tTitel
Ich hab’ Rezept 1 und alle dazugehörigen Tags. Jetzt möchte ich aber gerne alle anderen Rezepte, die den Tags zugeordnet sind, ebenfalls ausgeben:

Rezept 1:
Schokokuchen hat die Tags: Schokolade, Geil, Schuhsole …

Rezept 2:
Schwarzwälder Foo-Torte hat die Tags: Kirsche, Geil, Schuhsole …

Rezept 3:
Kalte Schnauze hat die Tags: Schokolade, Teig, Hund …

Rezept 4:
Katzenfutter hat die Tags: Kein Kuchen, Tunfisch, Halle Berry

Wenn ich jetzt in Rezept 1 bin, müsste er mir die Rezept 2 (-> Geil) und 3 (-> Schokolade) ausgeben. Klar soweit?

Man, ich krieg bei Datenbankkram immer Kopfschmerzen… besonders um zwei Uhr morgens! :D

Chriss bei Twitter ;)

Zitat:

theDon postete
Quellcode:select distinct obj from obj_tags where tag in (select tag from obj_tags where obj = a) order by random() limit n

Schlachtfestchen TF2 Community
Schlachtfestchen.de - Drink 'n' Fight: sfchen.de:27015
Jeden Donnerstag ist Custom-Map-Abend!

Das wuerde dir Dons Query doch liefern.
ninja'd...

The C language combines all the power of assembly language with all the ease-of-use of assembly language.
"humorig is n blödwort :>" by -CarniGGeLjumpR-

Ich hab’ gewusst ich hätte lieber schlafen gehen sollen. ;)

Nichts für ungut, danke don, danke Johannes …

Chriss bei Twitter ;)

Die vorgeschlagene Lösung ist nicht schlecht, aber falsch.

Arbeiten wir zu dem Zweck mit
Quellcode:PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "obj_tags" (
    "obj_tag" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "obj" INTEGER NOT NULL,
    "tag" TEXT NOT NULL
);
INSERT INTO "obj_tags" VALUES(67933,4,'bam');
INSERT INTO "obj_tags" VALUES(67934,1,'foo');
INSERT INTO "obj_tags" VALUES(67935,1,'bar');
INSERT INTO "obj_tags" VALUES(67936,2,'foo');
INSERT INTO "obj_tags" VALUES(67937,2,'baz');
INSERT INTO "obj_tags" VALUES(67938,3,'baz');
INSERT INTO "obj_tags" VALUES(67939,5,'bar');
INSERT INTO "obj_tags" VALUES(67940,5,'foo');
INSERT INTO "obj_tags" VALUES(67941,5,'baz');
INSERT INTO "obj_tags" VALUES(67942,6,'bar');
INSERT INTO "obj_tags" VALUES(67943,7,'bar');
INSERT INTO "obj_tags" VALUES(67944,8,'bar');
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('obj_tags',NULL,'12');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('obj_tags',67944);
COMMIT;
a sei immer 5, n sei auch immer 5.

Gefordert waren n *andere* Objekte die die gleichen Tags haben wie das Referenzobj.
Geliefert werden n Objekte die die gleichen Tags haben - ohne jedoch das Referenzobj auszuschliessen.

Quellcode:6
5
2
7
1
8
3
Richtig wäre demnach das referenzobj auszuschliessen:
Quellcode:select
    distinct obj
from
    obj_tags
where
    tag in (select tag from obj_tags where obj = a)
    and obj != a
order by
    random()
limit n
mein vorschlag wäre ja
Quellcode:SELECT
    DISTINCT o0.obj
FROM
    obj_tags AS o0
    JOIN (SELECT tag FROM obj_tags WHERE obj = a) AS tags USING(tag)
WHERE
    o0.obj != a
ORDER BY
    random()
LIMIT n
gewesen, davon abgesehen geht das natürlich auch ohne subquery
Quellcode:SELECT
    DISTINCT o0.obj
FROM
    obj_tags AS o0
    JOIN obj_tags AS o1 USING(tag)
WHERE
    o1.obj = a and o0.obj != a
ORDER BY
    random()
LIMIT n
Desweiteren finde ich das query nicht ideal - eigentlich die Aufgabenstellung -, schliesslich möchte man eigentlich keine n random empfehlungen haben, sondern idealerweise nur die n ähnlichsten empfohlen bekommen.
Die Ähnlichkeit kann man sich über die Anzahl der identischen Tags konstruieren.
Quellcode:SELECT
    o0.obj
    , count(o0.obj) as c
FROM
    obj_tags AS o0
    JOIN (SELECT tag FROM obj_tags WHERE obj = a) AS tags USING(tag)
WHERE
    o0.obj != a
GROUP BY
    o0.obj
ORDER BY
    COUNT(o0.obj) DESC
LIMIT n
Quellcode:1|2
2|2
3|1
6|1
7|1
8|1
Eine Gewichtung über den Informationsgehalt der tags des Referenzobjekts wäre auch möglich, der Vorteil wäre eine Bevorzugung von Beiträgen mit 'selteneren tags' die das Referenzobjekt hat.

Als Beispiel, das Referenzobj hat 2 tags, A und B, A ist sehr häufig (= es gibt viele andere Objekte die den Tag auch haben), B eher selten(=wenig andere Objekte die den Tag auch haben).
Die Objekte die auch den B tag haben sollten daher auch im Suchergebnis vorkommen, bevorzugt gegenüber allen Objekten die nur den häufigen A tag haben, und natürlich sollten objekte mit mehrfachtreffern (A&B) weiterhin priorisiert sein.
Bei komplexeren Beispielen kann es aber auch sein, dass ein obj mit n gleichen tags so Vorrang vor anderen obj mit mehr als n gleichen Tags bekommt. Das hat aber durchaus System und ich finde es eigentlich auch ideal.

Da das ohne grösseren Aufwand auch in einem Query geht kann man es einfach ersetzen:
Quellcode:SELECT
    o0.obj
    ,count(o0.obj) AS TagMatchCount
    ,SUM(val.I) AS InformationsgehaltSumme
FROM
    obj_tags AS o0
    JOIN (
        SELECT tag,
            -(
                log(
                    COUNT(tag)*1.0/(SELECT COUNT(*) from obj_tags) -- die Wahrscheinlichkeit 'p'
                )/
                log((SELECT COUNT(DISTINCT tag)*1.0 from obj_tags)) -- die gesamtanzahl unterschiedlicher tags 'a'
            ) AS I -- der 'Informationsgehalt' des jeweiligen tags
        FROM
            obj_tags
        GROUP BY
            tag) AS val USING(tag)
    JOIN (SELECT tag FROM obj_tags WHERE obj = a) AS tags USING(tag)
WHERE
    o0.obj != a
GROUP BY
    o0.obj
ORDER BY
    SUM(val.I) DESC;
LIMIT n
SQLite fehlt hier leider log(), kriegt man durch http://sqlite.org/contrib/download/extension-functions.c?get=25 && gcc -DHAVE_ISBLANK -DHAVE_LOG10 -DHAVE_COSH -DHAVE_SINH -DHAVE_TANH -DHAVE_ACOSH -DHAVE_ASINH -DHAVE_ATANH -lm -lsqlite3 -fPIC extension-functions.c -shared -o libsqlitefunctions.so

Quellcode:2|2|2.0
1|2|1.6315172029169
3|1|1.0
6|1|0.631517202916897
7|1|0.631517202916897
8|1|0.631517202916897
und, ich denke eigentlich sollte das in Coding verschoben werden?

Du musst Deine Bandbreite verbreitern, damit du breiter wirst von der Bandbreite her und ein breiteres Publikum ansprechen kannst.

Das klingt sehr geil, ich werd's heute Abend mal austesten. Besten dank! :)

Edit: Geil. Funktioniert perfekt!

Chriss bei Twitter ;)