Want to select records due to their status in a table using mysql
good guys i have a problem of selecting all records that are inactive or active in the database but if i select all estates and all statuses it will return all estates and their other status, active or inactive, but if i select all estates and active status, it will return all estates and show that they are all active, the same happens with inactive, but when I select all estates and inactive, I want it to display all inactive objects that go along with active ones.
my code:
if estate_id == ''
if current_user.is_admin?
filter_estates = "e.id IS NOT NULL"
elsif current_user.has_managing_agent?
estates = current_user.business.approved_estates.collect{|e| e.id }
filter_estates = "e.id IN (#{estates})"
elsif current_user.estate_manager? || current_user.board_member?
filter_estates = "e.id = #{current_user.estate_id}"
end
else
filter_estates = "e.id = #{estate_id}"
end
if status == 'All status'
filters_status = "select e.active"
elsif status == 'Inactive'
filters_status = "select * from estates e where e.active = false limit 1"
else
filters_status = "select * from estates e where e.active = true limit 1"
end
# Query the Database.
if estate_id == ''
@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, "+
"(#{filters_status}) 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} "
else
@query = Estate.find_by_sql"SELECT (e.name) as estate_name, "+
"(select g.name from governing_bodies g where e.governing_body_id = g.id AND #{filter_estates}) 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 e.id = #{estate_id}) 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 WHERE e.id = #{estate_id} group by e.name"
I am using ruby on rails, so this code is in the model.
thanx in advance for your help.
This code is extremely "unusable" to be polite. I don't think you understand how SQL works, which is a significant obstacle to a situation like this - sorry, no sugar needed :-(
You need to apply some conditions to the query as a whole, not to subqueries, which only incorrectly populate the overarching query columns. I can also see signs that you are trying to cram three dimensions of data into a table with two dimensions.
Except that you are rewriting your code, put it back to basics and create the simplest query that returns you the simplest complete result. For example, a query that returns all estates, that returns all active estates, etc. Your LEFT JOIN looks fine, but you need to additionally select the active / inactive rows WHERE in this main query, and not do it in subqueries. You should also look at other ways to access "stands" etc., since you can't just cram them into one column. Personally, I would probably join the WHERE initially, being lazy.
Consider this non-working but psuedo SQL as a kind of hint to get at least:
select e.*, g.name FROM estates e, governing_bodies g WHERE g.id = e.governing_body_id AND e.active = true;
This will only give you active objects and the corresponding controlname.
a source to share