Buy Now
Sign in

Count results

webcrea , November 30 2017
webcrea
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????
Christophe
http://www.webcrea.fr
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
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
Christophe
http://www.webcrea.fr
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
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
Christophe
http://www.webcrea.fr
admin
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
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
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
Christophe
http://www.webcrea.fr
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
ok Max,
and how can i do to have count on multiple rows ?
Regards
Christophe
http://www.webcrea.fr
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
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
Christophe
http://www.webcrea.fr
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
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
Christophe
http://www.webcrea.fr
admin

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

Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea

Hello Max,

Model : ​

First

providers

Second

Models : sessions

ecran1.JPG

ecran2.JPG

Regards

Attachments
ecran1.JPG
ecran1.JPG
(65.81 KiB)
217 Downloads/Views
ecran2.JPG
ecran2.JPG
(70.26 KiB)
316 Downloads/Views
ecran1.JPG
ecran1.JPG
(65.81 KiB)
320 Downloads/Views
Christophe
http://www.webcrea.fr
admin
Hi Christoph,
Any fields with functions must be in this format:
COUNT(Model2.field):Model1.field_count
Best regards
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
Hi Max,
Sorry i don't understand what you mean.​
Could you show an exemple ?​
Regards
Christophe
http://www.webcrea.fr
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
webcrea
in sessions fields to retreive
COUNT(sessions.id):providers.field_count
in view column list​
​providers.field_count:Count​
Christophe
http://www.webcrea.fr
admin
And ? now output in the table column ?
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!