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?
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);
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);
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
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.
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.
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.
Im trying to build my own custom paginator and I get this error in debug mode. I edited the path so please dont comment on that:
Fatal error: Class 'AkPaginator' not found in /path/to/controller/my_controller.php on line 75
Line 75 of my_controller is:
$this->comment_pages = new AkPaginator($this,$num_of_recs,&$this->params['page']);
Why does it not find AkPaginator?
Nevermind. If anyone else has this issue, be sure to do this BEFORE you try to create a new AkPaginator object:
require_once(AK_LIB_DIR.DS.'AkActionController'.DS.'AkPaginator.php');
1 to 10 of 10