Database Query with JDatabase Object

[sazbaztaz]

Hi I have the following regular SQL Query...

Code: Select all

			$query = 'SELECT  a.*
			FROM  yyf3h_bmzr_staff a
			INNER JOIN
			(
				SELECT  clientid, staffid, COUNT(*) totalCount
				FROM    yyf3h_bmzr_relations
				WHERE franchiseeid = ' . $owner . '
				AND status < 9
				GROUP   BY staffid
				having count(case when clientid = ' . $clientid . ' then 1 end) = 0
			) b ON  a.id = b.staffid
			WHERE a.owner = ' . $owner . '
			AND a.status < 9
			AND a.name LIKE \'%' . $search . '%\'
			';

How would this look like using the proper JDatabase object like so...

Code: Select all

		$db =& JFactory::getDBO();
		$q =& $db->getQuery(true);
		$q->select(array('a.*'));
		$q->from($this->db->quoteName('#__bmzr_staff', 'a'));

My problem is that there is another select statement WITHIN the join and I can't find any complex Joomla query examples out there.
Thank you!

[itoctopus]

Please check components/com_content/models/articles.php - there are inner queries there which should be an excellent guide on how to do that (I think there is an inner query for generating the badcats).

[sazbaztaz]

That is actually a good idea. Will do that thank you!