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.

SQL syntax error while executing count with has_many :through and named_scopes

#2375

Appl.rb:


  named_scope :confirmed, :conditions => {:state => "confirmed"}
  has_many :tags, :through => :taggings
  has_many :taggings, :dependent => :destroy

Tag.rb:


  has_many :appls, :through => :taggings

And now in script/console:


= Tag.find 65
  Tag Load (0.6ms)   SELECT * FROM `tags` WHERE (`tags`.`id` = 65) 
=> #<Tag id: 65, name: "DO DUPY!!!", created_at: "2009-03-04 11:11:15", updated_at: "2009-03-04 11:11:15", company_id: 1, color: "f1f5ec", font_color: "006633", taggings_count: 20>
>> t.appls.size
  SQL (0.7ms)   SELECT count(*) AS count_all FROM `appls` INNER JOIN `taggings` ON `appls`.id = `taggings`.appl_id WHERE ((`taggings`.tag_id = 65)) 
=> 20
>> t.appls.confirmed.size
  SQL (0.0ms)   Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS count_appls_all FROM `appls` INNER JOIN `taggings` ON `appls`.id = `taggin' at line 1: SELECT count(`appls`.*) AS count_appls_all FROM `appls` INNER JOIN `taggings` ON `appls`.id = `taggings`.appl_id WHERE (((`appls`.`state` = 'confirmed') AND ((`taggings`.tag_id = 65))) AND ((`taggings`.tag_id = 65))) 
ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS count_appls_all FROM `appls` INNER JOIN `taggings` ON `appls`.id = `taggin' at line 1: SELECT count(`appls`.*) AS count_appls_all FROM `appls` INNER JOIN `taggings` ON `appls`.id = `taggings`.appl_id WHERE (((`appls`.`state` = 'confirmed') AND ((`taggings`.tag_id = 65))) AND ((`taggings`.tag_id = 65))) 

While t.appls.confirmed.to_a.size gives 20.

Error is in count(appls.), when I change it manually to count() it works well and gives the expected result.

Reported by Bartosz Pietrzak · March 30th, 2009 @ 11:42 AM

State: duplicate
Milestone: 2.x
Assigned to: nobody
Importance: none

Activity

  1. Bartosz Pietrzak
    Bartosz Pietrzak

    got an formatting error in last paragraph.

    error is in:

    
    count(`appls`.*)
    

    and correct version is:

    
    count(*)
    

    March 30th, 2009 @ 11:43 AM

  2. CancelProfileIsBroken