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:
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):
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
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