Counting Records from Multiple Lists

[altnetwork]

I would like to display a total count from multiple lists.

I have three lists:

List A: 100 records
List B: 200 records
List C: 300 records

Each list has the same field(element) called yes/no.

I would like to get a total count of yes's from all three lists and display the total number of YES'S.

What would be the best way to do this?

Thanks

 

[troester]

Where do you want to display this - in a form (-> use a calc element), in a list view (-> custom template)...?
The code is nearly the same (return the count in a calc element, echo it in a custom template)
something like
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query
->select('count(*)')
->from('tablename1')
->where('fieldyesno1 = "1"'));

$db->setQuery($query);
$c1 = $db->loadResult();

$query = $db->getQuery(true);
$query
->select('count(*)')
->from('tablename2')
->where('fieldyesno2 = "1"'));
$db->setQuery($query);
$c2 = $db->loadResult();
...
return/echo $c1+$c2....;

 

[cheesegrits]

Just FYI rather than doing the getQuery(true) for each subsequent query, you can just chain a clear() in the next query, like $query->clear()->select(...)->from(...) etc.

Not a big deal, just a slightly quicker way of doing it.

-- hugh

 

[altnetwork]

Goal: I would like to post a total count on a post in Joomla. It will be from 60 Lists and 140,000+ posts, only one element (field). I would like to keep it simple and not to create a sql load.

This is what I am using now at the top of each List via template. This shows total 'yes' for each list.

<?php

$db = JFactory::getDBO();

$db->setQuery("SELECT COUNT(`verification`) FROM `list_1` WHERE `verification` = 'Yes'");

$number_of_Yes = $db->loadResult();

echo $number_of_Yes .' Have Been Verified';
?>

My goal is to add all 60 lists and display in a post.

I was thinking about a "Scheduled Task" to get the total using "Just FYI rather than doing the getQuery(true) for each subsequent query, you can just chain a clear() in the next query, like $query->clear()->select(...)->from(...) etc."

Would this work? Where would the total value be saved to display?

Thanks for looking at this. Not sure if this could be done without causing a large sql load with so many lists and records.

 

[cheesegrits]

My comment about using clear() was about clearing a query ready for re-use, it wouldn't help in reducing the number of queries.

The way I would probably do this would be with a MySQL view. Create a view in phpMyAdmin something like this:

CREATE VIEW verification_view AS
SELECT
   (SELECT COUNT(*) FROM list_1 WHERE verification = "Yes") AS list_1_tot,
   (SELECT COUNT(*) FROM list_2 WHERE verification = "Yes") AS list_2_tot,
   ...

... which will give you a view with one row, and 60 fields, with list_1_tot through list_60_tot.

You can then build a Fabrik list on that, and display it in any way you need. For instance, in an article you could use the Fabrik article plugin to display a total as {fabrik view=element list=X rowid=1 element=verification_view___list_1_tot}, etc.

-- hugh

 

[altnetwork]

I was able to do everything but the Fabrik List for the views. I am getting the following error when I try to view data.

An error has occurred.
500 Fabrik has generated an incorrect query for the list Verification Totals: <br /><br /><pre>Unknown column 'verification_view.' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT
`verification_view`.`list1_total` AS `verification_view___list2_total`, `verification_view`.`list1_total` AS `verification_view___list1_total_raw`, `verification_view`.`list2_total` AS `verification_view___list2_total`, `verification_view`.`list2_total` AS `verification_view___list2_total_raw`, `verification_view`.`list3_total` AS `verification_view___list3_total`, `verification_view`.`list3_total` AS `verification_view___list3_total_raw`, `verification_view`.`` AS slug , `verification_view`.`` AS `__pk_val` FROM `verification_view` LIMIT 0, 10</pre>

 

[cheesegrits]

Oh yeah, ooops, I forgot about the PK. As there's only ever going to be one row, you can just add "1 as id" on to the start of the query ...

[code]
CREATE VIEW verification_view AS
SELECT
   1 as id,
   (SELECT COUNT(*) FROM list_1 WHERE verification = "Yes") AS list_1_tot,
   (SELECT COUNT(*) FROM list_2 WHERE verification = "Yes") AS list_2_tot,
   ...

... then select 'id' as the PK in the List settings once you've created the list from the view.

You'll have to delete the view and recreate it in phpMyAdmin. Then either add the id element - Set "alter field types" to No in the list settings, then create an 'id' element for it - or you could just delete the List and recreate it from scratch. Creating new elements on a list based on a view can be a bit tricky, as we can't actually add the underlying field to the "table" like we normally do, as a view isn't really a table, it's just a stored query which creates a temporary table.

-- hugh

 

[altnetwork]

Getting the same error.

Not sure if I missed something. What is "... then select 'id' as the PK in the List settings once you've created the list from the view."?

 

[cheesegrits]

In the main List settings under Data, "Primary key".

Normally we automatically select that when you create a list on an existing table, by looking at the table structure and finding the field which is defined as the primary key in MySQL. But views don't have actual primary keys in the database sense, so you have to tell us which field to use.

-- hugh

 

[altnetwork]

Thanks, that worked. I am now going to try the fabrik plugin to show the total. Will let you know how that goes.

 

[altnetwork]

Ok, I was able to display one of the totals in a post. How would I display a sum of the totals?

?For instance, in an article you could use the Fabrik article plugin to display a total as {fabrik view=element list=X rowid=1 element=verification_view___list_1_tot}, etc."

 

[cheesegrits]

Hmmm. Unfortunately there's no built in, automatic way (in either Fabrik or MySQL) of sum'ing fields in a row, as opposed to columns in a selection of rows.

So this means adding another big SELECT to your view, which combines all the SUMS. So as the last field in the view query you'd need ...

(SELECT COUNT(*) FROM list_1 WHERE verification = "Yes") + (SELECT COUNT(*) FROM list_2 WHERE verification = "Yes") AS all_lists_tot

You have to repeat the (SELECT COUNT...) statements, as MySQL won't let you use the AS aliases in calculations, i.e. you can't do "list_1_tot + list_2_tot AS all_lists_tot", it'll just complain that there are no fields of those names.

Or you could do that as a second view, which just has the two fields - id and all_lists_tot.

The only other option is you could maybe try adding a dummy field to the end of the view, like "SELECT 'dummy' AS all_lists_tot", then in Fabrik designate that as a 'calc' element which does ...

return (int)'{verification_view___list_1_tot}' + (int)'{verification_view___list_2_tot}';

and set "Calc on save only" to No.

-- hugh

 

[altnetwork]

This is what I used and it worked:

"The only other option is you could maybe try adding a dummy field to the end of the view, like "SELECT 'dummy' AS all_lists_tot", then in Fabrik designate that as a 'calc' element which does ...

Code (Text):

return (int)'{verification_view___list_1_tot}' + (int)'{verification_view___list_2_tot}';

and set "Calc on save only" to No."

 

[altnetwork]

Is it possible to get "{fabrik view=element list=X rowid=1 element=verification_view___list_1_tot}" to work in a module (custom html)?

 

[troester]

Yes. Just put it in and "Prepare content"=yes (in Options).