[web2py] Complex query in DAL?

[pbreit]

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'] ;

--