Filtern nach mehreren Kriterien

intex

Neuer Benutzer
Beiträge
3
Hallo zusammen! Könnt ihr mir bitte helfen? Ich beiß mir hierbei gerade die Zähne aus...

Ich soll ein php Script überarbeiten, das in einer Funktion eine SQL-Abfrage (Oracle) erzeugt. Diese Funktion soll Objekte aus der Datenbank zurückgeben in Abhängigkeit von einem Parameter 'Filter', der ein Array aus Integers ist. Es fand ausserdem eine Vorselektierung statt nach weiteren Filtern, dieses liegt ebenfalls als Integer Array vor.

Pseudocode:
PHP:
function getObjects(int[] filterIDs, int[] objectIDsVorselektion)
{
$query = //dynamisch zusammengeaubt unter Berücksichtigung
//der filterIDs und Vorselektion
return $sql->Execute($query)
}

Das Script tut auch was es soll, jedoch wird der Query-String ab 3 Filtern (von bis zu 20 möglichen Filtern) unfassbar groß und dementsprechend auch langsamer, hier ein Beispiel einer solchen Query, wenn $filterIDs [81,82] ist und die Vorselektion ergeben hat, dass nur Objecte mit den IDs [32,51] berücksichtigt werden sollen:

Code:
"SELECT O.object_ID FROM objects O LEFT JOIN lookuptable A
ON A.ad_ID = O.object_ID WHERE (
  A.ad_ID = 32 AND A.filter_ID = 82 AND EXISTS 
(SELECT * FROM lookuptable B WHERE filter_ID = 81 AND B.ad_ID = 32))
OR (A.ad_ID = 51 AND A.filter_ID = 82 AND EXISTS 
(SELECT * FROM lookuptable B WHERE filter_ID = 81 AND B.ad_ID = 51))
AND O.object_ID IN (32,51)
ORDER BY O.time ASC

Die Vorselektierung besteht allerdings oft nicht nur aus 2 IDs, sondern aus über 100, was bedeutet, dass die Zeilen 5 und 6 schonmal über 100mal mit der jeweiligen IDs vorkommen. Und das für nur 2 gesetzte Filter!

Im Prinzip geht es doch, wenn ich das Skript richtig verstanden habe, darum:
select object_ID FROM ... WHERE object_ID in (Vorselektion) AND (alle gesuchten Filter sind für diese ID gesetzt)

der letzte Punkt ist natürlich das kritische. Die Lookuptable sieht wie folgt aus:

object_ID|filter_ID
32|81
51|82
32|82
51|100
11|101
11|81
10|100

Bei diesen Daten würde obige Funktion also nur die objectID 32 ausspucken, da nur für ID 32 die FilterID 81 und 82 vorhanden sind.

Wie kann man soeine Selektion effizienter machen als mit dem obigen Ansatz? Ich hoffe ich habe den Spagat geschafft und es nicht zu vage und nicht zu detailliert beschrieben :/

Viele Grüße
Christian
 
Werbung:

akretschmer

Datenbank-Guru
Beiträge
9.830
Hallo zusammen! Könnt ihr mir bitte helfen? Ich beiß mir hierbei gerade die Zähne aus...


der letzte Punkt ist natürlich das kritische. Die Lookuptable sieht wie folgt aus:

object_ID|filter_ID
32|81
51|82
32|82
51|100
11|101
11|81
10|100

Bei diesen Daten würde obige Funktion also nur die objectID 32 ausspucken, da nur für ID 32 die FilterID 81 und 82 vorhanden sind.

Ich würde es vielleicht mal so probieren (ich hoffe, ich hab dich richtig verstanden):

Code:
test=*# select * from lookup ;
 object_id | filter_id
-----------+-----------
  32 |  81
  51 |  82
  32 |  82
  51 |  100
  11 |  101
  11 |  81
  10 |  100
(7 rows)

Time: 0,171 ms
test=*# select * from (
  select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo
  where array[81,82] @> filter
  order by object_id
;
 object_id | filter
-----------+---------
  32 | {82,81}
(1 row)

Nun habe ich aber kein Oraggle, sondern PostgreSQL. Da aber Oraggle viel von PostgreSQL angekupfert hat (oder war es andersrum?),
sehe ich reale Chancen, dasß es so oder ähnlich in Oraggle gehen könnte.

Also, ich erstelle ein ARRAY aller filter je object_id, und schaue dann, ob es ein ARRAY gibt, welches ein ARRAY aus den geforderten Filtern enthält.

Hab ich das so richtig verstanden?
 

akretschmer

Datenbank-Guru
Beiträge
9.830
Ich würde es vielleicht mal so probieren (ich hoffe, ich hab dich richtig verstanden):

Code:
Time: 0,171 ms
test=*# select * from (
  select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo
  where array[81,82] @> filter
  order by object_id
;
object_id | filter
-----------+---------
  32 | {82,81}
