Automating Attributes (If in this (size) category > apply this attribute)

[wolfderby]

1.5.6

I was going to try to build a little script to blast in minimal data into the database but I'm struggling w/ an SQL query that would join the necessary data...

SELECT
p.`products_id`, p.`products_status`, p2c.*
FROM
`products` AS p
INNER JOIN
`products_to_categories` AS p2c
ON (p.`products_id` = p2c.`products_id`
AND p.`products_status` = 1
AND p2c.`categories_id` = 510)
Left JOIN
`products_attributes` AS pa
ON (pa.`products_id` = p.`products_id`
AND pa.`options_values_id` != 91)


..this query doesn't seem to succesfully apply the != 91...

Any thoughts/help would be helpful.

Pseudo code for "quick n dirty" context...

//run select query to get product_id's in Size 8 CATEGORY that do not have the Size 8 ATTRIBUTE

//apply/insert into size 8 attributes into attributes table

^then hard code into buttons.


Psuedo code for more useful version?
//make db table w/ 2/3 columns
//col 1 = id
// column 2 = size category id
// column 3 = size attribute ids to apply (comma delimited)
//create button to apply all paired category > size attributes


...also disclaimer, I've used attributes controller, was just looking for something I could build skills around doing, and want a way to quickly fix things I didn't easy populate attributes onto.

maybe I should explore easy populate a bit more a tool for this particular problem... but yeah, any thoughts?

[mc12345678]

I'm thinking (because I haven't run the query) that you are actually looking to select pa.products_id instead of p.products_id... Right now you are receiving all products_id in the products table that are equal to or linked to categories_id 510... Oh and for the sake of "study" making it possible to retrieve information about attributes, but without any specific request of attribute related data...

SELECT
pa.`products_id`, p.`products_status`, p2c.*
FROM
`products` AS p
INNER JOIN
`products_to_categories` AS p2c
ON (p.`products_id` = p2c.`products_id`
AND p.`products_status` = 1
AND p2c.`categories_id` = 510)
Left JOIN
`products_attributes` AS pa
ON (pa.`products_id` = p.`products_id`
AND pa.`options_values_id` != 91)

In the future, it may be of more assistance to identify what is meant by "does not successfully apply"...

As for an external importer/exporter type program, yeah, that *should* be easy to address...
export all product within a category (including the products_id as part of the export) to be able to identify the products_id of all involved.
Export the basic attributes,
match the products_ids across the two downloads removing the products_ids in the attributes group that don't apply.
Identify the products that don't have the desired attribute.
Create a file to add/import that information.
Submit/process.

[mc12345678]

I'm thinking (because I haven't run the query) that you are actually looking to select pa.products_id instead of p.products_id... Right now you are receiving all products_id in the products table that are equal to or linked to categories_id 510... Oh and for the sake of "study" making it possible to retrieve information about attributes, but without any specific request of attribute related data...

SELECT
pa.`products_id`, p.`products_status`, p2c.*
FROM
`products` AS p
INNER JOIN
`products_to_categories` AS p2c
ON (p.`products_id` = p2c.`products_id`
AND p.`products_status` = 1
AND p2c.`categories_id` = 510)
Left JOIN
`products_attributes` AS pa
ON (pa.`products_id` = p.`products_id`
AND pa.`options_values_id` != 91)

In the future, it may be of more assistance to identify what is meant by "does not successfully apply"...

As for an external importer/exporter type program, yeah, that *should* be easy to address...
export all product within a category (including the products_id as part of the export) to be able to identify the products_id of all involved.
Export the basic attributes,
match the products_ids across the two downloads removing the products_ids in the attributes group that don't apply.
Identify the products that don't have the desired attribute.
Create a file to add/import that information.
Submit/process.

Though, on second thought (because this was bugging me), what likely is desired is to know all product in the products table that are associated with category 510 where the product do not have an attribute associated with options_values_id of 91, this can be obtained by identifying all product in the category, then all product that have the attribute of concern and return the product that do not have that attribute. So something like:

SELECT
p.`products_id`, p.`products_status`, p2c.*
FROM
`products` AS p
INNER JOIN
`products_to_categories` AS p2c
ON (p.`products_id` = p2c.`products_id`
AND p.`products_status` = 1
AND p2c.`categories_id` = 510)
Left JOIN
`products_attributes` AS pa
ON (pa.`products_id` = p.`products_id`
AND pa.`options_values_id` = 91)
WHERE pa.products_id IS NULL;

This assumes that options_values_id 91 is the attribute that is desired to be applied to all product associated with the category.

Thus in essence will have a full table of all of the active product that are associated with the category and a partial portion of the table that has the options_values_id desired with that table trimmed down to include just the above product that do *not* have the desired options_values_id...

[wolfderby]

Though, on second thought (because this was bugging me), what likely is desired is to know all product in the products table that are associated with category 510 where the product do not have an attribute associated with options_values_id of 91, this can be obtained by identifying all product in the category, then all product that have the attribute of concern and return the product that do not have that attribute. So something like:

SELECT
p.`products_id`, p.`products_status`, p2c.*
FROM
`products` AS p
INNER JOIN
`products_to_categories` AS p2c
ON (p.`products_id` = p2c.`products_id`
AND p.`products_status` = 1
AND p2c.`categories_id` = 510)
Left JOIN
`products_attributes` AS pa
ON (pa.`products_id` = p.`products_id`
AND pa.`options_values_id` = 91)
WHERE pa.products_id IS NULL;

This assumes that options_values_id 91 is the attribute that is desired to be applied to all product associated with the category.

Thus in essence will have a full table of all of the active product that are associated with the category and a partial portion of the table that has the options_values_id desired with that table trimmed down to include just the above product that do *not* have the desired options_values_id...

you are amazing! ...biggest question though... HOW?! did you come up with this?

[wolfderby]

any feedback is welcome:

https://github.com/wolfderby/attributes_auto