0

I am trying to write a query using Joomla's query building methods, but I cannot manage to write the subquery into the query.

Here is my desired raw sql with php variables:

"SELECT track_type,
       track_city,
       SUM(count) as COUNT_CITY,
       A.track_country,
       B.COUNT_COUNTRY,
       MAX(track_date) as ToDate,
       MIN(track_date) as FromDate,
       C.name
FROM azh_banner_tracks AS A
LEFT JOIN (
    SELECT SUM(count) as COUNT_COUNTRY,
           track_country
    FROM azh_banner_tracks
    WHERE banner_id = '" . (int) $bannerId . "'
      and track_type=2
    GROUP BY track_type,
             track_country
) AS B on A.track_country = B.track_country
INNER JOIN (
    SELECT name, id
    from azh_banners
    where id = '" . (int) $bannerId . "'
) as C on A.banner_id = C.id
WHERE banner_id = '" . (int) $bannerId . "'
GROUP BY track_type,
         A.track_country,
         track_city,
         COUNT_COUNTRY
ORDER BY COUNT_COUNTRY DESC,
         COUNT_CITY DESC,
         track_city"
4
  • Can you share what you have tried so far? – Grant G Mar 18 at 13:03
  • Welcome to JSE, @Azhar. I agree with Grant, we would like to see some proof of research and toil. In the absence of these details, it looks like you are expecting this community to do your development for you for free. Please include your best coding attempt (even if it is far from successful) and show us links to documentation, JSE, Joomla Forum or any other tutorials that you have read. For best support, include a db fiddle demo link. Please have a read through these pages to get some good insights: joomla.stackexchange.com/search?q=user%3A12352+subquery – mickmackusa Mar 18 at 13:39
  • @Azhar by providing a db fiddle with sufficient sample data and your desired result set we might be able to guide you to a simpler query. Right now, your sql is rather hard to follow. – mickmackusa Mar 18 at 13:48
  • 1
    Why does azh_banners need to be joined as a subquery? Why can't it be directly inner joined on A.banner_id = C.id? – mickmackusa Mar 19 at 14:01
0

Simplest effort, but not recommended:

$db = JFactory::getDbo();
$query = 'SELECT track_type, track_city, SUM(count) as COUNT_CITY, A.track_country, B.COUNT_COUNTRY, MAX(track_date) as ToDate, MIN(track_date) as FromDate, C.name FROM azh_banner_tracks AS A LEFT JOIN (SELECT SUM(count) as COUNT_COUNTRY, track_country FROM azh_banner_tracks WHERE banner_id = ' . (int) $bannerId . ' and track_type=2 GROUP BY track_type, track_country) AS B on A.track_country = B.track_country INNER JOIN (SELECT name, id from azh_banners where id = ' . (int) $bannerId . ') as C on A.banner_id = C.id WHERE banner_id = ' . (int) $bannerId . ' GROUP BY track_type, A.track_country, track_city, COUNT_COUNTRY ORDER BY COUNT_COUNTRY DESC, COUNT_CITY DESC, track_city';
$db->setQuery($query);
$result = $db->loadAssoc();

I suggest you familiarise yourself with Selecting data using JDatabase, particularly around to properly escape your SQL query. The above is not portable unless you recreate your database with the exact same table prefix.

0

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.