HubDB Query - Filtering by comparing values

[EmilyVaulted]

Background:

I am working on a query for a listing of events pulled from HubDB. Each row has a listing with event data, like event name, event time, etc.... Each event type has an id in a column, we'll call it `event_type_id`. This id is unique to the series of events (but not necessarily unique to individual events) as sold in a ticketing platform. The id is a string of numbers. Sometimes we have multiple events that run multiple times over a span of a couple days that share the same `event_type_id`. For this example we could say we have an event called "Movie A" with the event_type_id "123456" that runs 2 times a day for 3 days in a row.

Issue:

I want to create an event listing that lists out 10 unique upcoming events from the current time. I have a query set up already, but I'm finding if there are multiple events (ex. "Movie A" runs 2 times a day 3 nights in a row) the results are 5 rows of "Movie A" and then any other sequential events. 

Starting Point:

{% set currentDate = local_dt|unixtimestamp %}

{% set query = 'event_date__gt=' + currentDate + '&limit=10&orderBy=event_time' %}

{% set table = hubdb_table_rows(XXXXXXX, query) %}

Question:

Is there any way that I can filter the HubDB query based on if the `event_type_id` is already in the list of rows? The desired result is if there's already an event with `event_type_id == "123456"` returned any subsequent event with the same ID is skipped. So instead of 5 "Movie A"s at the top of my list I get something like:

1. Movie A

2. Movie B

3. Event C

4. Event D

5. Movie E

and so on...

[elizheleva · Accepted solution]

Hi, @EmilyVaulted ,

am I right to think that you have a unique row for each event regardless of whether the event_type_id is unique or not? If so, the simplest thing I can think of is to add another column in the HubDB called "main event" and to have a checkbox. This way, when you add the events themselves, you need to make sure that you tick that box for the first instance of the event. This way, in the filtering option, you will need to specify that you want to show only events that have this tickbox selected.

Do you think this can work?

Eli 

[paradummy · Accepted solution]

Hi @EmilyVaulted Another way would be to get all the rows from the table (without the limit) and filter out the duplicates with hubl:

{% set filtered_rows = [] %}
{% set used_ids = [] %}

{% for row in table %}
  {% unless row.event_type_id in used_ids %}
    {% do filtered_rows.append(row) %}
    {% do used_ids.append(row.event_type_id) %}
  {% endunless %}
{% endfor %}

{{ filtered_rows|pprint }}

[EmilyVaulted · Accepted solution]

Hi everyone, I wanted to reply with my solution. For clarification, the `event_type_id` was a shared number spread across multiple events only to denote the type of event (this was coming from a custom database that was built to track event type categories across a POS system). Each event on the row had it's own specific, unique id `event_id`. I wanted to group the event rows with multiple events (that shared the same `event_type_id`), so I realized I could use a groupby filter. So we could use this to group by any other shared value in the column like `event_name`.

Here's an example of how I adjusted the code.

{% set currentDate = local_dt|unixtimestamp %}
{% set query = 'event_date__gt=' + currentDate + '&limit=10&orderBy=event_time' %}
{% set table = hubdb_table_rows(XXXXXXX, query) %}

{% for group in table|groupby("event_type_id") %}
  {{ group.list[0].event_name }}
  {{ group.list[0].event_description }}
{% endfor %}

Then, I realized that limiting the query results by 10, then filtering with a groupby was not giving me the desired result of 10 items, so our workaround was to increase the limit to 50 (or whatever number works best for the dataset). Then in the loop we returned based on the index count.

Example of the final result:

{% set currentDate = local_dt|unixtimestamp %}
{% set query = 'event_date__gt=' + currentDate + '&limit=50&orderBy=event_time' %}
{% set table = hubdb_table_rows(XXXXXXX, query) %}

{% for group in table|groupby("event_type_id") %}
  {% if loop.index <= 10 %}
     {{ group.list[0].event_name }}
     {{ group.list[0].event_description }}
  {% endif %}
{% endfor %}

I'm open to better ways to do this if there are any. This method is what is giving us the most reliable result at this time.

[paradummy · Accepted solution]

Hi @EmilyVaulted Another way would be to get all the rows from the table (without the limit) and filter out the duplicates with hubl:

{% set filtered_rows = [] %}
{% set used_ids = [] %}

{% for row in table %}
  {% unless row.event_type_id in used_ids %}
    {% do filtered_rows.append(row) %}
    {% do used_ids.append(row.event_type_id) %}
  {% endunless %}
{% endfor %}

{{ filtered_rows|pprint }}

[elizheleva · Accepted solution]

Hi, @EmilyVaulted ,

am I right to think that you have a unique row for each event regardless of whether the event_type_id is unique or not? If so, the simplest thing I can think of is to add another column in the HubDB called "main event" and to have a checkbox. This way, when you add the events themselves, you need to make sure that you tick that box for the first instance of the event. This way, in the filtering option, you will need to specify that you want to show only events that have this tickbox selected.

Do you think this can work?

Eli