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

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

{% for id in typeId %}
  {% set relatedTo = relatedTo|merge([id]) %}
{% endfor %}
</code></pre>

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

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

<p>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.</p>

CC BY-SA 3.0
2014-10-08T04:00:36.227Z
2014-10-08T04:00:36.227Z
Henry
<p>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).</p>

<p>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,</p>

<p>Here i my slimmed down code</p>

<pre><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) %}
</code></pre>

<p>Pagination and for loop</p>

<pre><code>{% paginate entries as galEntries %}




                {% for entry in galEntries %}

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

                {% endfor %}



{% endpaginate %}
</code></pre>

<p>My form elements</p>

<pre><code>&lt;form action="{{ url('restaurants') }}" method="get" action="" class="rest-filter"&gt;

            &lt;label&gt;Filter :&lt;/label&gt;

            &lt;select name="l" id=""&gt;
                &lt;option value=""&gt;All Locations&lt;/option&gt;

                {% for loc in locations %}
                &lt;option value="{{ loc.slug }}" {{ loc.slug == l ? 'selected' }}&gt;{{ loc.title }}&lt;/option&gt;    
                {% endfor %}

            &lt;/select&gt;

            &lt;select name="t" id=""&gt;
                &lt;option value=""&gt;All Types&lt;/option&gt;

            {% for type in types %}
                &lt;option value="{{ type.name }}" {{ type.name == t  ? 'selected' }}&gt;{{ type.name | capitalize }}&lt;/option&gt;
            {% endfor %}

            &lt;/select&gt;

            &lt;input type="submit" value="Filter"&gt;

        &lt;/form&gt;

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

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

<p>ANy ideas where I'm going wrong?</p>

CC BY-SA 3.0
2014-10-02T19:30:56.227Z
Can you enable devMode, reproduce the error and post SQL query it's trying to execute from the stack trace?
Brad Bell
CC BY-SA 3.0
2014-10-03T09:01:41.920Z
https://gist.github.com/anonymous/bb59305a5027a6556a6b
Seán O'Grady
<relations><search><filter>
Filtering with multiple criteria
# Filtering with multiple criteria

(Asked by: username_0 on 2014-10-02)

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


            Filter :

            
                All Locations

                {% for loc in locations %}
                {{ loc.title }}    
                {% endfor %}

            

            
                All Types

            {% for type in types %}
                {{ type.name | capitalize }}
            {% endfor %}

            

            

        

        
            
                            Search
        

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?

**Tags:** relations, search, filter

> **Comments:**
> **username_1**: Can you enable devMode, reproduce the error and post SQL query it's trying to execute from the stack trace?  
> **username_0**: https://gist.github.com/anonymous/bb59305a5027a6556a6b  

---

## 1 answers

### Answer by username_2 on 2014-10-08. Score: 2 | Accepted answer

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.


---

