Count max admissions in joined db

[mattsh]

I?m a bit lost, so I need some guidance for best practice.

I plan to have an admission form for different events, and have 2 db tables, one for my events and one for registrations.

Events db:
Category, Date (text field ?25-26 October?), Date (Fabrik date element), Published, etc

Registration db:
Category (join), Date (cdd to date textfield), Name, Email etc.

The join and the ccd works fine and the correct dates are shown for each category in the registration form. But it would be great if I could simplify the work for the event administrators.

So if one or more of following points is fulfilled the date for the event should not be shown in ccd dropdown at the registration form.

1. I need to set a maximum numbers of admissions for each event date. It?s the same max number for all events.
2. I don?t want to show an event if it?s less than 10 days before the event held.
3. And it should be possible to unpublish an event. It should still be shown in the event list.

I manage to make 2 and 3 to work, using cdd element ?Advanced Where query?, but not the max count. I always end up with a sql-error.

Thanks!

 

[cheesegrits]

So what are you trying in your query? Give me the currently working one for 2 and 3, and whatever you've tried for 1. That'll help me understand what's going on.

-- hugh

 

[mattsh]

I try...

This code is to make the default option of category. It's in the joined dropdown element at the registration form:

$app = JFactory::getApplication();
return $app->input->get('this_category_id','GCP');

This is for option 2 and 3 and added to the cdd element in the event registration form and added to "Advanced Where query":

{thistable}.eventdate_real > (NOW() + INTERVAL 10 DAY) AND {thistable}.published = 1

This code above is working fine.

Something like this is what I like to add to count and hide event dates if it's more than 20 regisistration for that specific event, but a working code..

SELECT COUNT(DISTINCT event_dates) FROM jos_regsistration_table < 20

 

[mattsh]

An update. I have changed the set up slightly.

I'm now using a dbjoin element for the event dates to join the registration tabl with the event dates tabl.
The code I use to filter events in the registration form is working without errors (added to "Joins where and/or order by statement (SQL)":

{thistable}.eventdate_real > (NOW() + INTERVAL 10 DAY) AND {thistable}.active = 1 AND {thistable}.event_type = 'GCP' ORDER by {thistable}.eventdate_real ASC

But I'm still struggling with the code to limit the number of registration for a specific event and hide the date in the dbjoin listbox. The non working code I have tested is:

SELECT COUNT(DISTINCT event_dates) FROM jos_regsistration_table WHERE event_dates < 20

 

[mattsh]

Another update.
I think my wanted code instead should be like this, to do like expected. The code is working in phpMyAdmin, but not with Fabrik:

SELECT event_dates, COUNT( * ) AS num
FROM jos_regsistration_table
GROUP BY event_dates
HAVING num <20

 

[rob]

I need to set a maximum numbers of admissions for each event date. It?s the same max number for all events.

Have you looked at the limit form plugin? http://fabrikar.com/forums/index.php?wiki/limit-form-plugin/

I don?t want to show an event if it?s less than 10 days before the event held.

I would use a list pre-filter http://fabrikar.com/forums/index.php?wiki/pre-filters/ at the bottom has some examples of limiting records by date

 

[mattsh]

Thanks for the suggestions.

I did a quick test of the limit form plugin, but I can't get it to work. Even if I let User element, Limit length, Lookup element and Max element all have the value 1, I can still add many registrations.

The pre-filter works on the list, but I need it to work on the registration form.

 

[mattsh]

I have changed approche and think it would be good to use a list and pre filter it.

But it's still the hiding of events when they are fully booked I'm missing.
See the attached picture, with the list and related data function enabled.

If it would be possible to compare data from my Fabrik element "Max attendees" with the related data (Registration Table), and hide the specific event (row) if the data in the Registration Table is equal or bigger than Max attendees.

If this last hurdle work, I would be very happy!

 

[rob]

you could do this with a sneaky prefilter.

where: Max Attendees
condition: less than
type: query

value:

SELECT COUNT(*) FROM FROM jos_regsistration_table WHERE jos_regsistration_table.event_id = jos_events.id

That presumes your main db table is: jos_events, with a primary key of 'id' and that in jos_registration_table there is an element 'event_id' which contains a reference to the jos_events primary key.

 

[mattsh]

Thanks Rob!

It works perfect, I just updated the code slightly (deleted one extra FROM) and changed the condition to greater than and use public view.
In addition I added a new pre filter with "or" between these two:
where: Max Attendees
condition: greater than or equal
type: text
value: 0
view just for registrated.

The addition make "hidden" events visible for logged in users.