Re: Paging with aplhabetical search, not only by page number

[grigri]

 Member::findByLetter() uses this condition:

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

 I avoided using LIKE because I only want to look at the first letter, anyway.

This sounds like a bad idea for large tables. Assuming you have an
index on the `last_name` field, if you use this...

WHERE `Member`.`last_name` LIKE 'r%'

... it will use the index (it's a range query) and result in speedy
lookups

Using this...

WHERE LOWER(LEFT(`Member`.`last_name`, 1)) = 'r'

...will result in a full table scan, which is horribly inefficient.

(You can use EXPLAIN SELECT to see for yourself)

Case-sensitivity isn't really an issue because you can either set the
collation for the whole table as CI, or just that index. I can't
recall a single instance where the case insensitivity of a database
collation caused an issue, in any of my real-world projects. YMMV, of
course.

hth
grigri

On Apr 1, 4:38 pm, brian bally.z...@gmail.com wrote:
 I have something which does that. But it displays all names for a
 given letter, rather than using a paging limit. I can't figure out a
 clean way to do both.

 public function index($letter = null)
 {
         $letter = is_null($letter) ? 'a' : strtolower((string) $letter);

         $this-set('members', $this-Member-findByLetter($letter));
         $this-set('alpha_list', $this-Member-getAlphaList());
         $this-set('letter', $letter);

         /* render the element only so we don't end up with a nested mess
          */
         if ($this-RequestHandler-isAjax())
         {
                 Configure::write('debug', 0);
                 $this-viewPath = 'elements'.DS.'members';
                 $this-render('alpha_list');
         }

 }

 Member::findByLetter() uses this condition:

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

 I avoided using LIKE because I only want to look at the first letter, anyway.

 The rest is a bit more complicated, so I've posted it here:

 http://bin.cakephp.org/view/1433101916

 This is all based on some stuff I had with a non-Cake site. That one
 actually had a lot of the heavy lifting done in the DB using pl/python
 (Postgres).

 On Wed, Apr 1, 2009 at 3:52 AM, meximex meixne...@gmail.com wrote:

  hi,
  if i have an application with a list of 1000 items and want to jump to
  the page of the items starting with M. how could y do that?

  do i have to implement that by my self?

  if you would do it by yourself, how would you do it?

  create a loop for every letter and an sql that counts the items before
  that letter?

  isnt there something better in mysql?

  thx for your help!
  meximex
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to 
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---

Re: Paging with aplhabetical search, not only by page number

[Miles J]

You would have to do it yourself in the conditions of the $paginate
property. Im not sure if this will work, but something like:

$this-paginate['Model']['conditions']['Model.name LIKE'] = '%'.
$letter;
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to 
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---

Re: Paging with aplhabetical search, not only by page number

[brian]

I have something which does that. But it displays all names for a
given letter, rather than using a paging limit. I can't figure out a
clean way to do both.

public function index($letter = null)
{
$letter = is_null($letter) ? 'a' : strtolower((string) $letter);

$this-set('members', $this-Member-findByLetter($letter));
$this-set('alpha_list', $this-Member-getAlphaList());
$this-set('letter', $letter);

/* render the element only so we don't end up with a nested mess
 */
if ($this-RequestHandler-isAjax())
{
Configure::write('debug', 0);
$this-viewPath = 'elements'.DS.'members';
$this-render('alpha_list');
}
}

Member::findByLetter() uses this condition:

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

I avoided using LIKE because I only want to look at the first letter, anyway.

The rest is a bit more complicated, so I've posted it here:

http://bin.cakephp.org/view/1433101916

This is all based on some stuff I had with a non-Cake site. That one
actually had a lot of the heavy lifting done in the DB using pl/python
(Postgres).

On Wed, Apr 1, 2009 at 3:52 AM, meximex meixne...@gmail.com wrote:

 hi,
 if i have an application with a list of 1000 items and want to jump to
 the page of the items starting with M. how could y do that?

 do i have to implement that by my self?

 if you would do it by yourself, how would you do it?

 create a loop for every letter and an sql that counts the items before
 that letter?

 isnt there something better in mysql?


 thx for your help!
 meximex
 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to 
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---