(1 row)

Ähm, <@ wäre wohl richtiger... mach das auch ned alle Tagen.

Code:
test=*# select * from lookup ;
 object_id | filter_id
-----------+-----------
  32 |  81
  51 |  82
  32 |  82
  51 |  100
  11 |  101
  11 |  81
  10 |  100
  32 |  83
(8 rows)

Time: 0,174 ms
test=*# select * from (select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo where array[81,82] @> filter;
 object_id | filter
-----------+--------
(0 rows)

Time: 0,324 ms
test=*# select * from (select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo where array[81,82] <@ filter;
 object_id |  filter
-----------+------------
  32 | {83,82,81}
(1 row)
 

intex

Neuer Benutzer
Beiträge
3
Hallo akretschmer,

vielen Dank für Deine Antwort! Ich glaube Deinen Ansatz zu verstehen. Ich wiederum kenne mich mit Postgre überhaupt nicht aus - auch Oracle ist eigentlich nicht meine Welt.

Ich suche noch nach einem entsprechenden Pendant von array_agg...
Zunächst stellt sich mir aber noch die Frage wie ich aus meinem php Script das Object array[81,82] in den Query-String einbaue.

Wenn ich ein Pendant finden sollte und wissen würde wie ich das array einbaue, würde mein Query zum Schluß wie wolgt aussehen, richtig?

Code:
SELECT O.object_ID FROM objects O LEFT JOIN lookuptable A
ON A.ad_ID = O.object_ID WHERE O.object_ID in
(
select * from (select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo where array[81,82] <@ filter
)
O.object_ID IN (32,51)
ORDER BY O.time ASC
 

akretschmer

Datenbank-Guru
Beiträge
9.830
Hallo akretschmer,

vielen Dank für Deine Antwort! Ich glaube Deinen Ansatz zu verstehen. Ich wiederum kenne mich mit Postgre überhaupt nicht aus - auch Oracle ist eigentlich nicht meine Welt.

Ich suche noch nach einem entsprechenden Pendant von array_agg...
Zunächst stellt sich mir aber noch die Frage wie ich aus meinem php Script das Object array[81,82] in den Query-String einbaue.

Einfach:

Code:
test=*# select array[1,2,3];
  array
---------
 {1,2,3}
(1 row)

Wenn ich ein Pendant finden sollte und wissen würde wie ich das array einbaue, würde mein Query zum Schluß wie wolgt aussehen, richtig?

Code:
SELECT O.object_ID FROM objects O LEFT JOIN lookuptable A
ON A.ad_ID = O.object_ID WHERE O.object_ID in
(
select * from (select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo where array[81,82] <@ filter
)
O.object_ID IN (32,51)
ORDER BY O.time ASC

Fast richtig. Dein inneres Select liefert jetzt 2 Spalten, das würde dann natürlich bei dem Vergleich mit in (...) nicht gehen. Also nur die object_id abfragen an der Stelle. Also aus * einfach object_id machen, dann sollte das passen. Ach ja, die andere Condition noch richtig mit AND dazu nehmen.
 

intex

Neuer Benutzer
Beiträge
3
nochmals danke!

Code:
test=*# select array[1,2,3];
  array
---------
{1,2,3}
(1 row)

nein, ich meinte im PHP-Script! also wie sieht der String dann aus? etwa so? ich kann es gerade nicht ausprobieren...

PHP:
$filterArray = array(81,82);
$query = "
select * from (select object_id, array_agg(filter_id) as filter from lookup group by object_id) foo where "+$filterArray+" <@ filter";

stimmt, ein "AND" vergessen und das mit dem * macht natürlich Sinn :)

Dennoch glaube ich inzwischen, dass mich dieser Ansatz so nicht ohne weiteres zum Ziel führen wird, da es zwar für array_agg wohl ein Pendant namens Collection gibt, aber Oracle wohl von Haus aus keinen Vergleich zweier Collections bietet.
 
Werbung:

akretschmer

Datenbank-Guru
Beiträge
9.830
nochmals danke!

Code:
test=*# select array[1,2,3];
  array
---------
{1,2,3}
(1 row)

nein, ich meinte im PHP-Script! also wie sieht der String dann aus? etwa so? ich kann es gerade nicht ausprobieren...
Das schickst Du so aus PHP ab, also als array[x,y,z]. In PG hast dann ein Array.

Dennoch glaube ich inzwischen, dass mich dieser Ansatz so nicht ohne weiteres zum Ziel führen wird, da es zwar für array_agg wohl ein Pendant namens Collection gibt, aber Oracle wohl von Haus aus keinen Vergleich zweier Collections bietet.

Ähm, ja ;-)

Keine Ahnung, einklich gilt Oraggle ja als die eierlegende Wollmilchsau unter den Datenbanken, die alles kann. Aber ich kenne schon etliche Dinge, die es nicht kann...
 
Oben