Subqueries in SELECT | Go ahead... Laugh. Topic is solved

For Joomla! 3.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Post Reply
DanHouston
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Tue Oct 06, 2015 7:12 pm

Subqueries in SELECT | Go ahead... Laugh.

Post by DanHouston » Tue Nov 26, 2019 11:40 pm

A couple of years back I created a view in phpMyAdmin and used the data therein to make a module that relied on a group id. the view was relied on a fat chunk of code but displaying it was much because I had a simple table to pull records from. that had information from several components

Anyway, today the view broke due to this error: 1242 - Subquery returns more than 1 row

When viewing in phpmyadmin, the view works until it encounters whatever is breaking it. then i get the same 1242 - Subquery returns more than 1 row error. in other words, I can click the view and flip through the records until it breaks. There doesn't seem to be a way for me to test what is happening there.

So I figure, instead of creating a view, I can just run the query in the module and then they will work for the modules that don't encounter the bug (in my crazy theory)

Now this relies on several subqueries... again, I'm pulling from many db tables.

But joomla says my markup is off. I was hoping someone could tell me how my markup is off, as well as if my approach can be improved upon. All advice is valued. Cheers!

Code: Select all

<?php
defined( '_JEXEC' )or die( 'Restricted access' );

$selectedgroupid = $params->get('groupid');

// Summon User
$user = JFactory::getUser();

//Get User ID
$userid = $user->id;

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery( true );

$companyQuery = $db->getQuery(true);
// Create the base subQuery select statement.
$companyQuery->select('cfv.value')
    ->from($db->quoteName('#_community_fields_values', 'cfv'))
    ->join('INNER', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('cfv.u') . ' = ' . $db->quoteName('u.id'))
    ->where($db->quoteName('u.id') . ' = ' . $db->quoteName('#_community_groups_members.memberid') . ' and ' . $db->quoteName('cfv.field') . ' = ' . $db->quote('16'));
    
$titleQuery = $db->getQuery(true);
$titleQuery->select('cfv.value')
    ->from($db->quoteName('#_community_fields_values', 'cfv'))
    ->join('INNER', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('cfv.u') . ' = ' . $db->quoteName('u.id'))
    ->where($db->quoteName('u.id') . ' = ' . $db->quoteName('#_community_groups_members.memberid') . ' = ' . $db->quote('18'));
$professionQuery = $db->getQuery(true);
$professionQuery->select('cfv.value')
    ->from($db->quoteName('#_community_fields_values', 'cfv'))
    ->join('INNER', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('cfv.u') . ' = ' . $db->quoteName('u.id'))
    ->where($db->quoteName('u.id') . ' = ' . $db->quoteName('#_community_groups_members.memberid') . ' = ' . $db->quote('17'));
$mobileQuery = $db->getQuery(true);
$mobileQuery->select('cfv.value')
    ->from($db->quoteName('#_community_fields_values', 'cfv'))
    ->join('INNER', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('cfv.u') . ' = ' . $db->quoteName('u.id'))
    ->where($db->quoteName('u.id') . ' = ' . $db->quoteName('#_community_groups_members.memberid') . ' = ' . $db->quote('6'));
$officeQuery = $db->getQuery(true);
$officeQuery->select('cfv.value')
    ->from($db->quoteName('#_community_fields_values', 'cfv'))
    ->join('INNER', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('cfv.u') . ' = ' . $db->quoteName('u.id'))
    ->where($db->quoteName('u.id') . ' = ' . $db->quoteName('#_community_groups_members.memberid') . ' = ' . $db->quote('7'));
$emailQuery = $db->getQuery(true);
$emailQuery->select('cfv.value')
    ->from($db->quoteName('#_community_fields_values', 'cfv'))
    ->join('INNER', $db->quoteName('#__users', 'u') . ' ON ' . $db->quoteName('cfv.u') . ' = ' . $db->quoteName('u.id'))
    ->where($db->quoteName('u.id') . ' = ' . $db->quoteName('#_community_groups_members.memberid') . ' = ' . $db->quote('39'));

$query
	->SELECT( array(
		'u.id',
		'u.name',
		'gcm.groupid',
		'cu.alias',
		'cu.avatar',
		'cg.groupname',
		 $companyQuery->__toString(),
         $titleQuery->__toString(),
         $professionQuery->__toString(),
         $mobileQuery->__toString(),
         $officeQuery->__toString(),
         $emailQuery->__toString()
	) )
->FROM( $db->quoteName( '#__users', 'u' ) )
->join('INNER', $db->quoteName('#__community_users', 'cu') . ' ON ' . $db->quoteName('u.id') . ' = ' . $db->quoteName('cu.userid'))
->join('INNER', $db->quoteName('#__community_groups_members', 'cgm') . ' ON ' . $db->quoteName('u.id') . ' = ' . $db->quoteName('cgm.memberid'))
->join('INNER', $db->quoteName('#__community_groups', 'cg') . ' ON ' . $db->quoteName('cgm.groupid') . ' = ' . $db->quoteName('cg.id'))
->WHERE( $db->quoteName( 'u.block' )   . ' = ' . '0' )
->and( $db->quoteName( 'cgm.groupid' )   . ' = \'' .$selectedgroupid. '\'' );
// ->ORDER( $db->quoteName( 'u.name' ) . ' ASC') ;
$db->setQuery( $query );

// Create Associative Array
$recresults = $db->loadAssocList();

echo  '<table class="table" bgcolor="#E0E0E0" style="background-color: #E0E0E0; color: #041C43;">
			<tbody>';

foreach ( $recresults as $result => $inner_record ) {
        if (empty($inner_record[ 'avatar' ])) {
            $inner_record[ 'avatar' ] = 'components/com_community/assets/user-Male.png';
        } else {
        }
	echo '
	<tr>
		<td><a href="index.php/profile/'.$inner_record['alias'].'"><img class="img-circle" style="float: left; padding-right: 15px; padding-bottom: 15px;" src="https://networkinaction.com/' . $inner_record[ 'avatar' ] . '"></a>
		<a href="index.php/profile/'.$inner_record['alias'].'">
		<span style="font-size: larger;"><strong>Name: ' . $inner_record[ 'name' ] . '</strong></a></span><br />
		<span style="font-size: larger;"><em><strong>Company:</strong> ' . $inner_record[ 'company' ] . ' </em></span><br />
		<span style="font-size: larger;"><em><strong>Title:</strong> '. $inner_record['title'] .'</em></span><br />
		<span style="font-size: larger;"><strong>Profession:</strong> '. $inner_record['profession'] .'</span><br />
		<strong>Mobile:</strong> '. $inner_record['mobile'] .'<br />
		<strong>Office:</strong> '. $inner_record['office'] .'<br />
		<strong>Email: '. $inner_record['email'] .'</strong>
		</td>
	</tr>';

}
echo '</tbody></table>';
THANK YOU!

DanHouston
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Tue Oct 06, 2015 7:12 pm

Re: Subqueries in SELECT | Go ahead... Laugh.

Post by DanHouston » Mon Dec 02, 2019 9:24 pm

The problem was that something created duplicate entries in my database. I removed them and the view began to work again.


Post Reply

Return to “Joomla! 3.x Coding”