Best setup

Thanks for the quick reply. I exactly dit both the proposed approaches. Where I ended with both:

1. Groups:
The problem I had is that the data entry is three times the work, plus, on each day there is also a driver joining, which I would like to register as well. Alternatively I would like to create an overview on each rider with their riding history, sorted by date, then shown by group / route / distance. This I couldn’t achieve with the [if] statements in pods templates.

So that I why I proceeded to point two.
2. Single event
Problem is that I want to show a total on a page, for all members by the quantity of routes they have ridden, but preferably also show the ride total.
I have done that by a custom SQL query, but I didn’t manage to combine this for all three routes in the end. So then I decided to ask a question here.

$params = array( 
	'groupby' => 't.ID',     
	'select' => 't.ID,
	t.post_title, 
	rides_groupA.ride_date.meta_value, 
	COUNT(rides_groupA.route_groupA.distance.meta_value) as count, 
	ROUND(SUM(rides_groupA.route_groupA.distance.meta_value), 0) as distance',
	'where' => 'YEAR(rides_groupA.ride_date.meta_value) = "' . $selectedyear . ' " ',
	'orderby' => 'distance DESC',
	'limit' => $limit
);

So in this statement I’m coming from the ‘Members’ pod, I reference the ride program through the group link, then I get the distance of the related route. That works well.

However If I simultaneously use:

	COUNT(rides_groupB.route_groupB.distance.meta_value) as count, 
	ROUND(SUM(rides_groupB.route_groupB.distance.meta_value), 0) as distance',

It doesn’t work, it says ‘Unknown Column’.
If I use it separately, it works. Alternatively, can I use three pods in one PHP page and combine that data later on?

PS: My programming language is limited, so I’m just doing A LOT of trail and error 🙂

I test the array with Group A, it works, tested it seperately with group B, it works as well.
Then I tried to combine, as written above, it doesn’t work. So I thought, let’s put it in the code twice and write the results to my own custom array. However that does give mixed results!

It does correctly list people that have ridden in Group B, so the ‘where’ clause seems to be working, but then the ride count and distance count is still wrong.

If I comment out the Group A part the Group B part runs properly, so I would assume something like caching? I have no idea or knowledge about the inner workings of this. Thanks for helping me out!

• This reply was modified 6 years, 5 months ago by Driekes.

@keraweb Sorry to bother you, but have you had a chance to check out my latest findings?