paginate_by_sql auto adds or removes weird LIMIT from query #300

Closed
YavorIvanov opened this Issue Mar 11, 2013 · 1 comment

Projects

None yet

2 participants

@YavorIvanov

In the following source the ORDER and the LIMIT are removed from the query when making the count. This results in a bad query logic and error.

If you put LIMIT at the end of the query that the ORDER BY and LIMIT 1 are not being touched but the end of the query contains LIMIT LIMIT which breaks it. This results in an invalid query syntax.

query = "SELECT inner_tags AS name, items.*
               FROM items, (
                 SELECT tags.name AS inner_tags,
                        tags.id AS upper_tag_id,
                        (
                         SELECT items.id
                           FROM tags, taggings, items
                          WHERE taggings.tag_id = tags.id
                            AND taggings.taggable_id = items.id
                            AND taggings.taggable_type = 'Item'
                            AND taggings.context = 'tags'
                            AND items.category_id = #{@category.id}
                            AND tags.id = upper_tag_id
                       ORDER BY taggings.id DESC
                          LIMIT 1
                        ) AS item_id
                   FROM tags, taggings, items
                  WHERE taggings.tag_id = tags.id
                    AND taggings.taggable_id = items.id
                    AND taggings.taggable_type = 'Item'
                    AND taggings.context = 'tags'
                    AND items.category_id = #{@category.id}
               GROUP BY upper_tag_id
              ) AS tag_query
              WHERE tag_query.item_id = items.id"
    @items = Item.paginate_by_sql(query, :page => params[:page], :per_page => 60)
Owner
mislav commented Mar 11, 2013

paginate_by_sql is for simpler use cases, preferrably with no nested queries. Since neither will_paginate nor Active Record have a built-in SQL parser, we can't really edit complex queries with confidence.

You should refrain from using paginate_by_sql and do the queries manually. You can use WillPaginate::Collection.create to aid you with limit/offset calculations and to return a paginated collection in the end.

@mislav mislav closed this Mar 11, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment