Creating "pagination" links that go by first letter of a name, rather than items per page, in CakePHP (1.2).

The views were complicated by the fact that there were several types of Members. This is one of them:

CREATE VIEW members_by_letter AS
SELECT CASE
WHEN u.user_type_id = 2 THEN LOWER(SUBSTRING(u.last_name,1,1))
WHEN u.user_type_id = 3 THEN LOWER(SUBSTRING(u.organisation_name,1,1))
END AS letter, COUNT(u.id) AS present
FROM users AS u
INNER JOIN user_types AS ut
ON ut.model IN ('Professional', 'Organisation') AND ut.id = u.user_type_id
GROUP BY letter ORDER BY letter;


But this should suffice:

CREATE VIEW members_by_letter AS
SELECT LOWER(SUBSTRING(u.last_name,1,1)) AS letter, COUNT(u.id) AS present
FROM users AS u
GROUP BY letter ORDER BY letter;


/**
 * Members paged by letter
 */
public function index($letter = null)
{
    $letter = is_null($letter) ? 'a' : substr(strtolower((string) $letter), 0, 1);

    $members = $this->Member->findByLetter($letter);

    $alpha_list = Cache::read('alpha_list_members', 'default');

    if (!$alpha_list)
    {
        $alpha_list = $this->Member->getAlphaList();

        Cache::write('alpha_list_members', $alpha_list, 'default');
    }

    $this->set(compact('letter', 'alpha_list', 'members'), false);

    /* render the element only if AJAX
     */
    if ($this->RequestHandler->isAjax())
    {
        Configure::write('debug', 0);
        $this->viewPath = 'elements'.DS.'members';
        $this->render('alpha_list');
    }
}

Don't forget to delete the cache if a new Member comes along or another is deleted.

Model code:

/**
 * This will return an array: the keys are each letter of the alphabet for which
 * there is at least one last name (or organisation name, for some types) in the
 * members list. The member type queried is dependent upon the view name passed in.
 * Returns an array in the form:
 *
 * 'a' => 1, 'b' => 1, etc.
 *
 * Letters for which there are no names are omitted.
 *
 * @return  mixed   an array of letters
 *
 */
function getAlphaList()
{
    /* The result of the query should be an array in the form:
     *   'a' => 1, 'b' => 1, etc.
     * The keys are each letter of the alphabet for which
     * there is at least one last name (or organisation name) in the members list.
     * The value for each key is the number that is present in the table.
     * This array will be merged with the one below, resulting in an array with every
     * letter of the alphabet as the keys, the values of which will determine which
     * letters will be links.
     */
    $default = array(
        'a'=>0,'b'=>0,'c'=>0,'d'=>0,'e'=>0,'f'=>0,'g'=>0,'h'=>0,'i'=>0,'j'=>0,
        'k'=>0,'l'=>0,'m'=>0, 'n'=>0,'o'=>0,'p'=>0,'q'=>0,'r'=>0,'s'=>0,'t'=>0,
        'u'=>0,'v'=>0,'w'=>0,'x'=>0,'y'=>0,'z'=>0
    );

    /* select from view
     */
    $letters = Set::combine(
        $this->query('SELECT * FROM members_by_letter'),
        "{n}.${view_name}.letter",
        "{n}.${view_name}.present"
    );

    /* some of these are not alpha -- remove for now
     */
    foreach($letters as $key => $val)
    {
        if (is_numeric($key)) unset($letters[$key]);
    }

    return am($default, $letters);
}


I can't remember, offhand, what the "some of these are not alpha" was about.

findByLetter() uses this condition:

"lower(left(User.last_name, 1)) = '${letter}'"


The index view includes an element, members/alpha_list.ctp, which laid out the list of Members. That element, in turn, included another, general element, alpha_links.ctp. I did it this way because, as I said, there were several types of members.

echo $this->element(
    'alpha_links',
    array(
        'letter' => $letter,
        'route' => array(
            'controller' => 'members',
            'action' => 'index'
        )
    )
);


alpha_links.ctp:

<?php
/* Creates a series of links, one for each letter of the alphabet where at least one
 * entry in the DB corresponds. Otherwise, the letter is represented in a span.
 */
if (isset($alpha_list) && isset($route))
{
?>
<div class="AlphaLinks">
    <h4>List Alphabetically</h4>
<?php
    foreach($alpha_list as $key => $val)
    {
        /* are there members for this letter?
         */
        if ($val > 0)
        {
            /* is this the current letter?
             */
            if (isset($letter) && $key == $letter)
            {
?>
    <span id="current"><?= $key ?></span>
<?php
            }
            else
            {
                echo $html->link(
                    $key,
                    am($route, array('letter' => $key)),
                    array('title' => $key, 'rel' => $key)
                );
            }
        }
        else
        {
            /* no names for this letter
             */
?>
    <span><?= $key ?></span>
<?php
        }
    }
?>
</div>
<?php
}
?>