Want to select records due to their status in the table using mysql

Question

good morning guys, i have a problem of selecting all records which are inactive or active in a database, but if i select all estates and all status it returns all the estates and their different status, active or inactive, but if i select all estates and active status it returns all the estates and display that they are all active, same goes with inactive, but when i select all estates and inactive i want it to display all estates that are inactive same goes with the active.

my code is:

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 a model.

thanx in advance for your help..

Answer 1

That code is extremely "ungood", to be polite. I don't think you understand how SQL works, which is a significant impediment with a situation like this - sorry but there's no need to sugarcoat :-(

You need to be applying some of your conditions to the query overall, not to subqueries that only incorrectly populate the columns of the overarching query. I also see indications you're trying to cram three dimensions of data into a two dimension table..

Short of rewriting your code for you, take it back to basics and produce the simplest query that returns the simplest complete result for you. For example, a query that returns all estates, one that returns all active estates, and so on. Your LEFT JOIN seems okay, but you need an additional WHERE on that main query to select active/non-active rows rather than by doing it in subqueries. You also need to look at other ways to access "stands" etc, as you can't just cram them into a single column. Personally, I'd probably do the join in the WHERE initially, being lazy.

Consider this non-working but psuedo SQL as a sort of hint to get going 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 get you the active estates only and the associated governing_body name.