Problems, need help? Have a tip or advice? Post it here.
12 posts Page 1 of 2
Hello

I have a lot of code where I generate a list pages of a clonable template where I have to count the number of pages which are related to the other template:
Code: Select all
<table>
   <thead>
      <tr>
         <th>Customer</th>
         <th># Ordered</th>
         <th># Shipped</th>
         <th># Returned</th>
      </tr>
   </thead>
   <tbody>
   <cms:pages masterpage='customers.php' custom_field='type==premium'>
      <tr>
         <td>
            <cms:show name />
         </td>
         <td>
            <cms:reverse_related_pages 'customer' masterpage='orders.php' custom_field='status==ordered' count_only='1' />
         </td>
         <td>
            <cms:reverse_related_pages 'customer' masterpage='orders.php' custom_field='status==shipped' count_only='1' />
         </td>
         <td>
            <cms:reverse_related_pages 'customer' masterpage='orders.php' custom_field='status==returned' count_only='1' />
         </td>
      </tr>
   </cms:pages>
</table>


The problem is, that in my app I often have some hundreds of customers. The above code gets extremely slow.

I have already speed up the basic list using a sql query (from 70s to 0.7s):
Code: Select all
<cms:capture into='sql_query'>
   SELECT
      p.id as k_page_id,
      p.template_id,
      type.value as type,
      name.value as name
   FROM
      couch_pages p
      inner join couch_data_text type on type.page_id = p.id
      inner join couch_data_text name on name.page_id = p.id
   WHERE
      p.template_id='<cms:trim "<cms:call 'template_id' masterpage='customers.php' />" />'
      AND p.parent_id=0
      AND (
         type.field_id='<cms:trim "<cms:call 'field_id' masterpage='customers.php' name='type' />" />'
         AND name.field_id='<cms:trim "<cms:call 'field_id' masterpage='customers.php' name='name' />" />'
         )
         AND ( type.search_value = 'premium' )
   ORDER BY name.search_value asc
</cms:capture>


The functions 'field_id' and 'template_id' are helpers to ghet the appropriate ids.

So my problem is the following: How can I integrate the above sums into one sql query? I stuck up because the lists in CouchCMS are flat.

Anyone able to solve this?

Regards,
Olliwalli
Nice code :) Your sql 'merge request' can be solved by connecting an extra table 'couch_relations'. Basically, I see it as a query that outputs customers with orders grouped and combined like this -
premium name | ordered | shipped | returned
Trendoman | 22 | 20 | 0

If you ask me I could probably build it for you, otherwise maybe someone else would like to 'sharpen their teeth' here.
trendoman thanks, yes I would be happy if you could solve it.

For the functions: Thanks for the code for trim_all_spaces, that will be cleaner.

I build my own functions:
Code: Select all
<cms:hide>
   <!--
      Return id of a masterpage

      <cms:call 'template_id' masterpage='users/index.php'/>
   -->

   <cms:func 'template_id' masterpage='' >

      <cms:if masterpage=''><cms:set masterpage = k_template_id /></cms:if>
      <cms:capture into='sql'>
         SELECT id
         FROM <cms:php>echo K_TBL_TEMPLATES;</cms:php>
         WHERE name = "<cms:addslashes><cms:show masterpage /></cms:addslashes>"
      </cms:capture>
      <cms:query sql=sql limit='1'><cms:show id /></cms:query>

   </cms:func>
</cms:hide>



<cms:hide>
   <!--
      Return id of a field of a masterpage

      <cms:call 'field_id' masterpage='users/index.php'/>
   -->

   <cms:func 'field_id' masterpage='' name='' >

      <cms:if masterpage=''><cms:set masterpage = k_template_id /></cms:if>
      <cms:capture into='sql'>
         SELECT id
         FROM <cms:php>echo K_TBL_TEMPLATES;</cms:php>
         WHERE name = "<cms:addslashes><cms:show masterpage /></cms:addslashes>"
      </cms:capture>
      <cms:query sql=sql limit='1'><cms:set template_id=id scope='global' /></cms:query>

      <cms:capture into='sql'>
         SELECT id
         FROM <cms:php>echo K_TBL_FIELDS;</cms:php>
         WHERE
            template_id = "<cms:addslashes><cms:show template_id /></cms:addslashes>" AND
            name = "<cms:addslashes><cms:show name /></cms:addslashes>"
      </cms:capture>
      <cms:query sql=sql limit='1'><cms:show id /></cms:query>

   </cms:func>
</cms:hide>


I have played around a little bit using
Code: Select all
<cms:pages masterpage='customers.php' aggregate_by='orders.php::customer.php'' custom_field='status==ordered' skip_custom_fields='1' orderby='name' order='asc' return_sql='1'>


