Left Join, SQL COUNT(field) in Query

80 Posts

smg6511v2 Reply #1, 13 years, 1 month ago

I'm working on a relatively simple query that I need to convert to xPDO:

SELECT pcodes.seats, COUNT(preg.accesscode_id) as seatsused 
FROM passport_accesscoderegistry as preg 
LEFT JOIN passport_accesscodes as pcodes 
ON preg.accesscode_id = pcodes.id
WHERE accesscode_id = 4

The two tables are part of a component model with this (abbreviated schema):

<model version="1.1" package="passport" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" phpdoc-package="passport" phpdoc-subpackage="model">

    <object class="passportAccessCodes" table="passport_accesscodes" extends="xPDOSimpleObject">

        <field key="accesscode" dbtype="varchar" precision="25" phptype="string" null="false" default="" />
        <field key="seats" dbtype="smallint" precision="4" attributes="unsigned" phptype="integer" null="false" default="0" />
        [...]
        <index alias="accesscode" name="accesscode" primary="false" unique="true" type="BTREE">
            <column key="accesscode" length="" collation="A" null="false" />
        </index>
        
        <composite alias="passportAccessCodeRegistry" class="passportAccessCodeRegistry" local="id" foreign="accesscode_id" cardinality="many" owner="local" />

    </object>

    <object class="passportAccessCodeRegistry" table="passport_accesscoderegistry" extends="xPDOSimpleObject">

        <field key="accesscode_id" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="user_id" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        [...]

        <aggregate alias="passportAccessCodes" class="passportAccessCodes" local="accesscode_id" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="modUser" class="modUser" local="user_id" foreign="id" cardinality="one" owner="foreign" />

    </object>

</model>

The in-progress conversion (which I'm sure has problems):

$q = $this->modx->newQuery('passportAccessCodeRegistry');
$q->select('seats, COUNT(AC.accesscode_id) as seatsused');
$q->leftJoin('passportAccessCodes','AC');
$q->where(array('accesscode_id'=>$accesscode_id));
$q->prepare();
$q->stmt->execute();
$rs = $c->stmt->fetchColumn();

A number of general questions:
* Can this be done without going the prepared statement route?
* When you use ->leftJoin and the tables are being joined on the columns that are defined as related in the schema/model, do you still need to add the conditions parameter (which I've not done)?
* In joins, is the alias arbitrary or does it relate to that which is set up in the schema/model?
* I've seen many examples of the use of ->getSelectColumns ... when is it necessary and/or desirable?
* When you're fetching more than one column, do you still use ->fetchColumn? If so, do you just access the columns by:

$val1 = $rs->get('col1')
$val2 = $rs->get('col2')

Thanks for wading through the above!

This question has been answered by multiple community members. See the first response.

265 Posts

thapriest86 Reply #2, 13 years, 1 month ago

When you have a relation defined in your schema you could/should use getCollectionGraph.
http://rtfm.modx.com/xpdo/2.x/class-reference/xpdo/xpdo.getcollectiongraph
That way you do not have to use the Join

Also, modx extends the xpdo class, so you could also use:

$q = $modx->newQuery('passportAccessCodeRegistry');

80 Posts

smg6511v2 Reply #3, 13 years ago

Thanks for the reply. I see how the getCollectionGraph method is useful in fetching related objects. The data I'm looking to retrieve from the above mysql query, however, is much simpler (only one row, two columns). There must be a way to do this via xPDO, right?

discuss.answer

265 Posts

thapriest86 Reply #4, 13 years ago

From the top of my head, between coffee breaks. (untested.)

$q = $this->modx->newQuery('passportAccessCodeRegistry');
	
	$q->select(array(
		'seats, COUNT(AC.accesscode_id) as seatsused',
		));

	$q->leftJoin('passportAccessCodes','passportAccessCodes','passportAccessCodeRegistry.id = passportAccessCodes.id');

	$q->where(array(
		'accesscode_id'=>$accesscode_id
		));
		
		$results = $modx->getIterator('passportAccessCodeRegistry', $q);
			//Uncomment this to check what it would look like in  plain SQL and debug
			//echo $select->toSql();

		//and then iterate through it

discuss.answer

80 Posts

smg6511v2 Reply #5, 13 years ago

Thanks for sending me in the right direction! The final working query ended up being:

$q = $this->modx->newQuery('passportAccessCodeRegistry');
$q->select(array(
            'seats, COUNT(accesscode_id) as seatsused',
));
$q->rightJoin('passportAccessCodes','passportAccessCodes','passportAccessCodeRegistry.accesscode_id = passportAccessCodes.id');
$q->where(array(
            'accesscode_id'=>$id
));
$results = $this->modx->getIterator('passportAccessCodeRegistry', $q);
foreach($results as $result){
            $seats = $result->get('seats');
            $used = $result->get('seatsused');
}

Answered Left Join, SQL COUNT(field) in Query