Not signed in (Sign In)
Welcome Guest!
Want to take part in these discussions? If you have an account, sign in now.
If you don't have an account, apply for one now.

Akelos Programming: findBySQL & pagination: do they get along?

Bottom of Page

1 to 7 of 7

  1.  
    • CommentAuthorThijs
    • CommentTimeApr 3rd 2008
     

    Is it possible the use pagination with a findBySQL() ?

    I have this find

    $this->users =& $this->User->findbySQL('SELECT *
       FROM users, hoodmemberships, hoods
       WHERE users.id = hoodmemberships.user_id
       AND hoods.id = hoodmemberships.hood_id
       AND hoods.country_id = '.$_SESSION['user_country'].'
       AND hoods.region_id = '.$_SESSION['user_region']);

    since it's a query in 3 tables I cannot send it to getPaginator() as a 'count_conditions'

    Is there a way to send an SQL statement through the paginator?

    •  
      CommentAuthorbermi
    • CommentTimeApr 5th 2008
     

    I'm afraid you'll need to manually instantiate a custom paginator and use

    $this->user_pages = new AkPaginator($this,
  $number_of_records,
  $items_per_page, 
  'page');


$this->User->findBySql($sql, $limit, $offset);
    • CommentAuthorThijs
    • CommentTimeApr 5th 2008 edited
     

    Okay, not a big problem. Got it working.

    One remark though: findBySql() does not seem to take any more $limit and $offset parameters. I got this warning:

    Notice: DEPRECATED WARNING: You're calling AR::findBySql with $limit or $offset parameters. This has been deprecated. in /Users/thijs/akelos_framework/lib/Ak.php on line 82

    So my solution is something like this:

    $sql = 'FROM users, hoodmemberships, hoods
        WHERE users.id = hoodmemberships.user_id
        AND hoods.id = hoodmemberships.hood_id
        AND hoods.country_id = '.$_SESSION['user_country'].'
        AND hoods.region_id = '.$_SESSION['user_region'].'
        GROUP BY users.id ';

$number_of_records = count($this->User->findBySql('SELECT count(*) '.$sql));

$this->user_pages = new AkPaginator($this,$number_of_records,$items_per_page,&$this->params['page']);

$this->users = $this->User->findBySql('SELECT 
               users.id, users.first_name, users.last_name, users.zip, users.city '.
               $sql.$sort_by.
               ' LIMIT '.(($this->params['page']-1)*$items_per_page).','.$items_per_page);
    • CommentAuthorbjmg
    • CommentTimeApr 17th 2008
     

    I really ask me (and you) why limit and offset are deprecated in findBySql. I don't see a suitable solution to set those values. There is a function addLimitAndOffset is also used in findBySql but I really think that it would be huge hack if I would use that function in the controller. If I am correct then findBySql should work with an array of parameters like find does. What is your suggested solution for that?

    Thanks

    • CommentAuthorKaste
    • CommentTimeApr 17th 2008
     

    findBySql() takes a sql-query and variables for late binding as arguments. just what the name of the method suggest.

    I think findBySql should'nt be used inside a controller at all. so I would refactor this code and move it to a model.

    lastly, I think pagination should be a acts_as-addon to the ActiveRecord. In the end it only restricts a set.

    • CommentAuthorThijs
    • CommentTimeApr 18th 2008
     

    I think findBySql should'nt be used inside a controller at all. so I would refactor this code and move it to a model.

    You’re right, Kaste. (I'm still trying to get rid of old programming habits)

    Another question on findBySql():
    Would it be possible to get left join columns included in the object it returns?
    I tried sending the same SQL into it as a normal find() with an 'include' would generate.. but that doesn't seem to work.

    • CommentAuthorKaste
    • CommentTimeApr 18th 2008
     

    no, we use a simplified instantiation and parsing of the result-set on findBySql and very complicated one on find with include.

    this has a link to the problem of so-called deep eager loading you try to circumvent here.

1 to 7 of 7

  1.  
Add your comments
    Username Password
  • Format comments as
 
Back to Discussions Top of Page