How To Count The Number Of Non Empty MIGXDB Fields?

I am using MIGXDB to populate the MySql and loop collection to display data in a table, this part is successful. However I want to something a little different and I cannot find a solution:

I have 2 questions

1/ Is there a way to produce a value in the front end that displays whether there is any data in the fields of a MySql column?
#Note# I do not want to display the actual data from each field, I want to count the number of fields with data in and display that as a number excluding the fields with blank data.

For example: If a column has 20 rows and 15 have actual data with 5 blank then it should display front end 15

2/ How to display difference between the number of entries in one column compared to another (simple subtraction)?

For example: if column A has 15 fields with actual data and column B has 5 fields with actual data then it should display the sum difference = 10

MySql info:

packageName=`tfsabah`
classname=`Tfsabah`
selectfields=`Asha_Region,Asha_Loc_id,Asha_Site_Name`

Any advice, guidance or code examples would be greatly appreciated

I found and modified the following snippet (for 1 column only) but it is just returning a result 0 (zero)

<?php
$c = $modx->newQuery('Tfsabah');
$c->select('sum(Asha_Region) as cnt');
if ($c->prepare() && $c->stmt->execute()) {
echo $c->stmt->fetchColumn();
}

From reading the Modx docs I think the best way to do this would be to use xPdo getCount, however I cannot find any examples of code to replicate what I am trying to do.

Still looking for the solution …

I replaced the ‘select’ line with a ‘getCount’ line as shown below:

<?php
$c = $modx->newQuery('Tfsabah');
$count = $modx->getCount('Asha_Region', $c);
if ($c->prepare() && $c->stmt->execute()) {
echo $c->stmt->fetchColumn();
}

However, this is returning a count of 1 whereas there are 4 fields with data in the column ‘Asha_Region’ so it is still not returning the correct result.

class = Tfsabah
table = tfsabah
field key = Asha_Region

Could it be the above code is counting the columns with the name Asha_Region as opposed to counting the contents within the column?
If yes that might explain why I am getting the result 1 and not 4

Still searching for the solution, when/if I find it I will post it on here

I am getting closer, I changed the code in the chunk and now I can get a row count (see below code). However there is still a number of issues:

  1. Currently there is only 5 rows of data, but the field I have chosen in this code only has data in one row but it is returning a row count and not a count of the non empty fields.
  2. It is also repeating the output five times for each row
  3. If I add another field it makes no difference to the output, it just outputs the number of rows:-(

I added the following filter thinking it would filter out the empty fields but it does not work:

&where=`{"published":"1"}`

Here is the current code I am using

[[!migxLoopCollection?
&packageName=`tfsabah`
&classname=`Tfsabah`
&selectfields=`id,Asha_Region,`
&where=`{"published":"1"}`
&tpl=`mytotal`
]]

The Tpl

<ul><li>
[[+total]]
</li></ul>

Tried using ‘totalVar’ also but I cannot get that working either

Still working on this …

Hi this case has been interesting, I did a little searching.

What I think you want to do is use output modifiers to the MIGX output, like if…then…else

I found this bug report that may not be a bug.

Bruno provides a workaround and says this, but i am not sure if this will apply go line by line

there is also an &emptyTpl - property, which is used, if no results. Can be a chunk or @code: can be used

This also might be useful

This says with the right tpl you can apply conditionals

Hi nuan88,

Thank you for taking the time to try to find a solution, it is appreciated :slight_smile:

I have looked at both the links you provided but unfortunately I am still scratching my head wondering how I can implement a solution.

I do not think what I need is complicated, it is just a ‘count’ of non empty fields, however I have tried so many different ways of using ‘getImageList’ and I still cannot get the correct output.

I think a snippet of code could produce what I want, but unfortunately everything I have tried so far has not worked.

Off Topic

Actually MIGXDB is awesome as it is, but I can think of many ‘use cases’ where it could do so much more for me if I had the coding knowledge …

For example: My wish list would be some tutorials with coding examples replicating Excel maths (add, subtract, multiply, average, percentage …) or anything that could be done on Excel could be saved to a database and rendered on the front end using code to get the same outcome.

I’ve gone off topic a bit here but just wanted to share my thoughts

could you show your xpdo-schema
some example rows
and the wanted example outcome

1 Like

Below is part of the schema (i cut most out because it is too long)

        <?xml version="1.0" encoding="UTF-8"?>
<model package="tfsabah" baseClass="xPDOObject" platform="mysql" 
defaultEngine="MyISAM" version="1.1">
<object class="Tfsabah" table="tfsabah" extends="xPDOSimpleObject">
  	<field key="Asha_Region" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Asha_Loc_id" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Asha_Site_Name" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_PO_Num" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Asha_Project_Name" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_Project_Sow" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_Cme_Dependency" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_Ti_Summary" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
	<field key="FT_Admin_Ptw_Type" dbtype="varchar" precision="255" phptype="string" null="false" default="0"/>
    <field key="FT_Admin_Ptw_Applied" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>		
	<field key="FT_Admin_Ptw_Approved" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_Plan_Week" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_Actual_Week" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Ti_Team" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Ti_Start" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Ti_Complete" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Site_Integrated" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Ft_Admin_Photos" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Ft_Admin_Srs" dbtype="varchar" precision="255" phptype="string" 
null="false" default="0"/>
	<field key="Ft_Admin_Rbs_Log" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Ft_Admin_Uploaded_Date" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Ft_Admin_Decomm_Checklist" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<field key="Asha_Site_Keys_Checklist" dbtype="varchar" precision="255" 
phptype="string" null="false" default="0"/>
	<aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" 
cardinality="one" owner="foreign"/>
    <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" 
cardinality="one" owner="foreign"/>
</object>
</model>
1 Like

The outcome:

Count the number of fields that has data. (not counting empty or null fields)

If the CMP has been updated 10 times but any given field, say “Asha_Region” only has 7 entries then it should count the 7 entries and display 7 on the front end.

When the CMP is updated then and data is added to “Asha_Region” then that count should be reflected in the front end.

The front end can display in a table or anything, using a Tpl.
It’s just how to count that number

using migxLoopCollection, you could do it this way:

create a chunk, called countField with

[[migxLoopCollection?
&packageName=`tfsabah`
&classname=`Tfsabah`
&selectfields=`id`
&where=`{"[[+field]]:!=":""}`
&tpl=`@CODE:`
&wrapperTpl=`@CODE:{{+total}}`
&toPlaceholder=`cnt_[[+field]]`
]]

and use it like that:

[[$countField? &field=`Asha_Region`]]
[[$countField? &field=`Site_Integrated`]]
[[$countField? &field=`Asha_Rsa_Actual`]]
[[$countField? &field=`Sft_Sdd_Approved_Celcom`]]
[[$countField? &field=`Services_Invoice_Sent`]]


Asha_Region:[[+cnt_Asha_Region]]<br>
Site_Integrated:[[+cnt_Site_Integrated]]<br>
Asha_Rsa_Actual:[[+cnt_Asha_Rsa_Actual]]<br>
Sft_Sdd_Approved_Celcom:[[+cnt_Sft_Sdd_Approved_Celcom]]<br>
Services_Invoice_Sent:[[+cnt_Services_Invoice_Sent]]<br>
1 Like

Hi Bruno,

Yes, that works perfectly :smile:
I can put that outcome into a simple HTML table making it easy for presentation.

Thank you for your solution, it is appreciated

1 Like

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.