Skip to content

Sql Builder: Correctly handle multiselect value with negative conditions #32124

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
wants to merge 2 commits into
base: 2.4-develop
Choose a base branch
from
Open

Sql Builder: Correctly handle multiselect value with negative conditions #32124

wants to merge 2 commits into from

Conversation

Gymnasiast
Copy link

@Gymnasiast Gymnasiast commented Feb 11, 2021 •
edited
Loading

Description (*)

This fixes building SQL for a multiselect attribute with a !() or !{} condition. While () and {} conditions correctly use FIND_IN_SET() (see the code immediately preceding this diff), no code was provided to handle !() and !{} before.

Differences in generated SQL (with property 193 ("promoties") being a multiselect):

Before:

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `at_turnover_group`.`value` AS `turnover_group`, `at_promoties`.`value` AS `promoties` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_turnover_group` ON (`at_turnover_group`.`entity_id` = `e`.`entity_id`) AND (`at_turnover_group`.`attribute_id` = '182') AND (`at_turnover_group`.`store_id` = 0)
 LEFT JOIN `catalog_product_entity_varchar` AS `at_promoties` ON (`at_promoties`.`entity_id` = `e`.`entity_id`) AND (`at_promoties`.`attribute_id` = '193') AND (`at_promoties`.`store_id` = 0) WHERE ((((IFNULL(`at_turnover_group`.`value`, 0) = '5688') AND(IFNULL(`at_promoties`.`value`, 0) NOT IN ('6055')) ) ));

After:

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `at_turnover_group`.`value` AS `turnover_group`, `at_promoties`.`value` AS `promoties` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_turnover_group` ON (`at_turnover_group`.`entity_id` = `e`.`entity_id`) AND (`at_turnover_group`.`attribute_id` = '182') AND (`at_turnover_group`.`store_id` = 0)
 LEFT JOIN `catalog_product_entity_varchar` AS `at_promoties` ON (`at_promoties`.`entity_id` = `e`.`entity_id`) AND (`at_promoties`.`attribute_id` = '193') AND (`at_promoties`.`store_id` = 0) WHERE ((((IFNULL(`at_turnover_group`.`value`, 0) = '5476') AND(IFNULL(`at_promoties`.`value`, 0) NOT IN ('6055') AND (IFNULL(FIND_IN_SET ('6055', `at_promoties`.`value`), 0) = 0)) ) ));

Related Pull Requests

Fixed Issues (if relevant)

The issue does not appear to be reported to Magento's bug tracker.

Manual testing scenarios (*)

  1. Create !() or !{} condition that binds to a multiselect value that has multiple properties saved (in a comma separated list)
  2. Observe that it doesn't work correctly on the develop branch (e.g. it leaves out values of 6055, but not 6055,6155), but works correctly on this branch.

Questions or comments

Contribution checklist (*)

  • Pull request has a meaningful description of its purpose
  • All commits are accompanied by meaningful commit messages
  • All new or changed code is covered with unit/integration tests (if applicable)
  • All automated tests passed successfully (all builds are green)

Resolved issues:

  1. resolves [Issue] Sql Builder: Correctly handle multiselect value with negative conditions #33036: Sql Builder: Correctly handle multiselect value with negative conditions

@m2-assistant
Copy link

m2-assistant bot commented Feb 11, 2021

Hi @Gymnasiast. Thank you for your contribution
Here are some useful tips how you can test your changes using Magento test environment.
Add the comment under your pull request to deploy test or vanilla Magento instance:

  • @magento give me test instance - deploy test instance based on PR changes
  • @magento give me 2.4-develop instance - deploy vanilla Magento instance

❗ Automated tests can be triggered manually with an appropriate comment:

  • @magento run all tests - run or re-run all required tests against the PR changes
  • @magento run <test-build(s)> - run or re-run specific test build(s)
    For example: @magento run Unit Tests

<test-build(s)> is a comma-separated list of build names. Allowed build names are:

  1. Database Compare
  2. Functional Tests CE
  3. Functional Tests EE,
  4. Functional Tests B2B
  5. Integration Tests
  6. Magento Health Index
  7. Sample Data Tests CE
  8. Sample Data Tests EE
  9. Sample Data Tests B2B
  10. Static Tests
  11. Unit Tests
  12. WebAPI Tests
  13. Semantic Version Checker

You can find more information about the builds here

ℹ️ Please run only needed test builds instead of all when developing. Please run all test builds before sending your PR for review.

For more details, please, review the Magento Contributor Guide documentation.

⚠️ According to the Magento Contribution requirements, all Pull Requests must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

🕙 You can find the schedule on the Magento Community Calendar page.

📞 The triage of Pull Requests happens in the queue order. If you want to speed up the delivery of your contribution, please join the Community Contributions Triage session to discuss the appropriate ticket.

🎥 You can find the recording of the previous Community Contributions Triage on the Magento Youtube Channel

✏️ Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

@Gymnasiast
Copy link
Author

@magento give me test instance

@magento-deployment-service
Copy link

Hi @Gymnasiast. Thank you for your request. I'm working on Magento instance for you.

@magento-deployment-service
Copy link

Hi @Gymnasiast, here is your Magento Instance: https://a9264169a932d231bf9b1fa2d490a331.instances.magento-community.engineering
Admin access: https://a9264169a932d231bf9b1fa2d490a331.instances.magento-community.engineering/admin_ab70
Login: d2468bf7
Password: 306d3eb5d3b9

@Gymnasiast
Copy link
Author

@magento run all tests

@gabrieldagama gabrieldagama added the Priority: P3 May be fixed according to the position in the backlog. label May 18, 2021
@gabrieldagama
Copy link
Contributor

@magento create issue

@m2-assistant m2-assistant bot mentioned this pull request May 18, 2021
Open
4 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
Component: Rule Priority: P3 May be fixed according to the position in the backlog. Progress: pending review Release Line: 2.4
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

[Issue] Sql Builder: Correctly handle multiselect value with negative conditions
4 participants
@Gymnasiast @gabrieldagama @magento-engcom-team @engcom-Hotel