Python Query of Item by Attributes (get_all on child table values)

batonac · December 1, 2022, 4:43pm

Thanks to the boost you give me @JeansReal, I was able to figure this out using PyPika, the Item Variant Attribute DocType, and a little help from stackoverflow.

Here’s my final solution:

from pypika import Tuple, functions as fn

item_attributes_query = [('Grand Teton','Width - ft','12'),('Grand Teton','Length - ft','12'),('Grand Teton','Bents','2')]

vattr = frappe.qb.DocType('Item Variant Attribute')
query = frappe.qb.from_(vattr).select(vattr.parent).where(
    Tuple(vattr.variant_of, vattr.attribute, vattr.attribute_value).isin(
        item_attributes_query
    )
).groupby(vattr.parent).having(fn.Count(vattr.parent) == len(item_attributes_query))

result = frappe.db.sql(query, as_dict=True)

if result:
    item_name = result[0]['parent']
    item = frappe.get_doc('Item', item_name)

Hopefully someone from the frappe dev team is paying attention to this thread and adds a way to accomplish that using frappe.get_all shorthand in the future, cause that’s complicated!