Hi guy i am selecting my active record from my table estates and all my other record are fine but the active record give me errors my code is
@query = Estate.find_by_sql"SELECT (e.name) as estate_name, g.name as governing_body,"+ "(select count() from stands s where s.estate_id = e.id AND #{filter_estates}) as total_stands, "+ "(select e.active from estates e where e.active = true AND #{filter_estates}) as estate_status, "+ "(select count() from services sp where sp.estate_id = e.id AND #{filter_estates}) as service_providers,"+ "(select count(*) from approved_vendors av where av.estate_id = e.id AND #{filter_estates})as vendors"+ " FROM estates e LEFT JOIN governing_bodies g on e.governing_body_id = g.id AND #{filter_estates} "
and i am getting an error.
(Mysql::Error: Subquery returns more than 1 row: SELECT (e.name) as estate_name, g.name as governing_body,(select count() from stands s where s.estate_id = e.id AND e.id IS NOT NULL) as total_stands, (select e.active from estates e where e.active = true AND e.id IS NOT NULL) as estate_status, (select count() from services sp where sp.estate_id = e.id AND e.id IS NOT NULL) as service_providers,(select count(*) from approved_vendors av where av.estate_id = e.id AND e.id IS NOT NULL)as vendors FROM estates e LEFT JOIN governing_bodies g on e.governing_body_id = g.id AND e.id IS NOT NULL ):
and i want to display a all estates which are active and inactive.
please guys,how can i solve this problem. i am using Mysql database.
It looks like you might have a problem with your third line:
(select e.active from estates e where e.active = true AND #{filter_estates}) as estate_status
The lines above and below that use an aggregate, so they only return one row, this could (probably is) returning multiple rows and it doesn't know which one to assign to estate_status.
You could probably just change that line to:
e.active as estate_status