Buy Now
Sign in

Count results

webcrea , November 30 2017, 09:36
webcrea 582
November 30 2017, 09:36 #373865
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 27
December 04 2017, 10:46 #373914
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 582
December 04 2017, 10:54 #373915
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 27
December 04 2017, 11:04 #373916
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 582
December 04 2017, 11:21 #373917
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 27
December 04 2017, 15:25 #373925
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 582
December 04 2017, 15:48 #373928
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 27
December 07 2017, 07:43 #373968
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 582
December 07 2017, 07:48 #373969
ok Max,
and how can i do to have count on multiple rows ?
Regards
Christophe
http://www.webcrea.fr
admin 27
December 29 2017, 11:42 #374290
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 582
January 02 2018, 09:25 #374340
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 27
January 15 2018, 18:42 #374602
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 582
January 22 2018, 09:32 #374720
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 27
January 30 2018, 21:35 #374882

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 582
January 31 2018, 07:44 #374891

Hello Max,

Model :

First

providers

Second

Models : sessions

ecran1.JPG

ecran2.JPG

Regards

Attachments
ecran1.JPG
ecran1.JPG
(65.81 KiB)
386 Downloads/Views
ecran2.JPG
ecran2.JPG
(70.26 KiB)
534 Downloads/Views
ecran1.JPG
ecran1.JPG
(65.81 KiB)
550 Downloads/Views
Christophe
http://www.webcrea.fr
admin 27
February 11 2018, 22:45 #375263
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 582
February 12 2018, 07:47 #375273
Hi Max,
Sorry i don't understand what you mean.
Could you show an exemple ?
Regards
Christophe
http://www.webcrea.fr
admin 27
February 12 2018, 09:12 #375279
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 582
February 12 2018, 09:21 #375283
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 27
February 12 2018, 18:11 #375308
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!