Flask SQL Alchemy querying a table with a != column condition

Question

Edit - This is what the working solution looked like for me

thanks to the answer below by @abhishek Jebaraj I was able to adapt the code as follows by changing the not_in_lists function.

def not_in_lists(self):
        query = List.query.filter(
            List.id.notin_(
                db.session.query(items_lists.c.list_id).filter(
                    items_lists.c.item_id == self.id)))
        list = []
        for i in query:
            list.append(i)

        return list

Original Post

My questions seems to be related to: Flask SQLAlchemy querying a column with "not equals", but can't seem to figure it out with the information there. Perhaps someone can help.

I have a many to many table and am trying to run 2 queries that separate one of the tables into two lists - those that have an item and those that don't.

I have a table called items and a table called lists with a many to many relationship. The functions shown below are part lf the item model class. When an item id is passed into the function it checks the association table for all records and then filters for its own value. I wrote a test function that creates 4 lists and adds 1 item to two of them.

The test then calls both of these functions. The first function correctly returns the 2 lists that it was added to. The second test incorrectly returns all 4 lists. The only difference between the two functions is == vs !=. Meaning my non equality condition isnt firing.

Using Flask with Flask-SQLAlchemy

The first query checking for equality works as expected, but the not equal query doesn't work at all.

I've tried the SQLAlchemy docs, tried using !=, not_, is not but not having any luck.

Googling doesn't yield any helpful results either.

In SQLITE3 this works and this is exactly what I'm trying to accomplish with flask-SQLAlchemy:

select * from List
left join items_lists
on items_lists.list_id = List.id
WHERE items_lists.item_id is not item.id;

Here's my code and thanks in advance for the guidance.

def in_list(self):
        return List.query.join(
            items_lists, (items_lists.c.list_id == list.id)
            ).filter(
               items_lists.c.item_id == self.id
               ) 
    
def not_in_list(self):
        return List.query.join(
            items_lists, (items_lists.c.list_id == list.id)
            ).filter(
                items_lists.c.item_id != self.id
                )

I'm testing with Python's unittest - here's the code that checks for the conditions. The test fails at self.assertEqual(nm1, [l2, l4]) instead of seeing [l2, l4] as the result, I'm getting [l1,l2,l3,l4] - per the test condition, item1 - i1 is in the lists - [l1, l3] so the second query condition isn't working.

def test_show_members(self):
        i1 = Item(text = "this is an item")
        i2 = Item(text = "This is a second item")
        i3 = Item(text = "This is a third item")
        i4 = Item(text = "This is a fourth item")


        l1 = List(title = "Canaries")
        l2 = List(title = "Bluejays")
        l3 = List(title = "Crows")
        l4 = List(title = "Ravens")

        db.session.add_all([i1,i2,i3,i4,l1, l2,l3,l4])
        db.session.commit()

        #i1 in 2 lists - Canaries + Crows
        i1.AddToList(l1)
        i1.AddToList(l3)
        
        #i2 in 1 list - Bluejays
        i2.AddToList(l2)
        
        #i3 in all lists - Canaries, Bluejays, Crows, Ravens
        i3.AddToList(l1)
        i3.AddToList(l2)
        i3.AddToList(l3)
        i3.AddToList(l4)
        #i4 not in any lists

        db.session.commit()

        m1 = i1.in_lists().all()
        m2 = i2.in_lists().all()
        m3 = i3.in_lists().all()
        m4 = i4.in_lists().all()

        nm1 = i1.not_in_lists().all()
        nm2 = i2.not_in_lists().all()
        nm3 = i3.not_in_lists().all()
        nm4 = i4.not_in_lists().all()  

        self.assertEqual(m1, [l1, l3])
        self.assertEqual(nm1, [l2, l4])
        
        self.assertEqual(m2, [l2])
        self.assertEqual(nm2, [l1, l3, l4])
        
        self.assertEqual(m3, [l1, l2, l3, l4])
        self.assertEqual(nm3, [])
       
        self.assertEqual(m4, [])
        self.assertEqual(nm4, [l1, l2, l3, l4])

additional things that aren't working:

.filter(
   items_lists.c.list_id == not_(self.id)
) 

.filter(
   items_lists.c.list_id == ~self.id
) 

This returns an empty list instead of having the two lists where item is not a member.

Answer 1

Replace the not_in_list function with the below one.

def not_in_list(self):
        return List.query.filter(List.id.notin_(session.query(item_lists.c.list_id).filter(item_lists.c.item_id == self.id)))

Its not working because when you say in your query != self.id , it means equal to everything else, including item 3 i3. But i3 is linked to all the lists. Therefore it returns all the lists to you.