Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    1,402
    Plugin Contributions
    2

    Default 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Newcastle UK
    Posts
    2,879
    Blog Entries
    2
    Plugin Contributions
    2

    Default Re: sql issue in split_page_results with custom query using UNION

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

  3. #3
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    1,402
    Plugin Contributions
    2

    Default Re: sql issue in split_page_results with custom query using UNION

    wilt
    it's v155 and on the admin side.

    thanks.

  4. #4
    Join Date
    Jan 2004
    Posts
    65,291
    Blog Entries
    7
    Plugin Contributions
    227

    Default Re: sql issue in split_page_results with custom query using UNION

    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?
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    1,402
    Plugin Contributions
    2

    Default Re: sql issue in split_page_results with custom query using UNION

    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...

  6. #6
    Join Date
    Jun 2003
    Location
    Newcastle UK
    Posts
    2,879
    Blog Entries
    2
    Plugin Contributions
    2

    Default Re: sql issue in split_page_results with custom query using UNION

    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

 

 

Similar Threads

  1. v154 Help with a SQL Query for Query Builder
    By lindasdd in forum Managing Customers and Orders
    Replies: 2
    Last Post: 24 Mar 2016, 01:18 PM
  2. Replies: 4
    Last Post: 17 Oct 2013, 10:55 PM
  3. New page with custom form & sql query
    By nialler in forum Templates, Stylesheets, Page Layout
    Replies: 0
    Last Post: 13 Nov 2009, 02:14 PM
  4. issue running custom query
    By stride-r in forum General Questions
    Replies: 10
    Last Post: 13 Oct 2008, 02:37 AM
  5. Help writing a custom SQL query please
    By bodyjewelrystores in forum Templates, Stylesheets, Page Layout
    Replies: 3
    Last Post: 6 Feb 2008, 03:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR