Page 1 of 1
Count LISE Items
Posted: Wed Sep 11, 2024 2:37 pm
by webform
I have a LISE Instance with projects and another LISE instance for reports attached to each project.
On the LISE Project summary page i would like to display how many reports is attached to each project, and this summary template works but is very very slow:
Code: Select all
{* Module: LISEProjects Layout: listProjects *}
{if $items|@count > 0}
<div class="table-responsive overview">
<table class="table table-striped">
<thead>
<tr>
<th class="border-top-0">Project Number</th>
<th class="border-top-0">Project Name</th>
<th class="border-top-0">Weight Reports</th>
<th class="border-top-0"></th>
</tr>
</thead>
<tbody>
{foreach from=$items item=item}
<tr>
<td class="fw-bold"><a class="link-dark" href="{$item->url}">{$item->project_number}</a></td>
<td class="fw-bold"><a class="link-dark" href="{$item->url}">{$item->title}</a></td>
<td>{LISEWeightReports template_summary='total' xs_project=$item->item_id}</td>
<td class="text-center"><a href="{$item->url}" class="button button-mini button-circle button-blumine">View</a></td>
</tr>
{/foreach}
</tbody>
</table>
</div>
{/if}
Counting items in my LISE Reports instance i use a simple summary template:
Code: Select all
{* Module: LISEWeightReports Layout: total*}
{$totalcount}
I then thought testing an UDT quering the database directly if that would maybe speed things up. But i get no result even though the query gets a result if i run the select statement directly in the sql database:
Code: Select all
$id = isset($params['id']) ? $params['id'] : '14';
$db = cmsms()->GetDb();
$query = "SELECT * FROM " . cms_db_prefix() . "module_liseweightreports_fieldval WHERE fielddef_id = '7' AND value LIKE" . $id;
$result = $db->Execute($query);
$num_rows = mysqli_num_rows($result);
echo $num_rows;
What am i missing or doing wrong since the UDT gives no result? And is there another way to count and displays my reports in the project summary template?
Re: Count LISE Items
Posted: Thu Sep 12, 2024 7:19 am
by velden
I think you shouldn't be using a mysqli function on this result.
Check whether this works:
https://adodb.org/dokuwiki/doku.php?id= ... ecordcount
Re: Count LISE Items
Posted: Thu Sep 12, 2024 8:06 am
by webform
Thanks for the suggestion. Sadly it didn't work. Only resulting in Server Error 500.
Host only gives access to a limited error log with no details, so i can't read any details about the error
Code: Select all
$id = isset($params['id']) ? $params['id'] : '14';
$db = cmsms()->GetDb();
$result = $db->Execute("SELECT * FROM " . cms_db_prefix() . "module_liseweightreports_fieldval WHERE fielddef_id = '7' AND value LIKE" . $id);
$recordCount = $result->recordCount();
return $recordCount;
I did also try to set $ADODB_COUNTRECS = true but no change.
Re: Count LISE Items
Posted: Thu Sep 12, 2024 8:19 am
by webform
My bad! It was my select statement that was off
This is working:
Code: Select all
$id = isset($params['id']) ? $params['id'] : '14';
$db = cmsms()->GetDb();
$result = $db->Execute('SELECT * FROM cms_module_liseweightreports_fieldval WHERE fielddef_id = "7" AND value LIKE '.$id);
$recordCount = $result->recordCount();
echo $recordCount;
Re: Count LISE Items
Posted: Thu Sep 12, 2024 8:38 am
by webform
And a speed test with loading all 30 projects and counting 200 reports shows a significant speed increase between using {LISEWeightReports template_summary='total' xs_project=$item->item_id} and {count_reports id=$item->item_id}. Roughtly from 3-5 secs => miliseconds.
So now i can display how many reports each project has.
Re: Count LISE Items
Posted: Fri Sep 13, 2024 10:11 am
by webform
Just posting a slightly updatet UDT (named: count_reports):
Code: Select all
$id = isset($params['id']) ? $params['id'] : '';
$db = cmsms()->GetDb();
$result = $db->Execute('SELECT * FROM ' . cms_db_prefix() . 'module_liseweightreports_fieldval WHERE fielddef_id = "7" AND value LIKE '.$id);
$recordCount = $result->recordCount();
echo $recordCount;
Usage of the UDT in my LISE Summary Template:
Code: Select all
{* Module: LISEProjects Layout: listProjects *}
{if $items|@count > 0}
<div class="table-responsive">
<table class="table table-striped">
<thead>
<tr>
<th class="border-top-0">Project Number</th>
<th class="border-top-0">Project Name</th>
<th class="border-top-0">Weight Reports</th>
<th class="border-top-0"></th>
</tr>
</thead>
<tbody>
{foreach from=$items item=item}
<tr>
<td class="fw-bold"><a class="link-dark" href="{$item->url}">{$item->project_number}</a></td>
<td class="fw-bold"><a class="link-dark" href="{$item->url}">{$item->title}</a></td>
<td>{count_reports id=$item->item_id}</td>
<td class="text-center"><a href="{$item->url}" class="button button-mini button-circle button-blumine">View</a></td>
</tr>
{/foreach}
</tbody>
</table>
</div>
{/if}