This code worked basically and was very fast. But I was not able to extend it to return multiple rel_count values for different search params.

The code working for ONE rel_count field is (this is with the real world templates from my application, so do not be irritated):
Code: Select all
SELECT 
   p.id as k_page_id,
   p.template_id,
   name.value as name,
   status.value as status,
   typ.value as typ,
   count(p.id) as leads_count_total
FROM
   couch_pages p
   inner join couch_relations rel1 on rel1.cid = p.id
   inner join couch_data_text typ on typ.page_id = p.id
   inner join couch_data_text status on status.page_id = p.id
   inner join couch_data_text name on name.page_id = p.id
WHERE
   p.template_id='<cms:trim "<cms:call 'template_id' masterpage='app/data/anbieter.php' />" />'
   AND p.parent_id=0
   AND rel1.fid='<cms:trim "<cms:call 'field_id' masterpage='app/data/leads.php' name='anbieter_relation' />" />'
   AND (
      typ.field_id='<cms:trim "<cms:call 'field_id' masterpage='app/data/anbieter.php' name='typ' />" />'
      AND status.field_id='<cms:trim "<cms:call 'field_id' masterpage='app/data/anbieter.php' name='status' />" />'
      AND name.field_id='<cms:trim "<cms:call 'field_id' masterpage='app/data/anbieter.php' name='name' />" />'
      )
   AND (
      typ.search_value = 'Direkt'
      AND status.search_value = '5 Aktiv'
      )
GROUP BY
   p.id
ORDER BY name.search_value asc
I'll be happy to help you with that query and others if needed.

Could you also provide relevant definitions from both templates? In order to fully test your existing posted queries and build the new one(s) I must see everything in action. I will create dummy data with your defs. Also post if you have any changes made to the stock database (Indexes, changed cell types).

I can not guarantee at the moment that everything will be built within a single query (I hope so, though) and maybe subqueries will be required. It is often the best to have big data at hands, however I understand that it may not be possible since it's your customer's data and probably should be kept secret.

Nevertheless, I must say in my experience a slight change in SQL, choosing the right fields or a new custom Index which covers query's scope may produce an important speed improvement. So if you care about it that much, at least stuff me with some approx numbers of your cloned pages and maybe a sample dataset. I will code a script to recreate a similar amount of pages and it will help to build the fastest possible query.

I will PM you now with the rest details.
@olliwalli, take a look at your cms:func 'field_id'. I will show you how to optimize it via 2 tricks.

1. Remove the duplicate code that gathers 'template_id' from inside 'field_id', because you have such function already and can reuse via <cms:call 'template_id' />.

2. Cache results of queries via a global variable. Output variable's value instead of querying database again and again for the same values!

Another tip (applicable to other functions) for caching - you can use cms:md5 tag to convert bad strings (template names) into good strings -
Code: Select all
<cms:set template_id = "<cms:get "template_id_<cms:md5 masterpage />" />" />


In function 'field_id' presented above, there is no need to md5 the name, because field's name is always a 'good string'. But for template, it's very handy!
@trendoman thanks for the tip. I implemented it - it saves me between 30 to 120 sql requests. Nice! Also the code for trimming dynamic functions output 'trim_spaces_call' is great!

I managed to achieve some nice things:
1. A dynamic function that fetches only selected fields using cutsom filters, some sort of cms:pages with a param like fetch_fields='field1|field2|...' (see params dscription in the head of the function):

Code: Select all
<cms:func 'pages' params='' ><cms:ignore>

   // Return selected fields of a clonable template
   // <cms:call 'pages' params=params />
   // params should be defined in the following form: 
   <cms:capture into='params' is_json='1'>
   {
      "masterpage": "my_template.php",
      "fields": "first_name|last_name|address|zip|city|phone",
      "filters": {
         "type": {
            "=": "standard|premium"
         },
         "name": {
            "like": "A%"
         }
         "country": {
            "!=": "DE"
         }
      },
      "order": {
         "last_name": "asc",
         "first_name": "asc"
      }
   }
   </cms:capture>

   </cms:ignore>
      SELECT
         <cms:each params.fields as='field'>
         <cms:show field />.value as <cms:show field />,
         </cms:each>
         p.id as k_page_id
      FROM
         couch_pages p
         <cms:set fields="<cms:concat params.fields '|' "<cms:each params.filters><cms:show key />|</cms:each>" />" />
         <cms:set fields="<cms:php>echo implode('|', array_filter(array_unique(explode('|', '<cms:show fields />'))));</cms:php>" />
         <cms:each fields as='field'>
