sql issue in split_page_results with custom query using UNION

hi,
i'm having an issue with split_page_results and i'm curious if anyone has come up with a workaround. this is NOT specifically a problem with ZC, but i'm trying to use some of the ZC tools.

specifically i have an SQL statement that makes use of the UNION operator.

when i throw it through the split_page_results function, it dies.

as an example, my sql statement is:

Code:

---

select * from orders where orders_id = 15362
UNION
select * from orders where orders_id = 15483

---

when i run it through splitPageResults, the resulting sql statement that dies is:

Code:

---

select count(*) as total from orders where orders_id = 15362 UNION select * from orders where orders_id = 15483

---

now, i do NOT want to come up with a whole new paginator function, which is why i am using the split_page.

if anyone has any ideas... i am listening.

thanks.

What ZC version are you using
and is this admin or catalog side code

wilt
it's v155 and on the admin side.

thanks.

As I imagine you've learned from reading the code, the split-page-results disassembles your SQL query to prepare a "count" query first, and then adds pagination-based LIMIT statements to retrieve the exact records your pagination parameters are dictating.
It has never had support for "custom" count logic.
And, since it only knows how to disassemble a basic SELECT query, and is not expecting to see multiple such queries joined with a UNION (or other) syntax, that's where it's falling down.

It would need expansion to support custom "count" capability, perhaps by having you pass a new added parameter containing a completely separate query that it would run to do its count operations without parsing it (other than perhaps adding some additional LIMITs to it for count purposes only).

Perhaps it depends also on what you're trying to accomplish with this query. What's the business problem you're trying to solve?

first, off, i appreciate the team taking a look at it...

at the end of the day, i am trying to accomplish a full outer join of 2 queries. which is impossible in mysql, hence my need for union.

over the years, i have customized some clients sites, probably a bit too much. on one clients site, management of inventory is crucial. product can be sold before it arrives; or after it arrives. it can be purchased from multiple vendors. product is only shipped during maybe 5-6 months of the year. so someone may buy something 1 month and then then not have it shipped for 6 months. in addition, i have put together the ability to ship items from multiple orders into 1 box. finally, there is a wholesale aspect of the business. i have put together a system for entering wholesale orders on the admin side. these wholesale orders have their own separate tables.

one of the pages i put together is a drill down onto a specific item. one can see who who bought the product, when they bought it, how much they paid for it, how many are shipped, and how many are owed. in addition, this drill down shows retail sales as well as wholesale sales. i have also provided a sorting capability on this page.

and there is the rub: combining the detail from the orders_products table with the wholesale orders_products table.

in the past, i have succeeded in doing this by creating a dummy record in each table to get my task accomplished. but it is clearly not ideal, as if those records get deleted or additional ones created, my query will show bad data.

in exploring this sql statement further, i have found that a UNION works perfect! it makes the SQL cleaner and easier to read; i am still able to sort based on order date coming from two different tables, etc. the existing query, as one might imagine, is quite complex, and therefore difficult to maintain.

so after figuring out the UNION query this morning, i tried putting into play... and then my problem with the split-page-results...

totally understand that it is outside of the scope of ZC, but i thought it would be worth a shot if someone had a nice/easy solution to it...

You may want to take a look at
[admin]/includes/classes/split_page_results_new.php
while this has different method signatures from the legacy split_page_results class it does allow for passing in a custom count sql query