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