left join couch_data_text <cms:show field /> on <cms:show field />.page_id = p.id
         </cms:each>
      WHERE
         p.template_id='<cms:call 'template_id' masterpage="<cms:show params.masterpage />" />'
         AND p.parent_id=0
   <cms:each params.fields as='field'>
         AND <cms:show field />.field_id='<cms:call 'field_id' masterpage="<cms:show params.masterpage />" name="<cms:show field />" />'
   </cms:each>
   <cms:each params.filters as='filter'><cms:set field=key /> <cms:each filter as='values' >
         AND ( <cms:set operator=key /><cms:each values as='value' ><cms:if k_count!='0'> OR </cms:if><cms:show field />.search_value <cms:show operator /> '<cms:show value />'</cms:each> )
   </cms:each>
   </cms:each>
      GROUP BY
         p.id
   <cms:if "<cms:show params.order as_json='1' />">ORDER BY </cms:if><cms:each params.order><cms:if k_count!='0'>, </cms:if><cms:show key />.search_value <cms:show item />
   </cms:each>
</cms:func>

2. A sql query to calculate sums or count elements from a (reverse_)related template:
Code: Select all

<cms:capture into='sql_query'>
   SELECT
      COALESCE(orders.count, 0) as orders_count,
      FORMAT(COALESCE(orders.price, 0), 2, 'de_CH') as orders_sum_price,

      p.id as k_page_id,
      first_name.value as first_name,
      last_name.value as last_name
      type.value as type
   FROM
      couch_pages p
      left join couch_data_text first_name on first_name.page_id = p.id
      left join couch_data_text last_name on last_name.page_id = p.id
      left join couch_data_text type on type.page_id = p.id
      left join (
         SELECT
            sum(price.value) as price,
            count(relation.cid) as count,
            relation.cid as related_page_id
         FROM
            couch_pages related_page
            left join couch_relations relation on relation.pid = related_page.id
            left join couch_data_text price on price.page_id = related_page.id
            left join couch_data_text status on status.page_id = related_page.id
         WHERE
            related_page.template_id='<cms:call 'template_id' masterpage='orders.php' />'
            AND related_page.parent_id=0
            AND (
               relation.fid='<cms:call 'field_id' masterpage='orders.php' name='customer' />'
               AND price.field_id='<cms:call 'field_id' masterpage='orders.php' name='price' />'
               AND status.field_id='<cms:call 'field_id' masterpage='orders.php' name='status' />'
            )
            AND (
               status.search_value != 'canceled'
            )
         GROUP BY
            relation.cid
      ) orders on orders.related_page_id = p.id
   WHERE
      p.template_id='<cms:call 'template_id' masterpage='my_template.php' />'
      AND p.parent_id=0

      AND first_name.field_id='<cms:call 'field_id' masterpage='my_template.php' name='first_name' />'
      AND last_name.field_id='<cms:call 'field_id' masterpage='my_template.php' name='last_name' />'

      AND ( type.search_value = 'standard' OR type.search_value = 'premium' )
   GROUP BY
      p.id
   ORDER BY
      last_name.search_value asc, first_name.search_value asc
</cms:capture>

<cms:query sql=sql_query fetch_pages='0' limit=limit>
...
</cms:query>

But now if I want to fetch a lot of sums and counts from different fields the query gets to a monster query and gets slow again ...
If 'live' query is too expensive, perhaps you could consider saving all calculated values separately (updating them whenever the involved pages change)? Could that be an option?
@KK: I was already thinking about it ... but this would involve a lot of changes in every snippet that changes the templates. And it would involve a lot of testing in order to guarantee to have reliable counts. Probably a solution would be to calculate the sums for a specific item completely every time it is saved, changed or if a related item is changed or created. But the solution of calculating it live is easier ...
KK wrote: If 'live' query is too expensive, perhaps you could consider saving all calculated values separately (updating them whenever the involved pages change)? Could that be an option?

@KK, It would be a perfect way if only we knew how to push values into 'globals' upon a page save. Afaik, cms:persist within a config_form_view updates values of the current page only and the only simplest but cumbersome workaround would be -
Code: Select all
<cms:globals>
    <cms:editable name='qty_total' label='Total' type='text' />
</cms:globals>

<cms:editable name='qty' type='text' />

<cms:config_form_view>

    <cms:persist qty = "<cms:show frm_qty /><cms:db_persist .... />" />

</cms:config_form_view>
After I've read what @olliwalli said about updating snippets, I now see that the correct solution would be a different one (without cms:persist). If fields are changed from both backend and frontend (via cms:db_persist or live edit), then there must be a hook onto the changing the field.

Perhaps a custom validator function, similar to what you wrote for 'no_duplicate_mail' viewtopic.php?f=4&t=11513&p=30704#p30704?
12 posts Page 1 of 2