Count LISE Items Topic is solved

[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?

[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

[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.

[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;

[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.

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