We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Paginator with PHQL or Raw

So there are 3 addaptors for paginator Model, NativeArray and QueryBuilder. Can any of these be used to paginate PHQL or Raw MySql queries? I was under the assumption that QueryBuilder was just an alternative to PHQL and would produce the same result, so I was hoping the paginator would work with PHQL as it does with QueryBulder.



9.5k
edited Jun '16

Hi,

I am not sure what you are trying to achieve. Is it to support your front-end by having the back-end do queries in your table that specify limit and starting index? If so, you can do it this way:

Model::find([
    <filter_here>,
    'offset'=> <start_index>,
    'limit'=> <limit_size>, 
]);

This code is not tested but it should be around there. Let me know if that works for you. You can edit it in PHQL or MySQL queries, whatever suits you. If it is another thing that you need to achieve, please advise.

Cheers,



20.4k

I am talking about Phalcon\Paginator and Phalcon\Paginator\Adaptor and wheather it can work with PHQL and/or raw queries. It has an adaptor for models and that works well, and it has an adaptor for query builder and that works well, and it has a native array adaptor which works, but would not be efficient in this case. I have some queries that have joins, sub queries etc. that are too complex for Model::find() and therefor need to be written in sql. However, I would like to use Phalcon\Paginator to page through the results of the queries rather than have to create a separate pager just for PHQL and raw.



145.0k

Joins and sub queries work in query builder.



20.4k

Yes, I actually got this particular query to work with query builder after a lot of messing about, but as soon as I add pagination, the paginator some how breaks the query! I posted the issue on github.

I some times find that using query builder on some more complex queries requires a lot of adaptation to make it work. For example, ORDER BY CASE WHEN... doesn't seem to work (although I read some where that it is now supported) and I have to use IF instead. So some times it's easier to actually write out the query as a string in PHQL or raw. In some cases it's more semantic to do that too, especially if a database god later wants to take a look at a query. So it would be good to be able to use paginator with such queries.



145.0k

Case is working, there is just other syntax. You need to use case like this for example:

CASE Admin.id WHEN :adminId: THEN User.email ELSE NULL END as email'


125.7k

If you're doing a big fancy query in PHQL or raw SQL, I think the easiest pagination solution will be to just do it yourself with a limit & offset clause. The paginator works great when you're using the QueryBuilder or some other Phalcon construct, but when you're not using them, I don't think it adds a whole lot.



20.4k

Thanks Dylan. The custom paginator is the rout I've had to take. However, I did convert a query to queryBuilder so that I could paginate it, but strangly as soon as I added the paginator, it would break the query! I posted this issue on GH, but no replies - see here. Have you any ideas on that?



629

Thanks