Thread

I’m trying to build a “local church finder”. Although it does work the way it’s set up, it is extremely heavy on the database now.

Every District (first column) has several Regions (second) wich has several Churches (third).

I made a weblog for Districts, one for Regions and another for Churches with relationships inbetween.

Because I need a well structured “Churches/Regios/Districts” output into UL’s and DIV’s for the tabs/subtabs I’m calling Districts and then through reverse relations calling Regio’s to build the UL’s and again to build the DIV’s. I’m passing a variable (Regio) to an embed that’s calling all Churches wich are related to that specific Regio.

The first part (District/Regio) goes fine, for there aren’t too many districts or regio’s. But then it gets really ugly.

Anyone have an idea how to structure or call the content more efficiently?

The code:

~ index page:

<div id="zoeker" class="span-24">
    <h3>Kerkzoeker</h3>
<p>    <ul id="district" class="span-8"><br />
        <span></span><br />
    {exp:weblog:entries weblog="lokale-kerken-district" orderby="title" sort="desc" dynamic="off"}<br />
        <li class="tab"><br />
            <a href="#regio-{url_title}">_                <strong>{district-naam}</strong>_                <br>_                {district-ondertitel}_            </a><br />
        </li><br />
    {/exp:weblog:entries}<br />
    </ul><br />
    {exp:weblog:entries weblog="lokale-kerken-district" limit="" orderby="title" sort="desc" dynamic="off"}<br />
    <div id="regio-{url_title}" class="span-16 last"><br />
        <ul class="span-4"><br />
            <span></span><br />
        {reverse_related_entries weblog="lokale-kerken-regio" orderby="title" sort="asc"}<br />
            <li><a href="#locatie-{url_title}">{title}</a></li><br />
        {/reverse_related_entries}<br />
        </ul><br />
        {reverse_related_entries weblog="lokale-kerken-regio" orderby="title" sort="asc"}<br />
        <div id="locatie-{url_title}" class="span-12 last"><br />
            {embed="lokale-kerken/locatie" regio="{url_title}"}<br />
        </div>    <br />
        {/reverse_related_entries}<br />
    </div><br />
    {/exp:weblog:entries}<br />
</div>

~ “location” embed:

{exp:weblog:entries weblog="lokale-kerken-regio" limit="" orderby="title" sort="desc" dynamic="off"}
{if url_title == "{embed:regio}"}
    <ul class="span-4">
        <span></span>
    {reverse_related_entries weblog="lokale-kerken-locatie" orderby="title" sort="asc"}
        <li><a href="#dienst-{url_title}">{title}</a></li>
    {/reverse_related_entries}
    </ul>

    {reverse_related_entries weblog="lokale-kerken-locatie" orderby="title" sort="asc"}
    <div id="dienst-{url_title}" class="kerk span-8 last">
        {embed="site/dienst" locatie="{url_title}"}
    </div>    
    {/reverse_related_entries}
{/if}
{/exp:weblog:entries}

~ “dienst”/details embed:

{exp:weblog:entries weblog="lokale-kerken-locatie" limit="" orderby="title" sort="desc" dynamic="off"}
{if url_title == "{embed:locatie}"}
    {if locatie-prent}
    {exp:imgsizer:size image="{locatie-prent}" width="146" alt="{title}" class="kerkprent"}
    {/if}
    <h4>{title}</h4>
<p>    {locatie-straat} {locatie-straat-nummer}{if locatie-straat-nummer-bus} bus {locatie-straat-nummer-bus}{/if}</span><br />
    </p>

<p>    {locatie-postcode} {locatie-gemeente}<br />
    {reverse_related_entries weblog="lokale-kerken-dienst" orderby="dienst-uur" sort="asc"}<br />
    dienst om {dienst-uur}u{dienst-minuten}<br />
    {/reverse_related_entries}<br />
    {if locatie-detail}<a href="http://{path=">→ detailpagina</a>{/if}<br />
{/if}<br />
{/exp:weblog:entries}

(I know it’s quite ugly)

There are certainly other ways. Any reason you couldn’t simply use categories / sub-categories? Also, moving to Howto.

looks to me like the simplicity of using the EE tags to get the desired output removes the ability to optimize the query for your particular setup.  I think that you can leave your weblog structure as-is, but in order to improve performance you should probably make a plugin that executes a single query for each embed and outputs everything you need in one pass through the data.

Also if this stuff doesn’t change often you could use tag/template caching… although if the page is *really* slow to load when it’s uncached then you’ll still need to find ways to speed it up in order to have an acceptable solution.

@Ingmar: thanks for the suggestion. Only I’m wondering how, if, I can get the desired output using categories (the reason why I went with the separate weblogs, reverse relations and embeds)?

@Ty: the seemingly simple tricked me in believing this would work for someone with no database or programming skills. A pity, ‘cause the clients where mad about the functionality. That is.. until it grinded to a halt.

Attached is the output as is desired. (moved the working example to a out-of-clients’-sight-page for now)

Now I’m using a double reverse_relationship lookup per tab (li+div) per level (once to build the li’s and once for the div’s, two embeds deep and then another embed for the entry data).
This apparently has an exponential effect on querying.. not a good idea.

Anyone have a clue how to get this done smoothly? Or able to offer a solution?

Output, for tabs’ sake, looks like:

<ul>
  <li><a href="#1"> 1 </a></li>
</ul>

<div id="1">

  <ul>
    <li><a href="#1.1"> 1.1 </a></li>
  </ul>

  <div id="1.1">

    <ul>
      <li><a href="#1.1.1"> 1.1.1 </a></li>
    </ul>

    <div id="1.1.1">
      Finally, the contents!
    </div>

  </div>

</div>

“1” is a weblog (Districts)
“1.1” another weblog (Region) with a field relating to “1”
“1.1.1” again a weblog (Location) with a field relating to “1.1.1”

Through reverse relationships and embeds I’m able to get everything where it needs to be within this structure.

I’m not seeing how I can achieve this simply using categories.

seems like you could use categories for this.  main categories would be your districts, subcategories would be your regions, entries in the weblog would be your locations

either way though, you need to write a plugin that builds this tree of jquery UI tabs/data recursively.  I had a custom nav that had similar slowdown (although it was using categories) when using the native EE tags.  I sped it up by writing a plugin that builds the nav out of a single database query and a couple recursive php functions.  this seems like it could be solved in much the same way.

I definitely like your demo, and it works great once it loads… very cool use of the tabs.  I’d be happy to help speed it up if you need assistance writing the code, but I’d venture a guess that it’ll take a couple hours and my employer probably wouldn’t want me to do it for free. 😉  Available to help if you don’t mind paying my time though.