We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 18373 ☆ A M B ☆
    • 3,141 Posts
    For an event manager I need to get the total amount of reservations made for a certain event, to make sure it’s not being overbooked.

    At first I was using getCount with a new Query, or a count(..) on the result set of $event->getMany(’Reservations’) which both return the number of reservations - however one reservation can be any amount of tickets being ordered... which makes it useless sad


    So I am wondering if there’s any way to get the count of a column with xPDO, rather then the number of objects. I could achieve it by looping through the reservations, but that seems like an awful amount of overhead. Any ideas?

    Here’s my schema:
    <?xml version="1.0" encoding="UTF-8"?>
    <model package="eventManager" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1" phpdoc-package="eventManager" phpdoc-subpackage="model">
    	<object class="Events" table="em_events" extends="xPDOObject">
    		<field key="eventid" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native" />
    		<field key="title" dbtype="varchar" precision="200" phptype="string" null="false" />
    		<field key="description" dbtype="varchar" precision="2000" phptype="string" null="false" />
    		<field key="date" dbtype="int" precision="15" phptype="integer" null="false" />
    		<field key="capacity" dbtype="int" precision="10" phptype="integer" null="true" />
    		<field key="last_signup" dbtype="int" precision="10" phptype="integer" null="true" />
    		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    			<column key="eventid" collation="A" null="false" />
    		</index>  
    		<composite alias="Reservations" class="Reservations" local="eventid" foreign="eventid" cardinality="many" owner="local" />
    	</object>
    	<object class="Reservations" table="em_reservations" extends="xPDOObject">
    		<field key="reservationid" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native" />
    		<field key="eventid" dbtype="int" precision="10" phptype="integer" null="false" />
    		<field key="tickets" dbtype="int" precision="10" phptype="integer" null="false" />
    		<field key="firstname" dbtype="varchar" precision="200" phptype="string" null="false" />
    		<field key="lastname" dbtype="varchar" precision="200" phptype="string" null="false" />
    		<field key="time" dbtype="int" precision="15" phptype="integer" null="false" />
    		<field key="address" dbtype="varchar" precision="500" phptype="string" null="false" />
    		<field key="email" dbtype="varchar" precision="200" phptype="string" null="false" />
    		<field key="remarks" dbtype="varchar" precision="2000" phptype="string" null="false" />
    		<field key="phone" dbtype="varchar" precision="30" phptype="string" null="false" />
    		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    			<column key="reservationid" collation="A" null="false" />
    		</index>
    		<composite alias="Events" class="Events" local="eventid" foreign="eventid" cardinality="one" owner="foreign" />
    	</object>
    </model>
      Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

      Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.
      • 4172
      • 5,888 Posts
      perhaps something like that can do it:

      <?php
      $class='Reservations';
      
      $c = $modx->newQuery($class);
      $c->select('sum(tickets) as cnt');
      $c->where(array('eventid'=>$eventid));
      $object = $modx->getObject($class, $c);
      
      echo $object->get('cnt');
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 22303 MODX Staff
        • 10,725 Posts
        You wouldn’t instantiate an object from that query since it doesn’t have the columns of the object. But you can build it that way and execute the prepared statement:
        <?php
        $c = $modx->newQuery('Reservations');
        $c->select('sum(tickets) as cnt');
        $c->where(array('eventid'=>$eventid));
        if ($c->prepare() && $c->stmt->execute()) {
            echo $c->stmt->fetchColumn();
        }
        
          • 18373 ☆ A M B ☆
          • 3,141 Posts
          Thanks. smiley Greatly appreciated!

          Will do this when I have time for it again, and if I run into issues I’ll report back.
            Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

            Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.