Joomla 2.5, how to use "HAVING" using Query Object

Question

"SELECT *, ((ACOS(SIN($lat * PI() / 180) * SIN(latitude * PI() / 180) + COS(78 * PI() / 180) * COS(latitude * PI() / 180) * COS((87 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance "
            . "FROM `#__content` AS `b` "
            . "RIGHT JOIN `#__jreviews_content` AS `a` ON (`a`.`contentid` = `b`.`id`) "
            . "LEFT JOIN `#__categories` AS `c` ON (`b`.`catid` = `c`.`id`) "
            . "LEFT JOIN `#__jreviews_media` AS `m` ON (`m`.`listing_id` = `b`.`id` AND `m`.`main_media` = 1 AND `m`.`media_type` = 'photo') "
            . "WHERE 1 = 1 AND (`c`.`title`  LIKE '%Stores%' OR `c`.`title`  LIKE '%Dining%' OR `c`.`title`  LIKE '%Events%') AND `a`.`jr_latitude`<> 0.0000000 AND `c`.`title`<> 'Archives' "
            . "HAVING distance<='50' "
            . "ORDER BY distance ASC"

I managed to write the query with Query Object without HAVING

$query->select(array('*', '((ACOS(SIN($lat * PI() / 180) * SIN(latitude * PI() / 180) + COS(78 * PI() / 180) * COS(latitude * PI() / 180) * COS((87 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance'))
                ->from($content)
                ->join('RIGHT', $jreviews_content . ' ON (' . $db->quoteName('a.contentid') . ' = ' . $db->quoteName('b.id') . ')')
                ->join('LEFT', $categories . ' ON (' . $db->quoteName('b.catid') . ' = ' . $db->quoteName('c.id') . ')')
                ->join('LEFT', $media . ' ON (' . $db->quoteName('m.listing_id') . ' = ' . $db->quoteName('b.id') . ' AND ' . $db->quoteName('m.main_media') . ' = 1 AND ' . $db->quoteName('m.media_type') . ' = "photo")')
                ->where($conditions);

Please anyone help me to write the HAVING in this Query Object

Answer 1

If you are using having clause without group_by then it will act just like where clause.

JDatabaseQuery class provides method for having clause.

Try this

$query->having('distance <=50');

Answer 2

I'd like to add some refinements to the posted query...

• While valid, I recommend building your conditional filters in the WHERE clause versus the JOIN's ON expression -- it will be cleaner to read this way.
• Rather than writing 3 separate LIKE conditions, it will be more brief to write a single REGEXP condition condensing the 3 targeted substrings.
• There is no benefit to 1 = 1
• I recommend writing your "less expensive" conditions before the "heavier" ones.
• I think it is more intuitive to use != when comparing a string, whereas <> is more appropriate when comparing numeric values.
• ON expressions don't need to be wrapped in parentheses.
• $lat in the SELECT should be type cast as a float for best security practices.
• If you are going to use arbitrary table aliases, you should start from a and descend from there versus using a broken order.
• ASC is the default sorting direction, so it can be removed.

I am blindly going to trust your math and I can't be entirely sure about the correctness of your choices of JOINs without having some sample data and knowing the desired output, so I'll leave those aspects alone.

New RAW SQL:

SELECT *, (ACOS(SIN($lat * PI() / 180) * SIN(latitude * PI() / 180) + COS(78 * PI() / 180) * COS(latitude * PI() / 180) * COS((87 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 AS distance
FROM #__content a
RIGHT JOIN #__jreviews_content b ON b.contentid = a.id
LEFT JOIN #__categories c ON a.catid = c.id
LEFT JOIN #__jreviews_media d ON d.listing_id = a.id
WHERE d.main_media = 1
  AND d.media_type = 'photo'
  AND c.title != 'Archives'
  AND b.jr_latitude <> 0.0000000
  AND c.title REGEXP 'Stores|Dining|Events'
HAVING distance <= 50  
ORDER BY distance

HAVING is necessary here because the distance column alias is not yet available when WHERE logic is implemented.

PHP/Joomla Equivalent (untested):

$query = $db->getQuery(true)
    ->select([
        "*",
        "(ACOS(SIN(" . (float)$lat . " * PI() / 180) * SIN(latitude * PI() / 180) + COS(78 * PI() / 180) * COS(latitude * PI() / 180) * COS((87 - longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 AS distance"
    ])
    ->from("#__content a")
    ->rightJoin("#__jreviews_content b ON b.contentid = a.id")
    ->leftJoin("#__categories c ON a.catid = c.id")
    ->leftJoin("#__jreviews_media d ON d.listing_id = a.id")
    ->where([
        "d.main_media = 1",
        "d.media_type = " . $db->q("photo"),
        "c.title != " . $db->q("Archives"),
        "b.jr_latitude <> 0.0000000",
        "c.title REGEXP " . $db->q("Stores|Dining|Events")
    ])
    ->having("distance <= 50")
    ->order("distance");