Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Limit is ignored when joins is specified

#6374

Limit is being ignored in the following case. When calling to_sql on the ARel object, it returns the correct SQL. Then, calling that query with find_by_sql returns the correct count.

ruby-1.9.2-p136 :005 > Animal.limit(1).joins(:lot).where("animals.location_id" => 20).where("lots.species" => "Horse").count
=> 229

ruby-1.9.2-p136 :006 > Animal.limit(1).joins(:lot).where("animals.location_id" => 20).where("lots.species" => "Horse").to_sql
=> "SELECT animals.* FROM animals INNER JOIN lots ON lots.id = animals.lot_id WHERE animals.location_id = 20 AND lots.species = 'Horse' LIMIT 1"

ruby-1.9.2-p136 :007 > Animal.find_by_sql("SELECT animals.* FROM animals INNER JOIN lots ON lots.id = animals.lot_id WHERE animals.location_id = 20 AND lots.species = 'Horse' LIMIT 1").count
=> 1

Reported by James · February 4th, 2011 @ 04:45 PM

State: invalid
Milestone: none
Assigned to: Aaron Patterson Aaron Patterson
Importance: Low

Activity

  1. James
    James

    Turns out it has nothing to do with the joins.

    Animal.limit(1).count
    => 1297

    Is that expected behavior?

    Found this by trying to do (essentially) Animal.limit(1).update_all(...) -- not surprisingly based on the above, it's updating every record.

    February 4th, 2011 @ 05:22 PM

  2. Jan Xie
    Jan Xie

    Model.limit(1).count generate correct sql (select count(*) from model limit 1), it's mysql's behavior:

    mysql> select count(*) from knowledges limit 1;
    +----------+
    | count(*) |
    +----------+
    |      842 |
    +----------+
    1 row in set (0.00 sec)
    

    February 5th, 2011 @ 01:04 PM

  3. James
    James

    Yeah -- that makes perfect sense -- forgot that count and size actually modify the query and return a single row.

    So what about my specific case with update_all? It builds the update_all query including the "wheres" and discards the limit:
    Animal.limit(1).joins(:lot).where("animals.location_id" => 20).where("lots.species" => "Horse").update_all(:location_id => 20)

    AREL (2.7ms) UPDATE animals INNER JOIN lots ON lots.id = animals.lot_id SET location_id = 20 WHERE animals.location_id = 20 AND lots.species = 'Horse'

    February 5th, 2011 @ 03:55 PM

  4. Aaron Patterson
    Aaron Patterson
    • State changed from new to invalid
    • Importance changed from to Low

    James,

    order must also be supplied if you want the limit to work on updates.

    February 25th, 2011 @ 08:02 PM