[FIXED] Flask, SQLAlchemy, Can't compare a collection to an object or collection; use contains() to test for membership

Issue

I am trying to create an Alert table, and add a Command table to it. I want the name of the alert and the command associated with it to be displayed in the index page together. But I cannot get it. Could you look at my code and tell me my mistake?

jointable = db.Table('jointable',
    db.Column('command_id', db.Integer, db.ForeignKey('commands.id'), primary_key=True),
    db.Column('alert_id', db.Integer, db.ForeignKey('alerts.id'), primary_key=True))


class Command(db.Model):
    __tablename__='commands'
    id = db.Column(db.Integer, primary_key=True)
    cname = db.Column(db.Text)
    alertses = db.relationship('Alert', secondary=jointable, backref=db.backref('commands', lazy = 'select'))
    alert_id = db.Column(db.Integer)



class Alert(db.Model):
    __tablename__='alerts'
    id = db.Column(db.Integer, primary_key=True)
    aname = db.Column(db.Text)

If my request is like this: **Command = Command.query.join(Alert.commands).filter(Alert.commands == Command.id).all() , I get the error: Can't compare a collection to an object or collection; use contains() to test for membership

If my request is like this:Command = Command.query.join(Alert.commands).filter(Alert.id == Command.alertses).all()) I get: enter image description here

Solution

you didn't mention it clearly but ill assume that the relationship is one to many.

if this is not the case take a look at - sql-alchemy relationships doc

hope youll find this snippet helpful, notice i changed some naming conventions & used the Base class of sqlalchemy

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Command(Base): #child
    __tablename__='command'
    id = db.Column(Integer, primary_key=True, index=True)
    name = db.Column(Text)
    alert_id = db.Column(Integer, ForeignKey('alert.id'))


class Alert(Base): #parent
    __tablename__='alert'
    id = db.Column(Integer, primary_key=True, index=True)
    name = db.Column(Text)
    command = relationship("Command")

In order to query a specific alert & the related commands try this -

alert = Alert.query.get(id)

For all -

alerts - Alert.query.all()

the commands collection will be under alert.command

EDIT: If it all about the rendering part try this under your desired route -

alerts = Alert.query.all() 
return render_template('index.html', alerts=alerts)

in the index.html -

{% for alert in alerts %}
    <div>{{ alert.name }}</div>
    {% for command in alert.command &}
        <p>{{command.name}}</p>
    {% endfor %}
{% endfor %}

Answered By - IlayG01