exp:query - how to

Question

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 ?

Answer 1

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}

{/exp:query}

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