Thread

Thanks to the Wiki : http://expressionengine.com/wiki/Alphabetical_Navigation/

I started out with this which gives me a part of what I need:

{exp:query limit="1" paginate="bottom"
sql="SELECT title, entry_id, url_title, url_title AS urlt
FROM exp_weblog_titles    
WHERE weblog_id = '3'
{if segment_3}AND title LIKE '{segment_3}%'{/if}
ORDER BY title ASC"}

I can then filter ALL posts by letter (with pagination) like this:

<a href="http://{path=template_group/alpha/a/}">A</a> | <a href="http://{path=template_group/alpha/b/}">B</a> |....

❓ What I can’t figure out is how to filter by Category… and then further filter by alphabet.

I can envision an URL like this, but I’m not sure where to start:

site.com/index.php/template_group/template/category/my-category-name/a/P0/

Not sure exactly where to start? Another custom query perhaps?

A query something like this should get you off in the right direction:

SELECT 
    t.title, 
    t.entry_id, 
    t.url_title, 
    t.url_title AS urlt
FROM 
    exp_weblog_titles AS t NATURAL JOIN
    exp_category_posts AS p NATURAL JOIN
    exp_categories AS c
WHERE
    weblog_id = '3' AND
    {if segment_5}title LIKE '{segment_5}%' AND{/if}
    {if segment_4}c.cat_url_title = '{segment_4}'{/if}
ORDER BY title ASC

Thank you Ty! MUCH appreciated! That worked brilliantly!!

One related question:  ❓  Could you use a similar query to build the navigation (A | B | C etc…) so that if there were no entries, for example, with a “Q” or “X” title, then “Q” would not appear as a link?

❓ Or if it was a drop-down menu type of filter, Q or X (or any letter not containing a result) would not appear in the list?

Thanks again and have a marvelous day!

maybe something like this:

SELECT
    SUBSTRING(UPPER(t.title), 1, 1) AS letter
FROM
    exp_weblog_titles AS t
WHERE
    t.weblog_id='3'
GROUP BY letter
ORDER BY letter ASC;

note that this may have horrible performance if you have a ton of entries.  hard to say without having something to test it against.  Also it currently doesn’t look for the first alphanumeric character… so if your entry starts with punctuation or anything else you’ll get a link to it… anything like ()@”’{}!#$%^&* etc.

Ty that’s amazing… I love what you can do with raw power of SQL. Something I obviously need to learn more about.

❓  OK, so now I would need to join with the categories table again if I wanted to retain the entries category info?

I used this for testing the navigation:

<a href="http://{path=retail/alpha/{segment_3}/{segment_4}/{letter}/P0}">{letter}</a>

..And since I’m relying on segments 3 and 4 for category info, the above alpha nav worked for the current category I was on (e.g. “fashion”) but obviously when I tried to access an entry from a different category… it gave no results.

/index.php/retail/alpha/category/fashion/B/P0/

Thanks again for the great assist!!

are you just saying that you need to limit the letters to entries within the current category?

SELECT
    SUBSTRING(UPPER(t.title), 1, 1) AS letter
FROM
    exp_weblog_titles AS t NATURAL JOIN
    exp_category_posts AS p NATURAL JOIN
    exp_categories AS c
WHERE
    t.weblog_id='3' AND
    c.cat_url_title='{segment_4}'
GROUP BY letter
ORDER BY letter ASC;

if that’s not the problem then I guess I’m not following you.

Yes. I think you’re probably following me better than I am!  😉

The idea is to have alpha navigation that would appear on a page that lists All entries (in my case retail stores) regardless of category.

The alpha navigation would also be present once the user has drilled into a specific category, and yes, then it would be limited to entries in that category.

It would be a “nice to have” to list the entire alphabet, with nly the letters with entries being clickable, but if that’s too complicated (don’t want to take up your entire day!), it could just return the letters that have a result; perhaps I could use that for a drop-down menu filter.

Ty thanks again for the assistance. I appreciate it to the max!

in order to list the entire alphabet, I would resort to PHP.  Short of creating a new table with an entry for each number/letter/character you want to show in your pagination there’s nothing to my knowledge that will let you join on each letter of the alphabet.  MySQL doesn’t have a string splitting function and creating one requires stored procedures.

Sounds tricky indeed.

I guess for now I could just have two different templates… the “all entries” template could have the alpha nav using the first nav query you provided, and the “specific category” template could use the second query you so generously provided.

Now I have to try and figure out how to handle titles that start with the word “The”.

This whole alpha-based navigation and filtering is certainly a can of worms, isn’t it?

Thanks again for the generous help! Have a fantastic day!