| Issue 90: | PATCH Order.search return wrong result when counting orders in some situations. | |
| 1 person starred this issue and may be notified of changes. | Back to list |
What steps will reproduce the problem? 1. Place an order with different shipping and billing addresses that have something in common in the first and last name (like a gift from John Doe to John Doe Jr.) 2. Use Order.search to see how many orders John Doe have. What is the expected output? What do you see instead? It should be one. It shows two. What version of the product are you using? On what operating system? Trunk. Ubuntu 7.10 Please provide any additional information below. Theres something wrong with the SQL that does the search. The order is joined directly with the addresses using the order_user_id, to not have duplicated results with different addresses that points to the same user, was added a distinct clause to select, but when counting the distinct clause is missing. Theres more than a way to fix that, I choosed the simpler. Just add a distinct clause instead of join two times with the addresses. ... sql = "SELECT COUNT(DISTINCT orders.id) " ... It was just "SELECT COUNT(*)" I deleted two WHERE clauses that I think doesn't make difference. ... sql << "OR order_addresses.first_name LIKE ? " sql << "OR order_addresses.last_name LIKE ? " sql << "OR CONCAT(order_addresses.first_name, ' ', order_addresses.last_name) LIKE ? " ... If the names are being concatenated in a rule and the logical operator is an "or" theres no need of the first two, as the third will be always evaluated and will match anything that was matched by the first two too. Follows a patch.
Jun 1, 2008
Project Member
#1
subim...@gmail.com
Jun 2, 2008
I didn't got rid of the where clause completely, both names together was left there (this is what CONCAT is meant for), but I didn't understood why do you want yet the FIRST or LAST name alone. Anyway, with or without it the result should be the same, I just think its not needed. Unless you give me an example that I didn't though about and it gives different results.
Jun 2, 2008
OK. I tried to make it give different results and found another problem. Creating two orders with the same e-mail address, when you search by any field of one order the other is returned too, even if it don't match the criteria (but it matches the order_user_id owner of the e-mail address). This will need to be redone. Both selects are wrong.
Jun 2, 2008
Here is the patch, I joined with the addresses table two times giving different names, this way the distinct clause is not needed. I still took out the where clauses that uses first or last name alone, only the clause with concat was left. |