Considering following peewee backed python code, how to filter data in a table based on values across rows in another table
e.g.
if I want to get a note in Note table having all java
, lambda
, generics
tags in customtags
#!/usr/bin/env python3 import peewee import datetime db = peewee.SqliteDatabase('test.db') class Note(peewee.Model): id = peewee.AutoField() name = peewee.CharField() text = peewee.CharField() created = peewee.DateField(default=datetime.date.today) class Meta: database = db db_table = 'notes' class CustomTag(peewee.Model): id = peewee.AutoField() note = peewee.ForeignKeyField(Note, backref='notes') tag = peewee.CharField() class Meta: database = db db_table = 'customtags' indexes = ((("note_id", "tag"), True),) Note.drop_table() CustomTag.drop_table() Note.create_table() CustomTag.create_table() note1 = Note.create(name="note1",text='Java 8 lambda with generics') note1.save() CustomTag.insert(note=note1, tag='java').on_conflict_ignore(True) CustomTag.insert(note=note1, tag='lambda').on_conflict_ignore(True) CustomTag.insert(note=note1, tag='generics').on_conflict_ignore(True) note2 = Note.create(name="note2",text='Core Java concepts', created=datetime.date(2018, 10, 20)) note2.save() CustomTag.insert(note=note2, tag='java').on_conflict_ignore(True) note3 = Note.create(name="note3",text='Java collection framework', created=datetime.date(2018, 10, 22)) note3.save() CustomTag.insert(note=note3, tag='java').on_conflict_ignore(True) note4 = Note.create(name="note4",text='Java NIO packageJava nio package') note4.save() CustomTag.insert(note=note4, tag='java').on_conflict_ignore(True) notes = Note.select().join(CustomTag, peewee.JOIN.LEFT_OUTER,on=(Note.id == CustomTag.note_id)).order_by(Note.name) for note in notes: print('{} with text {} on {}'.format(note.name, note.text, note.created))
I am really not having idea how to modify my code the get the data above mentioned, I know that issue is in following code
notes = Note.select().join(CustomTag, peewee.JOIN.LEFT_OUTER,on=(Note.id == CustomTag.note_id)).order_by(Note.name)
Advertisement
Answer
e.g. if I want to get a note in Note table having all java, lambda, generics tags in customtags
tags = ['foo', 'bar', 'baz'] query = (Note .select() .join(CustomTag) .where(CustomTag.tag.in_(tags)) .group_by(Note) .having(fn.COUNT(CustomTag.id) == len(tags)))