flask sqlalchemy - Many To Many - filter

[Jora]

Guten Tag,

flask 1.0.2
sqlalchemy 1.2.13

Aufgabenstellung:
Mit einer kleinen APP möchte ich verschiedenen Flaschentypen unterschiedliche Deckelsymbole zuweisen (M2M)

Datenbankstruktur:
[Arten]-1--m-[Getraenke]-1--m-[Flaschen]-m--n-[Deckelsymbole]

Problem:
Meine Datenbankabfrage mittels .join und .filter liefert mir alle Getraenke und Flaschentypen auch wenn diese nicht das passende Deckelsymbol haben. Lediglich die Arten, die das Symbol auf den Flaschen noch nicht haben, werden herausgefiltert.

Code: Alles auswählen

arten = Arten.query.join(Getraenke).join(Flaschen).join((Deckelsymbole, Flaschen.deckelsymbole).filter(Deckelsymbole.id == 1).all()

die Abfrage mit .filter(Deckelsymbole.id == 1) liefert diese Ausgabe:
-Art#1
---Getraenk#1
------Flasche#1
------Flasche#2
------Flasche#3
-----------Deckelsymbol#1
------Flasche#4
-----------Deckelsymbol#1
---Getraenk#2
------Flasche#5
------Flasche#6


gewünschte Ausgabe:
-Art#1
---Getraenk#1
------Flasche#3
-----------Deckelsymbol#1
------Flasche#4
-----------Deckelsymbol#1

Frage:
wo liegt mein Denk- bzw. Erwartungsfehler?

Vielen Dank!

models.py

Code: Alles auswählen

subs_deckelsymbole_flaschen = db.Table('subs_deckelsymbole_flaschem', db.Model.metadata,
				db.Column('deckelsymbol_id', db.Integer, db.ForeignKey('deckelsymbole.id')),
				db.Column('flasche_id', db.Integer, db.ForeignKey('flaschen.id')))
				
class Deckelsymbole(db.Model):

	__tablename__ = 'deckelsymbole'
	
	id = db.Column(db.Integer, primary_key=True)
	symbol = db.Column(db.String(20))
	
	# Many To Many: 
	# unterschiedliche Deckelsymbole für den gleichen Flaschentyp
	# unterschiedliche Falschen mit gleichem Deckelsymbol
				
	deckelsymbole_flaschen = db.relationship('Flaschen', secondary=subs_deckelsymbole_flaschen,
									backref=db.backref('deckelsymbole', lazy='dynamic'))
									
	def __int__(self, symbol):
		self.symbol = symbol			
				
class Arten(db.Model):

	__tablename__ = 'arten'
	
	id = db.Column(db.Integer, primary_key=True)			
	art = db.Column(db.String(20) nullable=False, default='default_art')  
		# z.B alkoholfrei
				
	getraenke = db.relationship('Getraenke', backref='art', lazy='dynamic')
	
	def __int__(self, art):
		self.art = art
		
class Getraenke(db.Model):

	arten = db.relationship('Arten')

	__tablename__ = 'getraenke'			
		
	id = db.Column(db.Integer, primary_key=True)			
	getraenk = db.Column(db.String(20) nullable=False, default='default_getraenk')  
		# z.B Sprudel		
		
	art_id = db.Column(db.Integer, db.ForeignKey('arten.id'), nullable=False)
	
	def  __int__(self, getraenk):
		self.getraenk = getraenk
		
class Flaschen(db.Model):
	
	getraenke = db.relationship('Getraenke')
	
	__tablename__ = 'flaschen'
	
	id = db.Column(db.Integer, primary_key=True)			
	flasche = db.Column(db.String(20) nullable=False, default='default_flasche')  
		# z.B Dose			
	
	getraenk_id = db.Column(db.Integer, db.ForeignKey('getraenke.id'), nullable=False)
	
	def  __int__(self, flasche, getraenk_id):
		self.flasche = flasche
		self.getraenk_id = getraenk_id				
				

[sparrow]

Lass dir doch mal den generierten SQL-Code anzeigen:

Code: Alles auswählen

arten = Arten.query.join(Getraenke).join(Flaschen).join((Deckelsymbole, Flaschen.deckelsymbole).filter(Deckelsymbole.id == 1))
print(str(arten))

sollte das tun, wenn ich mich richtig erinnere.

Ohne das ausprobiert zu haben: Ich denke du wirst feststellen, dass die WHERE-Clause nur ein Subquery einschränken wird und nicht die ganze Abfrage.

Ich finde übrigens die Datenstruktur ein bisschen seltsam. Warum fängst du denn von "Arten" an und nicht von "Getraenke"? Brauchst du denn die Art überhaupt in der Query? Für mich wäre das Getränk doch "das Objekt" und die Art nur ein Merkmal davon.

Übrigens kann man dir nur schwer helfen, wenn du nciht den Code zeigst, den du wirklich verwendest. Denn genau da liegt der Fehler:

Code: Alles auswählen

>>> x = "arten = Arten.query.join(Getraenke).join(Flaschen).join((Deckelsymbole, Flaschen.deckelsymbole).filter(Deckelsymbole.id == 1).all()"
>>> x.count("(")
6
>>> x.count(")")
5
>>> 

[Jora]

Guten Tag,

danke für deine Antwort sparrow.

Den Code habe ich freihand abgetippt und den unwichtigen Ballast weggelassen.
(dabei haben sich einige Tippfehler eingeschlichen)

Anbei ein Screenshot von der Ausgabe (bereinigte APP):
https://bilder-teilen.de/image/fLln


Die SQLAlchemy-Datenbankabfrage ohne .all() gibt mit print() (zeile: 28 in app.py) den folgenden SQL-Code aus:

Code: Alles auswählen

SELECT arten.id AS arten_id, arten.name AS arten_name
FROM arten JOIN getraenke ON arten.id = getraenke.art_id JOIN flaschen ON getraenke.id = flaschen.getraenk_id JOIN subs_deckelsymbole_flaschem AS subs_deckelsymbole_fla
schem_1 ON flaschen.id = subs_deckelsymbole_flaschem_1.flasche_id JOIN deckelsymbole ON deckelsymbole.id = subs_deckelsymbole_flaschem_1.deckelsymbol_id
WHERE deckelsymbole.id = ?

models.py

Code: Alles auswählen

from mygetraenke import db

subs_deckelsymbole_flaschen = db.Table('subs_deckelsymbole_flaschem', db.Model.metadata,
				db.Column('deckelsymbol_id', db.Integer, db.ForeignKey('deckelsymbole.id')),
				db.Column('flasche_id', db.Integer, db.ForeignKey('flaschen.id')))

class Deckelsymbole(db.Model):

	__tablename__ = 'deckelsymbole'

	id = db.Column(db.Integer, primary_key=True)
	name = db.Column(db.String(20))

	# Many To Many:
	# unterschiedliche Deckelsymbole für den gleichen Flaschentyp
	# unterschiedliche Falschen mit gleichem Deckelsymbol

	deckelsymbole_flaschen = db.relationship('Flaschen', secondary=subs_deckelsymbole_flaschen,
									backref=db.backref('deckelsymbole', lazy='dynamic'))

	def __int__(self, name):
		self.name = name

class Arten(db.Model):

	__tablename__ = 'arten'

	id = db.Column(db.Integer, primary_key=True)
	name = db.Column(db.String(20), nullable=False, default='default_art')
		# z.B alkoholfrei

	getraenke = db.relationship('Getraenke', backref='art', lazy='dynamic')

	def __int__(self, name):
		self.name = name

class Getraenke(db.Model):
	
	arten = db.relationship('Arten')
	
	__tablename__ = 'getraenke'
	
	id = db.Column(db.Integer, primary_key=True)
	
	name = db.Column(db.String(20), nullable=False, default='default_getraenk')
	
	art_id = db.Column(db.Integer,db.ForeignKey('arten.id'), nullable=False)
	
	flaschen = db.relationship('Flaschen', backref='getraenk', lazy='dynamic')
	
	def  __int__(self, name, art_id):
		self.name = name
        	self.art_id = art_id

class Flaschen(db.Model):

	getraenke = db.relationship('Getraenke')

	__tablename__ = 'flaschen'

	id = db.Column(db.Integer, primary_key=True)

	name = db.Column(db.String(20), nullable=False, default='default_flasche')
		# z.B Dose

	getraenk_id = db.Column(db.Integer, db.ForeignKey('getraenke.id'), nullable=False)

	def  __int__(self, name, getraenk_id):
		self.name = name
		self.getraenk_id = getraenk_id

app.py

Code: Alles auswählen

from mygetraenke import app, db, login_required
from flask import render_template, redirect, request, url_for, flash, abort
from mygetraenke.models import Users, Arten, Getraenke, Flaschen, Deckelsymbole

@app.route('/')
def index():

    #
    
    return render_template('home.html')


@app.route('/list_affe')
def list_affe():

    symbol = Deckelsymbole.query.filter_by(name='Affe').first()

    print("symbol: "+str(symbol.id))

    arten = Arten.query.join(Getraenke).join(Flaschen).join((Deckelsymbole, Flaschen.deckelsymbole)).filter(Deckelsymbole.id == symbol.id).all()

    print(Arten.query.join(Getraenke).join(Flaschen).join((Deckelsymbole, Flaschen.deckelsymbole)).filter(Deckelsymbole.id == symbol.id))

    return render_template('list.html', symbol=symbol, arten=arten)

if __name__ == '__main__':
    app.run(port=5010, debug=True)

list.html

Code: Alles auswählen

{% extends "base.html" %}

{% block content %}

<div class="container">
  <div class="jumbotron">
    <h3>Flaschen mit Deckelsymbol: {{symbol.name}}</h3>

  </div>

  <div class="jumbotron">
    <ul>

      {% for art in arten %}
      <li>{{art.name}}
        <ul>

          {% for getraenk in art.getraenke %}
          <li>{{getraenk.name}}
            <ul>
              {% for flasche in getraenk.flaschen %}
              <li>
                {{flasche.name}}
                <ul>
                  {% for deckelsymbol in flasche.deckelsymbole %}
                    <li>
                    {{deckelsymbol.name}}
                  </li>
                  {% endfor %}
                </ul>
              </li>
              {% endfor %}
            </ul>
          </li>
          {%endfor %}
        </ul>
      </li>
      {% endfor %}
    </ul>

  </div>

</div>


{% endblock %}

[__blackjack__]

Ich vermute mal das Du nicht bedenkst das Deine Abfrage in der Route nicht die einzige ist und das die auf die weiteren Abfragen die Dein Template implizit auslöst, keinen weiteren Einfluss hat. Da gelten dann nicht auf magische Weite irgendwelche Bedingungen weiter/erneut.

[Jora]

Guten Tag,

danke!

anbei mein Lösungsansatz:

1. vorgefilterten Datensatz aus Datenbank holen
2. den Datensatz nach dem gewünschten Symbol durchsuchen
3. die gewünschten Daten in eine verschachtelte Liste schreiben
4. die verschachtelte Liste im Frontend ausgeben

https://bilder-teilen.de/image/fVuf

Code: Alles auswählen

@app.route('/list_affe')
def list_affe():

    # Auflistung der Arten und Getraenke in denen die Flaschen mit dem Deckelsymbol Affe enthalten sind
    
    symbol = "Affe"
    
    arten = Arten.query.join(Getraenke).join(Flaschen).join((Deckelsymbole, Flaschen.deckelsymbole)).filter(Deckelsymbole.name == symbol).all()

    flaschen_liste = []
    getraenke_liste = []
    arten_liste = []
    liste = []
    
    for art in arten:
        for getraenk in art.getraenke:
            for flasche in getraenk.flaschen:
                for deckelsymbol in flasche.deckelsymbole:
                    if deckelsymbol.name == symbol:
                        flaschen_liste.append(flasche)
                    else:
                        print("falsches Deckelsymbol")

            if flaschen_liste != []:
                getraenke_liste.append((getraenk, flaschen_liste))
                flaschen_liste = []

        arten_liste=((art.name, getraenke_liste))
        getraenke_liste = []

    liste.append(arten_liste)

    return render_template('list.html', liste = liste, symbol = symbol)

[Sirius3]

Ich versteh Deinen join nicht, wenn Du dann doch wieder alle Getränke, Flaschen und Deckel durchgehst und zum Schluß händisch nach dem Symbol filterst?
Viel besser wäre es doch, die Rückreferenzen zu benutzen und nur mit den richtigen Deckelsymbolen anzufangen.

Zum Code: Listen erst dann anlegen, wenn sie gebraucht werden, nicht nach gebrauch wieder leeren. `liste` enthält immer nur ein Element, wird also eigentlich gar nicht gebraucht.

Code: Alles auswählen

    arten_liste = []
    for art in arten:
        getraenke_liste = []
        for getraenk in art.getraenke:
            flaschen_liste = []
            for flasche in getraenk.flaschen:
                for deckelsymbol in flasche.deckelsymbole:
                    if deckelsymbol.name == symbol:
                        flaschen_liste.append(flasche)
                    else:
                        print("falsches Deckelsymbol")
            if flaschen_liste:
                getraenke_liste.append((getraenk, flaschen_liste))
        arten_liste.append((art.name, getraenke_liste))
    return render_template('list.html', liste=[arten_liste], symbol=symbol)

[Jora]

Guten Tag,

danke für den Tipp mit der Initialisierung der Listen!

Mit den vielen .join's gelange ich von der obersten Ebene [Arten] bis zur untersten Ebene [Deckelsymbole] und kann die Filterfunktion (.filter) auf Deckelsymbole anwenden.
Leider wird nur die oberste Ebene (Arten) gefilter.
Die Ebene darunter (Getraenke) bleibt ungefiltert. (siehe Ausgabe im ersten Threadpost)

Nutze ich die Rückreferenzen ist der Datensatz ohne Ebenen.
https://www.imagebanana.com/s/1295/NLI7F0KF.html

Code: Alles auswählen

@app.route('/list_hase')
def list_hase():
    
    symbol = "Hase"

    deckelsymbol = Deckelsymbole.query.filter(Deckelsymbole.name == symbol).first()

    return render_template('list_hase.html', symbol = symbol, deckelsymbol=deckelsymbol)

[Sirius3]

Die Struktur kannst Du Dir doch ganz einfach auf andere Art bauen:

Code: Alles auswählen

@app.route('/list_affe')
def list_affe():
    symbol = "Affe"
    deckelsymbol = Deckelsymbole.query.filter_by(name=symbol).first()
    arten = defaultdict(
        lambda: defaultdict(list)
    )
    for flasche in deckelsymbol.deckelsymbole_flaschen:
        getraenk = flasche.getraenk
        art = getraenk.art
        arten[art.name][getraenk.name].append(flasche.name)
    return render_template('list.html', liste=arten, symbol=symbol)

[Jora]

Vielen Dank Sirius3,

auf diese Art eine Struktur zu bauen, ist wirklich einfach und gleichzeitig praktisch.
So einfach wie einen PC selber zusammenzubauen. (wenn man es einmal gesehen hat)

Problem gelöst und mehr dazugelernt.

https://bilder-teilen.de/image/txfP

Danke @all