Paginate from a difficult sql query


#1

Hello,

I have 2 tables

table colors :
±---------------------+
| id | user_id | name |
±---------------------+
| 1 | 1 | test1 |
| 2 | 2 | test2 |
| 3 | 3 | test3 |
±---------------------+

table users :
±---------------------+
| id | enabled | name |
±---------------------+
| 1 | 1 | bob |
| 2 | 1 | joe |
| 3 | 0 | mike |
±---------------------+

I need to get the rows of table colors where the “user_id” in the table
user = 1

rows = Color.find_by_sql(“SELECT colors.* FROM colors,users WHERE
colors.user_id=users.id AND enabled=1 ORDER BY id DESC”)

So the return is :

±---------------------+
| id | user_id | name |
±---------------------+
| 2 | 2 | test2 |
| 1 | 1 | test1 |
±---------------------+

Everything is good, but I want to have this return directly in a
paginate. Like :

@paginate_pages, @colors = paginate :colors,

:per_page => 6,
:order
=> “id DESC”,

:conditions => " ??? " ,
: joins
=> " ??? "
:other
=> " ??? "

But I don’t know how to write it. And if it’s possible.

If it’s not possible. How to set the rows array from Color.find_by_sql
into a paginator ?

Can someone help me please ?
Thanks


#2

Wilson B. helped me out with a similar request. Check this site:

http://bigbold.com/snippets/posts/show/389


#3

Works perfect, thanks !
:slight_smile: