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

Frappe Framework Custom Script
,
1

How would I do a Python query for an Item variant based on the Template product and attributes?

I’ve tried all the following, but I get errors every single time:

item = frappe.get_all('Item', filters={
            'variant_of': 'Grand Teton',
            'attributes': {"Width - ft": 12, "Length - ft": 12, "Bents": 2}
        })

item = frappe.get_all('Item', filters={
            'variant_of': 'Grand Teton',
            'attributes': [
                {"attribute": "Width - ft", "attribute_value": 12},
                {"attribute": "Length - ft", "attribute_value": 12},
                {"attribute": "Bents", "attribute_value": 2}
            ]
        })

item = frappe.get_all('Item', filters=[
            ['variant_of', '=', 'Grand Teton'],
            ['attributes', 'in', [
                {"attribute": "Width - ft", "attribute_value": 12},
                {"attribute": "Length - ft", "attribute_value": 12},
                {"attribute": "Bents", "attribute_value": 2}
            ]]
        ])

The errors I’m getting are like this:

OperationalError: (1054, "Unknown column 'tabItem.attributes' in 'where clause'")
and
AttributeError: 'dict' object has no attribute 'lower'

So there’s clearly a syntax issue, but I don’t know how/where to refactor at this point.

Is this just not possible?

2

Hi @batonac,

Please check it.

Maybe help you.

Thank You!

3

@NCP, thanks for your reply!

I had checked both of those before posting this question. The forum post you mentioned is resolved with a Jinja script, which doesn’t fit my scenario.

The agiliq.com post mentions joining fields from child tables, which is great, but not filtering fields of child tables, which is my scenario.

I’m beginning to think this may simply not be possible…

4

Hi!
What version of frappe are you using?

Because from v13 we have Query Builder(PyPika)

https://frappeframework.com/docs/v13/user/en/api/query-builder

https://pypika.readthedocs.io/en/latest/2_tutorial.html#selecting-data

Let see an example

item = frappe.qb.DocType('Item')
item_variant = frappe.qb.DocType('Item Variant Attribute')

query = frappe.qb \
        .select(item.name, item.variant_of, item_variant.attribute, item_variant.attribute_value) \
        .from_(item_variant).join(item).on(item.name == item_variant.parent) \
        .where(item.variant_of == 'Grand Teton')

""" At this point you will get all Item Variants
You will need to find a way to query same column

This is because table 'tabItem Variant Attribute', uses two columns to store the data you want to filter: 'attribute' column and 'attribute_value' column
"""

# You can try from here. If you find a mysql example, sure it can be used with frappe.
query.where(
    Tuple(item_variant.attribute, item_variant.attribute_value).isin([
        ('Width - ft', 12),
        ('Length - ft', 12),
        ('Bents', 2)
    ])
)
# Or do it manually with python. Group all results using the item.name, and then create the custom dict and filter

Anyways let us know

2 Likes
5

@JeansReal, this is brilliant! Thanks so much!

I believe I have it working, with a slight variation on the above to match the syntax of the data in my specific variables.

Worth mentioning here is that I had to include:

from pypika import Tuple

In the header to get the Tuple in your snippet working.

Update: The solution above wasn’t quite sufficient. See the post below for a more comprehensive answer.

6

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! :sweat_smile:

2 Likes