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"
status="closed|completed"
start_on="{stash:time}" //Stashed time = today - 24 hours
orderby="event|products"
sort="asc|desc"} //query the orders channel
{stash:first_name} //stash the content
{stash:lastname}
{stash:nested_list}
{order_items} //Grab ordered variations stored in CartThrob matrix
{stash:qty}
{stash:color}
{stash:price}
{/order_items}
{/stash:nested}
{/exp:channel:entries}
UPDATE#1
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 ?