Count entries in many to many table

[pie]

Hi, I have a table of events and a table of locations which are joined through a events_locations table which keeps track of what and where.
Each location may host many events, so my table contains two "interesting" columns: location-id and event-id.

In a fullcalendar view I am showing all the events.
I also have a link to the event list (based on the events table) as a "standard" list view.

In both of these I'd like to show the count of occurrencies of each scheduled event aside of the event name.
I.e. "event1 (45)" means that event1 is scheduled in 45 locations.

I think I should count how many event1 id are there in the events_locations table, then append this number to the event title.

What would the best approach be?


Thank you

P.s. a cool thing would be to dinamically update a status field to change colors through css if the event is not yet scheduled anywhere: event2 (0) - in gray

 

[cheesegrits]

Hmm. The only thing I can think of is to use a calc, which is sub-optimal as it'll mean a lot of extra queries when building the list ... but it would work ...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('COUNT(*) AS foo')->from('events_location')->where('event_id = ' . $db->quote('{rowid}'));
$db->setQuery($query);
$total = $db->loadResult();
$total = empty($total) ? '0' : $total;
return "{yourtable___eventname} ($total)";

For the CSS ... again, the only thing I can think of is a calc element, but again it'd become murderously expensive on queries, as you'd have to have a calc that essentially did the same thing as above, but returned a class name, like eventTotalX, designate that calc a "Use as row class", and add a custom CSS to your template for eventTotal0.

The only other way I can think of would be a php_events list plugin, running onLoad, which did kinda the same thing but updates the title in place. See ...

http://fabrikar.com/forums/index.php?wiki/php-events-list-plugin/

.. the first code example on that page. You would do basically the same code as above, but in the group/row loop, and getting the event id from $row->__pk_val, and appending the count to $row->yourtable___eventname.

Something like this:

// $model is the list model, we want to get its data and then alter it.
$data = $model->getData();
$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach($data as $group)
{
   foreach($group as $row)
   {
      $query->clear()->select('COUNT(*) AS foo')->from('
      $query->clear()
         ->select('COUNT(*) AS foo')
         ->from('events_location')
         ->where('event_id = ' . (int)$row->__pk_val);
      $db->setQuery($query);
      $total = $db->loadResult();
      $total = empty($total) ? '0' : $total;
      $row->yourtable___eventname .= '(' . $total . ')';
      $row->yourtable___class = 'eventTotal' . $total;
   }
}

You could make that more efficient by only doing one COUNT query, grouping on the event_id ... but you'd have to do the group/row loop twice, once to build an array of pk ids, then do the grouped count query, then round a loop again to set the values.

Hmmm ... although ... I have a sinking feeling none of this will work in the fullcalendar viz ... where for performance reasons we "manually" load the event data from the list, rather than using the full blown getRows() from the list model, which has to do all the rendering of every element.

Double hmmm ...

-- hugh

 

[pie]

Thank you, I'll look into this even if I have a bad feeling about performance loss compared to the improvement of features...

 

[cheesegrits]

The list plugin wouldn't be too bad, if it was refactored to do a single query.

The main problem is going to be getting the title to work in fullcalendar, as we don't run any list or element plugins for that, we just look up the data "by hand", for performance reasons. Calendars can get quite large, with hundreds of events in a month, and trying to do a full list render on hundreds of rows is very slow, and just doesn't work well with the AJAX loading of each month as you navigate the calendar. Typically it's about a second per 10 rows, so 200 events in a month ... :/

So neither the calc nor the list plugin would work for the calendar itself.

The only other way I can think of to do this would be to bypass Fabrik entirely, and put a MySQL trigger on the events_location table, on insert and delete, which updates the events table's title / class rows. You'd probably need to add a second field for the title, which the trigger updates, and use that in list display and for the calendar event title, so the tirgger can do a concat of the original title with the count.

-- hugh

 

[pie]

I'm sorry, I believe that there is some mistype here, but I can't understand where: syntax error, unexpected ';', expecting ',' or ')'
where does $query->clear()->select('COUNT(*) AS foo')->from('
is supposed to end?

// $model is the list model, we want to get its data and then alter it.
$data = $model->getData();
$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach($data as $group)
{
   foreach($group as $row)
   {
      $query->clear()->select('COUNT(*) AS foo')->from('
      $query->clear()
         ->select('COUNT(*) AS foo')
         ->from('events_location')
         ->where('event_id = ' . (int)$row->__pk_val);
      $db->setQuery($query);
      $total = $db->loadResult();
      $total = empty($total) ? '0' : $total;
      $row->yourtable___eventname .= '(' . $total . ')';
      $row->yourtable___class = 'eventTotal' . $total;
   }
}

Just discovered that I cannot setup mysql triggers on my shared webhost, however maybe I could "mix" your suggestions:
what if instead of a mysql trigger I do the count everytime a new event is added/removed using element checkbox/dbjoin and put the event occurrency value in a field (as I would have done with mysql trigger)?

Do you think it's possible? May you please guide me a bit here?
thank you

 

[cheesegrits]

Ooops, that first $query line shouldn't be there, I must have reformatted it and forgot to delete part of it.

// $model is the list model, we want to get its data and then alter it.
$data = $model->getData();
$db = JFactory::getDbo();
$query = $db->getQuery(true);

foreach($data as $group)
{
   foreach($group as $row)
   {
      $query->clear()
         ->select('COUNT(*) AS foo')
         ->from('events_location')
         ->where('event_id = ' . (int)$row->__pk_val);
      $db->setQuery($query);
      $total = $db->loadResult();
      $total = empty($total) ? '0' : $total;
      $row->yourtable___eventname .= '(' . $total . ')';
      $row->yourtable___class = 'eventTotal' . $total;
   }
}

Yes, rolling your own "trigger" with a submission script would work. Obviously you wouldn't do that loop, you'd grab the event ID from the submitted data, then update the event table manually.

-- hugh