Problem mit subquery, die eine Liste in Form von longtext zurückliefert

[fabmeyer]

Hallo. Ich habe die folgende query:

SET @portfolio_id = 1;
SELECT configure_country.name
FROM configure_country
WHERE configure_country.id
IN (
    SELECT filters_portfoliofilter.values
    FROM filters_portfoliofilter
    WHERE filter_id = 1
    AND portfolio_id = @portfolio_id;
);

die nicht funktioniert.

Wenn ich die folgende query laufen lasse:

SET @portfolio_id = 1;
SELECT filters_portfoliofilter.values
FROM filters_portfoliofilter
WHERE filter_id = 1
AND portfolio_id = @portfolio_id;

Dann kommt das folgende zurück:

values
3, 4, 7

eine Tabelle, die Spalte values hat jedoch den Typ longtext...

irgendwie muss ich die einzelnen, kommagetrennten Werte in integers umwandeln?

Setze ich in die IN Klausel nämlich eine Liste (z.B. "1, 2, 3") ein, dann funktioniert es.

Vielen Dank!

 

[ukulele]

Deine Annahme ist korrekt, aus dem String muss eine Werte-Liste werden, sonst geht es nicht. Wie das genau funktionieren kann ist sehr stark vom DBMS abhängig, ich habe da keine Erfahrung bei MariaDB oder MySQL, ich könnte dir die MSSQL Lösung aufschreiben . Vielleicht hilft das hier weiter:

Convert comma separated string to a list

I want to pass a list of int's (comma separated) which is a field in my table ie. 1234, 2345, 3456, 4567 to my IN clause in WHERE. But the list is a string (VARCHAR), and I'm comparing to an int

stackoverflow.com

 

[akretschmer]

das Problem ist tiefgreifender. Falsches Datenmodell. Wäre es korrekt, würde die Abfrage problemlos funktionieren.

 

[BerndB]

Versuch mal das. Sollte gehen, habs aber nicht getestet, da ich gerade keine DB frei habe.

SET @portfolio_id = 1;
SELEC filters_portfoliofilter.values INTO @ids
FROM filters_portfoliofilter
WHERE filter_id = 1
AND portfolio_id = @portfolio_id;
SELECT configure_country.name
FROM configure_country
WHERE FIND_IN_SET(configure_country.id.@ids) >0;

oder

SET @portfolio_id = 1;
SELECT configure_country.name
FROM configure_country
CROSS JOIN ( SELECT INTO filters_portfoliofilter.values
  FROM filters_portfoliofilter
  WHERE filter_id = 1
  AND portfolio_id = @portfolio_id) AS fp
WHERE FIND_IN_SET(configure_country.id, fp.values) >0;

Schreib mal obs geht, sonst schau ich später mal

 

[ukulele]

das Problem ist tiefgreifender. Falsches Datenmodell. Wäre es korrekt, würde die Abfrage problemlos funktionieren.

Ja schon, wenn du das ändern kannst dann solltest du das tun. Ich hab das aber auch so in z.B. Wordpress gesehen, was will man da machen wenn ein Vorsystem das so liefert.

 

[fabmeyer]

Versuch mal das. Sollte gehen, habs aber nicht getestet, da ich gerade keine DB frei habe.

SET @portfolio_id = 1;
SELEC filters_portfoliofilter.values INTO @ids
FROM filters_portfoliofilter
WHERE filter_id = 1
AND portfolio_id = @portfolio_id;
SELECT configure_country.name
FROM configure_country
WHERE FIND_IN_SET(configure_country.id.@ids) >0;

oder

SET @portfolio_id = 1;
SELECT configure_country.name
FROM configure_country
CROSS JOIN ( SELECT INTO filters_portfoliofilter.values
  FROM filters_portfoliofilter
  WHERE filter_id = 1
  AND portfolio_id = @portfolio_id) AS fp
WHERE FIND_IN_SET(configure_country.id, fp.values) >0;

Schreib mal obs geht, sonst schau ich später mal

Hallo Bernd und vielen vielen Dank für die Antwort. Leider bekomme ich bei beiden queries ein parse error zurück. Auch der online Validator gibt ein parse error zurück. Ich bin echt Anfänger in SQL und bin um jede Hilfe froh

 

[akretschmer]

Leider bekomme ich bei beiden queries ein parse error zurück.

postgres=# create table demo as select * from generate_series(1,10) s;
SELECT 10
postgres=# select * from demo;
 s  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

postgres=# create table liste (id int, filter text);
CREATE TABLE
postgres=# insert into liste values (1, '3, 4, 7');
INSERT 0 1
postgres=# select * from liste ;
 id | filter  
