Filtering with multiple criteria

Question

IM running into an issue where I have one page that lists latest restaurant entries in a given section. However I also have a form to filter the entries by postcode (a category element and by cuisine type (tag element).

I'm also required to give some rudimentary search functionality, which for now is governed by a seperate form. Both forms redirect to the same root page,

Here i my slimmed down code

{% set restaurants = craft.entries.section('restaurants').limit(null) %}
{% set locations = craft.categories.group('postcode').relatedTo(restaurants).limit(null) %}
{% set types = craft.tags.group('imageTags').relatedTo(restaurants).limit(null) %}


{% set l =  craft.request.getParam('l') %}
{% set t = craft.request.getParam('t') %}
{% set q = craft.request.getParam('q') %} 


{% set locId = craft.categories.group('postcode').slug( l ).ids() %} 
{% set locName = craft.categories.group('postcode').slug( l ).first.title %} 
{% set typeId = craft.tags.group('imageTags').name( t ).ids()  %}


{% if (l or t) %}
    {% set relatedTo = ['and'] %}

    {% for id in locId %}
      {% set relatedTo = relatedTo|merge([id]) %}
    {% endfor %}

    {% for id in typeId %}
      {% set relatedTo = relatedTo|merge([id]) %}
    {% endfor %}

{% else %}


{% endif %}


{% set params = {
        section : 'restaurants',
        limit: 24,
        relatedTo: relatedTo ? relatedTo, 
        search : q ? q,
        }
%}



{% set entries = craft.entries(params) %}

Pagination and for loop

{% paginate entries as galEntries %}




                {% for entry in galEntries %}

                    {% include 'restaurants/includes/_rest-item' %}

                {% endfor %}



{% endpaginate %}

My form elements

<form action="{{ url('restaurants') }}" method="get" action="" class="rest-filter">

            <label>Filter :</label>

            <select name="l" id="">
                <option value="">All Locations</option>

                {% for loc in locations %}
                <option value="{{ loc.slug }}" {{ loc.slug == l ? 'selected' }}>{{ loc.title }}</option>    
                {% endfor %}

            </select>

            <select name="t" id="">
                <option value="">All Types</option>

            {% for type in types %}
                <option value="{{ type.name }}" {{ type.name == t  ? 'selected' }}>{{ type.name | capitalize }}</option>
            {% endfor %}

            </select>

            <input type="submit" value="Filter">

        </form>

        <form action="{{ url('restaurants') }}" method="get" action="" class="rest-search">
            <input type="text" name="q" class="text" value="{{ searchq }}" placeholder="Search for restaurants&hellip;" required />
                            <button type="submit"  class="ss-icon sn-search-button">Search</button>
        </form>

It works for the most part except for when I only filter by one element. Which throws the following error CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join

ANy ideas where I'm going wrong?

Answer 1

It looks like your loops are causing Yii to add joins to the craft_relations table once for each iteration of your loop.

{% for id in locId %}
  {% set relatedTo = relatedTo|merge([id]) %}
{% endfor %}

{% for id in typeId %}
  {% set relatedTo = relatedTo|merge([id]) %}
{% endfor %}

I would guess that either locId or typeId contains an array with many values. Try using {{ dump() }} or {{ locId | length }} to see how many values you are dealing with.

You might need to refine the query for each of these locId and typeId to ensure you get a single result.

Alternatively if you really want to search among that many records you might need to redesign your approach. Perhaps build an SQL query that achieves what you want first then work your way back from there.