Help filtering (limit) two HubDb tables after being combined

[Pixelwerx]

Hi all,

I hope someone can help me figure out what I'm missing.

I have started using HubDb for many parts of our site, that use the same content. I have two main tables, one for download type content (pdfs, video, etc.), and one for pages that is used for the product listing page.

I do have a foreign id field linking these two for my other uses, but for the case I'm having problems with, it doesn't seem to be the way to go.

I'm making a 'latest content' module, that needs to pull from either of these databases, based on publish date. We show any recent content (page or download), so I'm combing the two tables as seen below.

{% set queryParam = 'publish_date__not_null=' %}
{% set pages = hubdb_table_rows(XXXXXXX, queryParam) %}
{% set downloads = hubdb_table_rows(XXXXXXX, queryParam) %}
{% set table = downloads + pages %}
  {% for row in table|sort(true, false, 'publish_date') %}
    <div></div>
  {% endfor %}

I want to limit how many rows are pulled in after the tables are combined. I have tried making a limit= filter query, and adding it after the 'downloads + pages', with no luck. I know I can filter on the two separate table calls, but it has to be after combined to work the way I need.

Is there a way to do this that I'm missing, or is it just not possible the way I've got it?

Thank you in advance for any help 🙂

[Pixelwerx]

Sorry for the late reply, and thank you for the additional option to try.

With some futzing, I was able to make it limit the combined results, but the problem is that now it's not sorted as needed. I need them to be ordered by the publish date field, in reverse order. I can't figure out how to add a sort to the option you've provided.

Also, I've had to add 'table[loop.index-1]' to every field I want to pull in (as seen below), where I'd normally have 'row'.

{% set queryParam = 'publish_date__not_null=' %}
{% set pages = hubdb_table_rows(XXXXXXX, queryParam) %}
{% set downloads = hubdb_table_rows(XXXXXXX, queryParam) %}
{% set table = downloads + pages %}
  {% for x in range(0,6) %}
    <div>{{ table[loop.index-1].display_name }}</div>
  {% endfor %}

Is there really just not a way to add a query paramater to two set table variables, something like the following:

{% set queryParam = 'publish_date__not_null=' %}
{% set orderParam = 'orderBy=-publish_date&limit=6' %}
{% set pages = hubdb_table_rows(XXXXXX, queryParam) %}
{% set downloads = hubdb_table_rows(XXXXXX, queryParam) %}
{% set table = downloads + pages, orderParam %}

Thank you again for helping out. If you have any further ideas, please let me know.

Oliver 

[gsantos1]

Hey @Pixelwerx  have you solved this?

I have the same problem, 2 tables, need sort, pagination and dropdown filter, but can't make it happen if I merged the tables like your case:

{% set table = downloads + pages, orderParam %}

[Pixelwerx]

I'm starting to get the impression there is not a solution for this particular use case.

Not ideal, but I am currently having to use a separate database for our 'latest content' to get it to work how I need. It means duplicating data, but it works for now.

[gsantos1]

I managed to sort the merged tables with this:

{% set tablefull = downloads + pages %}
{% set tableordered = tablefull|sort(False, False, "name") %}

It also worked with number column, but I havent tried full date as your case.

Maybe it might work if you convert the publish date with unixtimestamp, or maybe with hs_created_at.

[Pixelwerx]

I have a version where I have the sort on the  {% for row in table|sort(true, false, 'publish_date') %} line. But I still need to also limit to 6 latest records.

[mangelet · Accepted solution]

@Pixelwerx 

Consider building your for loop like this:

{% set pages = ['A', 'Page 1', 'Page 2', 'Page 3', 'Page 4', 'Page 5'] %}
{% set downloads = ['B','Download 1', 'Download 2', 'Download 3', 'Download 4', 'Download 5'] %}
{% set table = downloads + pages %}

{{table|sort}}
{# result: [Download 1, Download 2, Download 3, Download 4, Download 5, Page 1, Page 2, Page 3, Page 4, Page 5] #}

{% for x in range(0,5) %}
{{ table[loop.index-1] }}
{% endfor %}
{# result: B Download 1 Download 2 Download 3 Download 4 #}

[Pixelwerx]

Does anyone else have any ideas? Is this possible with my approach, or is there a totally different approach I should try?

Any help would be greatly appreciated.

Oliver

[Pixelwerx]

Thank you for the reply Martin. However, no matter where in the chain I tried that, it didn't work as intended. 

If I put it inside the first or second for statements, it just duplicated items by however I set the range (i.e., using range (0,5) made each item show up 5 times).

In other words, I have A - I to pull from, and I want to limit to A - E. What is happening is AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, FFFFF, GGGGG, HHHHH, IIIII. Or if I put it as the first for statement, I get A, B, C, D, E, F, G, H, I, A, B, C, D... etc. 

Also, I didn't originally show all there is to the code, there is more going on in the loop.

{% set queryParam = 'publish_date__not_null=' %}
{% set pages = hubdb_table_rows(XXXXXXX, queryParam) %}
{% set downloads = hubdb_table_rows(XXXXXXX, queryParam) %}
{% set table = downloads + pages %}
  {% for row in table|sort(true, false, 'publish_date') %}
    {% for item in row.content_type %}
    {% if item.name == 'video' %}
        <div>Video Content</div>
    {% else %}
        <div>Link Content</div>
    {% endif %}
  {% endfor %}

Any other ideas?

Thank you,

Oliver

[mangelet]

Consider cycling with a ranged for loop like

{% for x in range(0,limit) %}
// do your thing with the table 
{% endfor %}