I'm generating a large XML document daily which on busy days is falling over with memory issues and it's been suggested that instead of using exp:channel:entries I use exp:query tags.

So I think, GREAT, lets give it a whirl. Being an SQL n00b, has anyone any pointers on how to write a query that selects all entries in a particular date range, from a particular channel that contains a Carthtrob ordered_items matrix?

This is the structure ATM

{exp:channel:entries disable="categories|etc"
  start_on="{stash:time}" //Stashed time = today - 24 hours
  sort="asc|desc"} //query the orders channel
  {stash:first_name} //stash the content
    {order_items} //Grab ordered variations stored in CartThrob matrix


So I've done some homework, I now have some rudimentary data being sourced, the data is spread over several tables, *exp_channel_titles*, *exp_channel_data*, *exp_cartthrob_order_items* and the final one which is a biatch is the *exp_channel_fields*

For the sake of sanity I'd like to refer to the fields in use as their EE label not some field_id_XX

{exp:query sql="SELECT * from exp_channel_titles AS t
    JOIN exp_channel_data AS d
    ON d.entry_id = t.entry_id
    JOIN exp_cartthrob_order_items AS ct
    ON d.entry_id = ct.order_id
    WHERE d.channel_id = 4
    AND t.status = 'completed'
    ORDER BY d.entry_id DESC

Is this the right approach ? If so how can i use the default titles in the results ?

  • As a sidenote to the original problem you mentioned have you used the output profiler to try to identify the bottleneck or see how many queries the Channel Entries tags are generating? – Ian May 17 '13 at 8:26
  • There is no real bottleneck. There is a large chunk of snippets being loaded even though not requested. Memory usage on 50 orders hovers around 35MB, 458 queries. Biggest consumer of time is querying the cartthrob ordered items (nee Matrix). – stuartmcd69 May 17 '13 at 8:49

That would certainly get the job done...

To use your titles, you'd need to specify them, eg;

{exp:query sql="SELECT *, field_id_20 AS my_custom_field_name, field_id_21 AS my_other_custom_field_name 
                FROM exp_channel_titles t 
                INNER JOIN exp_channel_data d using(entry_id) 
                INNER JOIN exp_cartthrob_order_items ct using(entry_id)
                WHERE d.channel_id = '4' 
                AND t.status = 'completed'
                ORDER BY d.entry_id DESC "}

{entry_id} | {my_custom_field_name} | {my_other_custom_field_name}


Using * and changing to custom field names using AS, kinda doubles up on the data... but will still be far more efficient than channels / carthrob etc etc.

If you're going to manually root these names out of the CP anyway, you may as well just comment your XML code...

{field_id_20}{!-- Custom NAme --} 
{field_id_21}{!-- Other Name --} 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.