Thats evil! I would create a view or a procedure in database.
On Wed, Aug 15, 2012 at 6:41 PM, pbreit <pbreitenb...@gmail.com> wrote: > Should I even attempt a complex query like this in DAL? Where do I begin? > > > $q = "SELECT > u.user_id, > u.fname, > u.lname, > u.telephone1, > u.email, > u.is_active, > u.company, > u.is_landlord, > u.last_login, > u.last_login_ip, > u.login_times, > count(DISTINCT p.prop_id) as properties, > count(uu.unit_id) as units, > count(l.lease_id) as leases "; > if (isset($vars['query']) && !is_numeric($vars['query'])) { > $q .= ", MATCH (fname,lname,email,company) > AGAINST ('" . $word_query . "' IN BOOLEAN MODE) as relevance "; > } > > $q .= " FROM users as u > > LEFT JOIN > properties as p > ON > p.user_id = u.user_id AND p.is_active = 1 > LEFT JOIN > units as uu > ON > uu.prop_id = p.prop_id > LEFT JOIN > leases_signed as l > ON > l.unit_id = uu.unit_id AND l.eft_auth = 1 "; > if (isset($vars['query']) && !is_numeric($vars['query']) ) { > $q .= " WHERE > MATCH (u.fname,u.lname,u.email,u.company) > AGAINST ('" . $word_query . "' IN BOOLEAN MODE) > GROUP BY > u.user_id, p.prop_id, uu.unit_id > ORDER BY > relevance DESC "; > } else if (isset($vars['query']) && is_numeric($vars['query'])) { > $q .= " WHERE > u.user_id = " . $vars['query'] . " > GROUP BY > u.user_id, p.prop_id, uu.unit_id "; > } else { > $q .=" GROUP BY > u.user_id > ORDER BY user_id DESC "; > } > $q .= "LIMIT " . $start . ", " . $vars['limit'] ; > > -- > > > > --