Thread

Here is what I have and what I would like to do is in ALLCAPS in the link path (I need to get the category name of the linked entry there). Can anyone help me achieve that?

<ul class="relatedProjects">
        <li>{related_entries id="client"}Client: <span>{title}</span>{/related_entries}</li>
        <li>Related Projects: {exp:query sql="SELECT exp_weblog_titles.title as my_title, exp_weblog_titles.url_title as my_url_title
FROM (exp_relationships INNER JOIN exp_relationships AS exp_relationships_1 ON exp_relationships.rel_child_id = exp_relationships_1.rel_child_id) INNER JOIN exp_weblog_titles ON exp_relationships_1.rel_parent_id = exp_weblog_titles.entry_id
WHERE (((exp_relationships.rel_id)={client}) AND exp_weblog_titles.weblog_id=4) ORDER BY exp_weblog_titles.entry_date"}
        {if my_url_title != segment_3}<a href="http://{path=/graphics-projects/CATEGORY_NAME/">{my_title}</a>{/if}
        {/exp:query}</li>
        </ul>

So I would like to query the category of each of those related entries as well so I can add that category name to the link path (category name is same as template name) - otherwise the links to those entries don’t work… I’m not too savvy with sql queries - I received help for the one written above - so any help is appreciated! thx

Anyone? Anyone? I really need some help here… Is this possible? Please?

So {categories}{category_name}{/categories} won’t work?
Is all this being placed in an exp tag?

no that doesn’t work because it places the category of the currently viewed entry, instead of the category of the linked related entry, which often is in a different category.

To summarize what is happening, I am pulling all of the entries which share the same client and listing them as “Related Projects” - but I would like to list them by their category_name instead of their title.

I think I’m missing some of the code here. Is there an exp tag somewhere in this? Don’t you need to call an entry before you can call a related entry?

sorry - that part is just outside of the code I posted:

{exp:weblog:entries weblog="graphics-projects" limit="1"}

<div id="main"> 
        <h1>{title}</h1>
        <ul class="relatedProjects">
        <li>{related_entries id="client"}Client: <span>{title}</span>{/related_entries}</li>
        <li>Related Projects: {exp:query sql="SELECT exp_weblog_titles.title as my_title, exp_weblog_titles.url_title as my_url_title
FROM (exp_relationships INNER JOIN exp_relationships AS exp_relationships_1 ON exp_relationships.rel_child_id = exp_relationships_1.rel_child_id) INNER JOIN exp_weblog_titles ON exp_relationships_1.rel_parent_id = exp_weblog_titles.entry_id
WHERE (((exp_relationships.rel_id)={client}) AND exp_weblog_titles.weblog_id=4) ORDER BY exp_weblog_titles.entry_date"}
        {if my_url_title != segment_3}<a href="http://{path=/graphics-projects/CATEGORY_NAME/}">{my_title}</a>{/if}
        {/exp:query}</li>
        </ul>
        
        {related_entries id="client"}{if client_body}<h3>About {title}:</h3>
<p>        {client_body}{/if}{/related_entries}<br />
        <br />
        </p><h3>About the project:</h3>
<p>        {project_body}<br />
        </div><!-- end main --></p>

<p>{/exp:weblog:entries}

FYI - the path uses single quotes instead of double because the code tag keeps stripping it out with double quotes…

For something like this i’d suggest starting by grabbing Solspace‘s Primary Category extension—because as Sue notes, entries can have more than one category. Alternatively, if you can guarantee that your entries will only have one category (because, for instance, you’re the only one entering them), try this:

<li>Related Projects: {exp:query sql="SELECT title, entry_id, cat_name from
   (SELECT title, url_title, entry_id, cat_id 
   FROM exp_weblog_titles, exp_category_posts
   WHERE exp_weblog_titles.entry_id IN 
      (SELECT rel_child_id FROM exp_relationships 
      WHERE rel_id = '{client}')
   AND exp_weblog_titles.entry_id = exp_category_posts.entry_id
   ) AS t1, 
   exp_categories
WHERE t1.cat_id = exp_categories.cat_id"}

{if url_title != segment_3}<a href="http://{path=/graphics-projects/{cat_name}/}">{title}</a>{/if}
        {/exp:query}</li>

If this doesn’t work, or you need more help, feel free to PM me!

unfortunately doing the query this way doesn’t generate anything. The previous query does what I want, with the exception of it querying the categories as well. See this thread here: http://ellislab.com/forums/viewthread/87834/

I need to expand on that query to also query and reference the categories of the related entries… i’m not totally savvy with the queries though - i’m learning, but it’s slow-going. :( Not sure if Solspace’s Primary Category extension is going to help with that issue…

Oh, my mistake. I misread what you were originally trying to do. The thread link makes it clearer, thanks!

Have you tried Mr. Wilson’s SiblEEngs plugin? Combined with the query plugin it might do exactly what you want. You’d do something like this, assuming it will work with the query plugin at all (and it *should*, if you set it to parse inward).

{exp:sibleengs url_title="{segment_3}" orderby="title" sort="desc" exclude_self="yes" parse="inward"}
{exp:query sql="select '{sibleengs_title}' as mytitle, cat_name from exp_categories 
where cat_id in 
(select cat_id from exp_category_posts where entry_id = '{sibleengs_entry_id}')"}
<a href="http://{path=/graphics-projects/{cat_name}/}">{mytitle}</a>
{/exp:query}
{/exp:sibleengs}

Failing that, the right query is something like (i’m not bothering to put this inside an exp:query tag this time, and i’m putting in a lot of whitespace just for readability):

SELECT my_title, my_url_title, t2.entry_id, cat_name FROM
   (SELECT exp_weblog_titles.title as my_title, 
   exp_weblog_titles.url_title as my_url_title, exp_weblog_titles.entry_id
   FROM 
      (exp_relationships INNER JOIN exp_relationships AS exp_relationships_1 
      ON exp_relationships.rel_child_id = exp_relationships_1.rel_child_id) 
   AS t1
   INNER JOIN exp_weblog_titles 
   ON exp_relationships_1.rel_parent_id = exp_weblog_titles.entry_id
   WHERE (((exp_relationships.rel_id)={client}) 
   AND exp_weblog_titles.weblog_id=4)
) AS t2, 
exp_category_posts, exp_categories
WHERE t2.entry_id = exp_category_posts.entry_id 
AND exp_category_posts.cat_id = exp_categories.cat_id

This is off the top of my head, so it might need modification.

If neither of these things works, you might consider PMing me and we can work through it on your actual EE installation—I’m happy to help, but it’s sometimes hard for me to work sight-unseen like this.

Thank you so much for helping me out with this - it is VERY much appreciated. I will take a look at the siblEEngs plugin and play with this new query string, try to understand it and see where i get.

One thing, though - what are the “t1” and “t2” bits for? It might help me understand better what exactly the query is doing so i can know how to approach similar issues in the future…?

Those are table aliases; you use those to say “wrap up this block i just gave you as a ‘new table’ for the purposes of this query, and name it t1”. (Or t2, as the case may be.)

And seriously, if that doesn’t work, don’t hesitate to PM me. I love doing this stuff!  It’s good to keep in practice.