Count results

hi,

I would want to count sessions for each providers

Please give me an example.

I'm tired to waspe my time on this component!!!
A full documentation for when????

Hi Christophe,

How to count sessions ? what are you trying to accomplish exactly ?

A full documentation can not cover every advanced trick anyway, I think that most of the basics are similar to Chronoforms or are covered in forums posts and FAQs, but I will try to add more in the near future!

Best regards

Hi Max,

I understand but we need more examples to understand how the component works...

I try to count sessions for a provider. In sessions table there is a providers_id...

Regards

Hi Christophe,

You can use the "Read data" function and in the conditions add a condition for the provider_id

This does not work ?

Best regards

I know that,

I did it with in Fields to retrieve in providers
COUNT(sessions.providers_id):sessions.count_sessions

And

in sessions
Multiple matching records, foreign key in this table
Group by : providers_id

Results :
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM

`#__admincrm_providers` AS `providers`' at line 1 SQL=SELECT FROM `#__admincrm_providers` AS `providers`;

Regards

Hi Christophe,

If you have the "Select type" set to "count" then no need to do anything except providing the where condition to specify the provider_id

Best regards

Max,

read_data
model : providers
where cond : providers.id:sessions.providers_id
type : return the count

model : sessions
related : providers
relation : multiple records, key in this table

show nothing

how to know the field to use in table ?

regards

Hi Christophe,

The count select type returns only one value which is the number of rows matching the conditions, there are no fields to check for.

And you can not have another model associated with multiple rows when you are returning the count.

Best regards

ok Max,
and how can i do to have count on multiple rows ?
Regards

Hi Christophe,

Apologizes for the late reply here!

To get multiple counts then you need to select "all matching" and have a count field added to the fields list and group by one field.

Best regards

Hi Max and happy new year,

In view, important the 0 to catch the counter
providers.provider_name:Provider
sessions.0.count(*):Nbr sessions

And if i want to order by sessions.0.count(*)
How can i do?

sessions.0.count(*) in sorting_link produces an error 1054
sessions.count(*) doesn't sort anything

And is it possible to filter on a sessions column like exam_date='2018' ?

Regards

Hi Christophe,

Happy new year and apologies for the late reply!

You can filter using a link, just pass a parameter with a value and catch it in the "Where" conditions, you can add /- to the end of the condition to omit it if the parameter is not passed.

I do't understand the session count order question, but each field you have has an alias which you can use for ordering if needed, you can add a debugger and check the fields in the query for the counter alias, can you return the count correctly now ?

Best regards

Hello Max,

I try to count sessions for each school. In first i started from school(providers) in first and sessions in second table. impossible to have the count.
I reversed the problem and start from session and defined schools for the dependant table.
Ok I have the count but not the school who have no enrollment.

Regards

Hi Christophe,
So you have 2 tables, 1 for schools and another for sessions, the sessions table has multiple records for each school ?
If yes then you can get the sessions count, in the secondary model, add a field to the "Fields to retrieve",

Model.sessionCount:COUNT(Model.id)

And use Model.schoold_id in the "Group" fields box, the names of the fields "id" and "schoold_id" depends on the table's primary and foreign keys
Best regards

Hello Max,
Model :

First
providers
Second
Models : sessions


Regards

Hi Christoph,
​
Any fields with functions must be in this format:

COUNT(Model2.field):Model1.field_count

Best regards

Hi Max,
​
Sorry i don't understand what you mean.

Could you show an exemple ?

Regards

Hi Christoph,
​
The "Fields to retrieve" under the "Sessions" model need to be updated to have the alias as shown in the example.
​
Best regards

in sessions fields to retreive
COUNT(sessions.id):providers.field_count
​
in view column list
providers.field_count:Count

And ? now output in the table column ?

in view column list
providers.field_count:Count
​
Nothing appears?

Hi Christoph,
​
The debug output of the read data should help here, could you please copy it and paste here ?
​
Best regards

Hi Max
​
Strange the count is in but...
providers.field_count:Count in columns list show nothing...
​

