2
"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

4

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');
0

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");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.