----+---------
  1 | 3, 4, 7
(1 row)
postgres=# select * from demo where  s in (select regexp_split_to_table(filter, ', ')::int from liste where id = 1);
 s 
---
 3
 4
 7
(3 rows)

postgres=#

Beachte #3

 

[fabmeyer]

postgres=# create table demo as select * from generate_series(1,10) s;
SELECT 10
postgres=# select * from demo;
 s 
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

postgres=# create table liste (id int, filter text);
CREATE TABLE
postgres=# insert into liste values (1, '3, 4, 7');
INSERT 0 1
postgres=# select * from liste ;
 id | filter 
----+---------
  1 | 3, 4, 7
(1 row)
postgres=# select * from demo where  s in (select regexp_split_to_table(filter, ', ')::int from liste where id = 1);
 s
---
 3
 4
 7
(3 rows)

postgres=#

Beachte #3

Danke für die Antwort. So wie ich das verstehe funktioniert die query aber nur für Postgres? Wie würde das für Mysql aussehen?
Ich hab jetzt diesen code hier:

SET @portfolio_id = 1;
SELECT configure_country.name
FROM configure_country
WHERE configure_country.id
IN (
SELECT regexp_split_to_table(filters_portfoliofilter.values, ', ')::int
FROM filters_portfoliofilter
WHERE filters_portfoliofilter.filter_id = 1
AND filters_portfoliofilter.portfolio_id = @portfolio_id
);

 

[dabadepdu]

regexp_split_to_table

das gibt es glaub ich nicht in MySQL

Und es ist vielleicht eine ziemliche Frickelei, das mit SQL Bordmitteln von MySQL zu verarbeiten.
Da Du bei diesem Datenmodel String Operationen verwenden musst und es nicht zerlegen kannst, wäre ggf. eine andere Modellierung ratsam.

Es gäbe theoretisch die Möglichkeit mit INSTR zu arbeiten (Zumindest gibt es tapfere "Lösungen" damit). Aber da kommt man vom Regen in die Traufe, wenn man exakte ID finden will, tatsächlich aber Ausschnitte anderer ID findet:
instr('3, 4, 7', '4')
geht super
instr('3,7, 44', '4')
geht leider auch super, ist aber hier vermutlich nicht gewollt.

Mir fällt keine mySQL Funktion ein dafür, ich nutze es aber auch nie. Es gibt hier mysql Experten, die vielleicht eine Lösung kennen.

 

[akretschmer]

Und es ist vielleicht eine ziemliche Frickelei, das mit SQL Bordmitteln von MySQL zu verarbeiten.

daher #3

 

[dabadepdu]

Aber wahrscheinlich würde als Stringoperation FIND_IN_SET(string, string_list) helfen.
Wäre jedenfalls besser als das bescheuerte Instr-Verfahren.

 

[ukulele]

Ich habe auch kein MySQL hier laufen aber hier eine vielversprechende Lösung für MySQL 8:

MYSQL8 - How to Split Dynamic Length String by a Delimeter?

Hi I have this sample strings from my database, my goal is to split the column value to individual columns of 5 and if the value is not present for other columns then it should return 0 as value Row1

stackoverflow.com

MySQL 8.0

> ``` status > Records: 2 Duplicates: 0 Warnings: 0 > ``` | id | value | |---:|:------| | 1 | 1,2,3 | | 2 | 4,5,6,7,8 |

dbfiddle.uk

Das muss man natürlich anpassen und wenn ich das richtig verstehe sind da noch Leerschritte hinter dem Trennzeichen aber wenn man dem Ansatz erstmal folgt bekommt man aus 3, 4, 7 eine vernünftige Tabelle die man joinen kann.

 

[BerndB]

Und es ist vielleicht eine ziemliche Frickelei, das mit SQL Bordmitteln von MySQL zu verarbeiten.

LOL

hier mal ein funktionsfähiges Beispiel in MySQL

DBFIDDLE Beispiel


Ammerkung:

Es ist keine besonders gute Idee Felder in der DB mit reservierten Keywords zu belegen.
Geht zwar, aber man muss diese dann IMMER in Backticks setzen

 

[dabadepdu]

LOL

Ja, sorry, habe im Post #11 bereits ergänzt und FIND_IN_SET vorgeschlagen.

 

[fabmeyer]

Hallo zusammen und vielen, vielen Dank! Ich bin nun zur weniger eleganten Lösung übergegangen dieses Problem in Python zu lösen (die SQL scripts werden innerhalb einer Python Applikation ausgeführt). Und ja, das Datenmodell könnte sicher besser sein. LG