[var] => Array
                (
                    [0] => Array
                        (
                            [providers] => Array
                                (
                                    [id] => 63
                                    [contacts_id] => 49
                                    [provider_name] => A Grape Affair NH
                                    [provider_add1] => 38 Pier #7
Constellation Wharf
                                    [provider_add2] => 
                                    [provider_zip] => 02129
                                    [provider_town] => Charlestown
                                    [provider_state] => NH
                                    [provider_country] => USA
                                    [provider_tel] => 603.494.6763
                                    [provider_alias] => A-GRAPE-AFFAIR-NH
                                    [old_partner] => 0
                                )

                        )

                    [1] => Array
                        (
                            [providers] => Array
                                (
                                    [id] => 18
                                    [contacts_id] => 47
                                    [provider_name] => Art Institute of Vancouver - Edmonton AB
                                    [provider_add1] => 300-609 Granville Street
                                    [provider_add2] => 
                                    [provider_zip] =>  V7Y 1G5
                                    [provider_town] => Vancouver
                                    [provider_state] => AB
                                    [provider_country] => CAN
                                    [provider_tel] => (778) 373-9022
                                    [provider_alias] => AI-VANCOUVER-EDMONTON-AB
                                    [old_partner] => 0
                                    [0] => Array
                                        (
                                            [field_count] => 2
                                        )

                                )

                            [sessions] => Array
                                (
                                    [0] => Array
                                        (
                                            [providers_id] => 18
                                            [MAX(session_date_start)] => 2014-02-01
                                            [MIN(session_date_start)] => 2010-06-01
                                        )

                                )

                        )

...etc
​
Thanks
Regards

Hi Christoph,
​
Something is wrong, what do you have in the "Fields to retrieve" in both models now ?
​
Best regards

Hi Max,
Thanks
​
Nothing in the providers
And in sessions :
COUNT(sessions.id):providers.field_count
providers_id
MAX(session_date_start)
MIN(session_date_start)
​
Regards

Hi Christoph,
​
Ok, I recommend changing this to:

COUNT(sessions.id):sessions.field_count
providers_id
MAX(session_date_start):sessions.maxstart
MIN(session_date_start):sessions.minstart

In order to use the value of in the table, you will need to use this in the columns list:

sessions.0.maxstart

Best regards

Hello Max,
​
Thanks for your help but i don't undestand, now after change i have this error
​
FUNCTION ws_joomla.MAX does not exist SQL=SELECT
COUNT(`sessions`.`id`) AS `sessions.field_count`,
MAX(`sessions`.`session_date_start`) AS `sessions.maxstart`,
MIN(`sessions`.`session_date_start`) AS `sessions.minstart` FROM
`#__admincrm_sessions` AS `sessions` WHERE `sessions`.`providers_id` IN
('63', '18', '17', '10', '11', '5', '3', '55', '19', '64', '20', '85',
'16', '34', '61', '71', '32', '81', '65', '24', '27', '26', '25', '15',
'23', '38', '67', '68', '47', '46', '53', '54', '83', '28', '48', '58',
'84', '49', '22', '40', '39', '77', '78', '76', '73', '79', '75', '74',
'80', '50', '35', '43', '51', '33', '8', '13', '45', '82', '36', '52',
'62', '37', '30', '59', '86', '88', '21', '72', '41', '60', '66', '87',
'70', '31', '69', '44', '7') GROUP BY `sessions.providers_id`;
​
it's crazy !!!
​
Regards
Chris

Hello,
Now i have :[h1]1305[/h1][h2]FUNCTION ws_joomla.MAX does not exist [/h2]
fields to retreive is :
COUNT(sessions.id):sessions.field_count
MAX(sessions.session_date_start):sessions.maxstart
MIN(sessions.session_date_start):sessions.minstart
​
regards

Try putting a space between MAX and the first bracket (

Just had a look at my usage and I have mine as

MAX(timesheet.date):dates.max

as in without spaces so all I can suggest is removing the group_by

Thanks healy
but if remove the group by i'll don't have what i want
best regards

Sounds like you need two actions

You mean one action to retreive sessions grouped and one action to calculate count, max and min ?

Yep. Worth a shot

Ok i'm trying another kind
​
A function get_stats() wich returns an array
​[pre]$db = JFactory::getDBO();
$query = $db->getQuery(true);[br]$query = "SELECT providers_id,count(*) as n,max(`session_date_start`) as maxi,min(`session_date_start`) as mini FROM `#__admincrm_sessions` GROUP BY `providers_id` ORDER BY maxi desc";[br]$db->setQuery($query);[br]$db->execute();[br]$data = $db->loadAssocList();[br]return $data;[/pre]
A table with data provider : {fn:get_stats}
​
I don't no how to use it in column list
​
get_stats.returned.providers_id:ID[br]or
get_stats.providers_id:ID
or
get_stats.returned.0.providers_id:ID
????[h2]Sessions Stats by providerIDNbr[/h2]

Array
(
    [Itemid] => 2282
    [option] => com_chronoconnectivity6
    [view] => connection
)

Array
(
    [get_stats] => Array
        (
            [returned] => Array
                (
                    [0] => Array
                        (
                            [providers_id] => 34
                            [n] => 17
                            [maxi] => 2019-04-01
                            [mini] => 2016-09-27
                        )

                    [1] => Array
                        (
                            [providers_id] => 41
                            [n] => 13
                            [maxi] => 2019-02-12
                            [mini] => 2015-02-01
                        )

                    [2] => Array
                        (
                            [providers_id] => 89
                            [n] => 2
                            [maxi] => 2019-01-11
                            [mini] => 2018-09-28
                        )

...etc...
thanks
best regards

the way you have it set up it would be
​
{Var:gget_stats.[n]} or something
​
Should add the table alias in your query, as in
​
Select blahblah as model field

Sorry {fn:get_stats} is the only way to see something in debug.

Well yes you have to use {fn:} to call the action, and then its result are available under {var:}

Hello
That's not work..
Regards

Going to need more information.
​
need to know everything in your event
Need screenshot of your read data action

Exactly there isn't read_data
the function is
​
​

$db = JFactory::getDBO();$query = $db->getQuery(true);$query = "SELECT providers_id,count(*) as n,max(`session_date_start`) as maxi,min(`session_date_start`) as mini FROM `#__admincrm_sessions` GROUP BY `providers_id` ORDER BY maxi desc";$db->setQuery($query);$db->execute();$data = $db->loadAssocList();return $data;

I try to retreive in $form->data the result to use it in view table..
​
Regards

It won't be in form->data, it's not form data.
​
It'll be in $this->get("php_action_name", "default"); or in {var:phpactionname}
​
But why are you bothering with that PHP SQL query when you can just do it in a read data action ?

Hahaha
Read all from the beginning to understand...
Regards